IBM i - Send email alerts for user profiles that don't have a ticket mentioned in the text description

User Profile Text Monitor.sql

--
-- Description: As an audit requirement, all user profiles that are created should include a ticket number in the text description.
--              This SQL can be scheduled to run daily, and will send an email report alerting the administration team to any
--              user profiles that have been created in the past 7 days without a ticket number mentioned in the text.
--              This version is based on ServiceNOW ticket naming standards (SCTASK, RITM, CTASK, CHG) but can be adapted to suit.
--

-- Skip to the end (Step 5) for the complete solution, or follow along with the incremental improvements --

--Step 1: List all user profiles whose text descriptions don't reference a SNOW ticket
SELECT *
    FROM QSYS2.USER_INFO
    WHERE TEXT_DESCRIPTION NOT LIKE '%RITM%'
          AND TEXT_DESCRIPTION NOT LIKE '%SCTASK%'
          AND TEXT_DESCRIPTION NOT LIKE '%CTASK%'
          AND TEXT_DESCRIPTION NOT LIKE '%CHG%';
--Note 1: This is a good start, but we're going to see IBM-supplied user profiles, and other profiles that may be "out of scope" (i.e. created years prior)

--Step 2: List all user profiles created in the past week whose text descriptions don't reference a SNOW ticket
SELECT *
    FROM QSYS2.USER_INFO
    WHERE TEXT_DESCRIPTION NOT LIKE '%RITM%'
          AND TEXT_DESCRIPTION NOT LIKE '%SCTASK%'
          AND TEXT_DESCRIPTION NOT LIKE '%CTASK%'
          AND TEXT_DESCRIPTION NOT LIKE '%CHG%'
          AND CREATION_TIMESTAMP > CURRENT TIMESTAMP - 7 DAYS;
--Note 2: Now we have a more useful list of user profiles that meet our criteria

--Step 3: Send an email notification for each user profile created in the past week whose text description doesn't reference a SNOW ticket
SELECT QSYS2.QCMDEXC('SNDSMTPEMM RCP((''EMAILADDRESS@company.com'')) SUBJECT(''Recently-created user profile with no ticket in the text'') ' || 'NOTE(''' || AUTHORIZATION_NAME || ''')')
    FROM QSYS2.USER_INFO
    WHERE TEXT_DESCRIPTION NOT LIKE '%RITM%'
          AND TEXT_DESCRIPTION NOT LIKE '%SCTASK%'
          AND TEXT_DESCRIPTION NOT LIKE '%CTASK%'
          AND TEXT_DESCRIPTION NOT LIKE '%CHG%'
          AND CREATION_TIMESTAMP > CURRENT TIMESTAMP - 7 DAYS;
--Note 3: This statement will send email notifications when executed, only if there are any user profiles that match our criteria

--Step 4: Now let's "punch up" the email to contain more details and to be more readable by parsing in various details about the user profiles and using HTML formatting
SELECT QSYS2.QCMDEXC('SNDSMTPEMM RCP((''EMAILADDRESS@company.com'')) CONTENT(*HTML) SUBJECT(''' || CURRENT SERVER || 
                       ': Recently-created user profile with no ticket mentioned in the text description'') ' ||
                       'NOTE(''<p><b>User profile:</b> ' || AUTHORIZATION_NAME ||
                       '<br><b>Text description:</b> ' || TEXT_DESCRIPTION ||
                       '<p><b>Profile creation date:</b> ' || date(CREATION_TIMESTAMP)  || 
                       '<br><b>Profile creation time:</b> ' || time(CREATION_TIMESTAMP)  || 
                       '<br><b>Created by:</b> ' || USER_CREATOR ||
                       '<p>Please update the text description to reference the correct ticket number!'')')
    FROM QSYS2.USER_INFO
    WHERE TEXT_DESCRIPTION NOT LIKE '%RITM%'
          AND TEXT_DESCRIPTION NOT LIKE '%SCTASK%'
          AND TEXT_DESCRIPTION NOT LIKE '%CTASK%'
          AND TEXT_DESCRIPTION NOT LIKE '%CHG%'
          AND CREATION_TIMESTAMP > CURRENT TIMESTAMP - 7 DAYS;
--Note 4: this is useful, but we don't want to run this query manually every day

--Step 5 (final form): wrap up the query into a procedure which can be scheduled and executed in batch
CREATE OR REPLACE PROCEDURE UTILITY.MONPRFTXT ()
LANGUAGE SQL
BEGIN
    DROP TABLE IF EXISTS QTEMP.MONPRFTXT;
    --Send an alert for any profiles created in the past 7 days with no ticket mentioned in the description
    CREATE TABLE QTEMP.MONPRFTXT AS (
        SELECT QSYS2.QCMDEXC(
                   'SNDSMTPEMM RCP((''EMAILADDRESS@company.com'')) CONTENT(*HTML) SUBJECT(''' || CURRENT SERVER || 
                       ': Recently-created user profile with no ticket mentioned in the text description'') ' ||
                       'NOTE(''<p><b>User profile:</b> ' || AUTHORIZATION_NAME ||
                       '<br><b>Text description:</b> ' || TEXT_DESCRIPTION ||
                       '<p><b>Profile creation date:</b> ' || date(CREATION_TIMESTAMP)  || 
                       '<br><b>Profile creation time:</b> ' || time(CREATION_TIMESTAMP)  || 
                       '<br><b>Created by:</b> ' || USER_CREATOR ||
                       '<p>Please update the text description to reference the correct ticket number!'')') AS CMDRESULT,
               AUTHORIZATION_NAME,
               TEXT_DESCRIPTION,
               CREATION_TIMESTAMP
            FROM QSYS2.USER_INFO
            WHERE TEXT_DESCRIPTION NOT LIKE '%RITM%'
                  AND TEXT_DESCRIPTION NOT LIKE '%SCTASK%'
                  AND TEXT_DESCRIPTION NOT LIKE '%CTASK%'
                  AND TEXT_DESCRIPTION NOT LIKE '%CHG%'
                  AND CREATION_TIMESTAMP > CURRENT TIMESTAMP - 7 DAYS) WITH DATA;
END;

--schedule a job to run this procedure every day at 10am using the native job scheduler
CL:ADDJOBSCDE JOB(MONPRFTXT) CMD(CALL UTILITY/MONPRFTXT) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME('10:00:00') FRQ(*WEEKLY) RCYACN(*NOSBM) USER(WQAALLOBJ) JOBQ(QUSRNOMAX);

---------The resulting email notifications will look like this:
--Email Subject:  MYIBMIHOST: Recently-created user profile with no ticket mentioned in the text description
--Email Body:
  --User profile: TESTUSER
  --Text description: Test Account for github
  --Profile creation date: 2023-11-02
  --Profile creation time: 09:59:14
  --Created by: SYSADMIN
  --Please update the text description to reference the correct ticket number!