Monitor for a particular message id being sent in the past 24 hours

MONMSGID.sql

--
-- Description: Some software sends a warning message when its license key is nearing expiration, but the message
--              is only sent to the user who is accessing the software at the time that they access it.
--              If that user doesn't notify the system admin, the license key may expire "without warning"!
--              Can I write a single SQL statement to check if a specific message id was sent to any user in
--              the past 24 hours, and send me an email alert if so?
--

--
-- Find all occurrences of message ID "XYZ1234" having been sent to any user:
SELECT *
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234';


--
-- Limit the search to messages sent only in the past 24 hours:
SELECT *
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS;


--
-- We want to send an email alert if any occurrences of this message are found.
-- We only want to send 1 alert regardless of how many times this message has been sent.
-- Adding "LIMIT 1" allows us to retrieve a maximum of 1 record.
SELECT *
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
    LIMIT 1;


--
-- If any such message is found, we can use the SNDSMTPEMM command to send an email alert.
-- Below is the syntax for sending an email using this command from a command line:
SNDSMTPEMM RCP(('[email protected]')) SUBJECT('Message XYZ1234 found!') NOTE('Message ID XYZ1234 sent in the past 24 hours.')
stop;
-- Or to run this command in "Run SQL Scripts":
cl:SNDSMTPEMM RCP(('[email protected]')) SUBJECT('Message XYZ1234 found!') NOTE('Message ID XYZ1234 sent in the past 24 hours.');


--
-- Since we're not operating at a command line, we can take the "SNDSMTPEMM" command string
--    from above, and put it into our SELECT statement.
-- In our SELECT we assign this string the name "command".
-- Note that the single quotes in the original command had to become two single quotes,
--    because the whole command itself is included within single quotes.
SELECT
    'SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''Message XYZ1234 found!'') NOTE(''Message ID XYZ1234 sent in the past 24 hours.'')' AS command
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
    LIMIT 1;


--
-- So far the "SNDSMTPEMM" command is simply a string of characters. The command has not been executed.
--     The string is simply being returned in the SQL results.
-- If the command string being returned looks good, we can ask the system to EXECUTE that string instead of displaying it.
-- The key here is the QSYS2.QCMDEXC() scalar function.  It's easy!  Wrap the string with "QSYS2.QCMDEXC(your command here)"
-- Since the command string is very long, I've broken it in half to make it display nicely in the browser window:
SELECT
    qsys2.qcmdexc('SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''Message XYZ1234 was found!'') ' ||
        'NOTE(''The message id XYZ1234 was sent in the past 24 hours.  Investigation required!'')') AS command
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
    LIMIT 1;
-- QSYS2.QCMDEXC() will return a "1" if the command executes successfully - or a "-1" if the command fails


--
-- The final step to making this SQL statement function as a monitor/alert is to schedule it to run daily.
-- Unfortunately an SQL "SELECT" can't be run in batch - a SELECT is meant to return data back to your screen!
-- However, there's an easy fix.  We can simply wrap the SELECT with a "CREATE TABLE xxx AS (...) WITH DATA" like this:
CREATE TABLE qtemp.alert AS
            (SELECT qsys2.qcmdexc('SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''Message XYZ1234 was found!'') ' ||
                'NOTE(''The message id XYZ1234 was sent in the past 24 hours.  Investigation required!'')') AS command
                    FROM qsys2.message_queue_info
                    WHERE message_id = 'XYZ1234'
                          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
                    LIMIT 1)
            WITH DATA;


--
-- Now we have an SQL statement that can be run in batch.
-- There are many options here, but we could use the native job scheduler to run this command daily at 10am:
cl:ADDJOBSCDE JOB(MONMSGID) CMD(RUNSQL SQL('create table qtemp.alert as (select qsys2.qcmdexc(''SNDSMTPEMM RCP((''''[email protected]'''')) SUBJECT(''''Message XYZ1234 was found!'''') NOTE(''''The message id XYZ1234 was sent in the past 24 hours.  Investigation required!'''')'') as cmdresult from qsys2.message_queue_info where message_id=''XYZ1234'' and message_timestamp > current timestamp - 24 HOURS limit 1) with data') COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(100000);
-- Since we are passing this whole SQL statement into the RUNSQL command, we have to "double-up" the single quotes inside the string again.
-- Admittedly it does look ugly, but it works.
-- We could use "RUNSQLSTM" instead of "RUNSQL" and keep the whole SQL statement inside an IFS file or a source file member.


--
-- This could be good enough!
-- This SQL would run once per day, and it would send an email alert if this message id was sent during the previous 24 hours.  
-- If that's all you need, run with it!  However, we can make it even better!  


--
-- We can make the email subject and body dynamic by concatenating values that are provided by the qsys2.message_queue_info view.
-- Here we concatenate the MESSAGE_ID into the subject, and the MESSAGE_TIMESTAMP into the body of the email:
SELECT 'SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''WARNING: Message ' || message_id ||
           ' was found!'') NOTE(''The message was sent at ' || message_timestamp || ''')' AS command
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
    LIMIT 1;


--
-- We can add other details to the email alert like the MESSAGE_TEXT:
SELECT 'SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''WARNING: Message ' || message_id || 
           ' was found!'') NOTE(''The message id ' || message_id || 
           ' was sent at ' || message_timestamp || 
           '. Message text: ' || message_text || ''')' AS command
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
    LIMIT 1;


--
-- We can even add the user profile to whom the message was sent (MESSAGE_QUEUE):
SELECT 'SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''WARNING: Message ' || message_id || 
           ' was found!'') NOTE(''The message id ' || message_id || 
           ' was sent at ' || message_timestamp || 
           ' to user ' || message_queue_name || 
           '. Message text: ' || message_text || ''')' AS cmdstring
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
    LIMIT 1;


--
-- We can also add the name of the LPAR that is sending the alert, using "CURRENT SERVER":
SELECT 'SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''WARNING from ' || CURRENT SERVER || 
           ': Message ' || message_id ||
           ' was found!'') NOTE(''The message id ' || message_id || 
           ' was sent at ' || message_timestamp || 
           ' to user ' || message_queue_name || 
           '. Message text: ' || message_text || ''')' AS command
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
    LIMIT 1;


--
-- This alert could absolutely be good enough!
-- It provides a lot of details: the message text, when it was sent, to whom it was sent, and even the system name sending the alert.
-- However, the SNDSMTPEMM command also has the ability to send HTML-formatted emails.
-- We can make the email more readable using the CONTENT(*HTML) parameter and some simple HTML tags:
SELECT 'SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''WARNING from ' || CURRENT SERVER || 
           ': Message ' || message_id ||
           ' was found!'') NOTE(''<h2>The message id ' || message_id || 
           ' was sent at ' || message_timestamp || 
           ' to user ' || message_queue_name || 
           '.</h2><b>Message text:</b> ' || message_text || 
           '<p><b>From job:</b> ' || from_job || ''') CONTENT(*HTML)' AS command
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
    LIMIT 1;


--
-- If you (like me) don't like seeing microseconds on the timestamp in the email, we can remove them using the timestamp() function
SELECT
    'SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''WARNING from ' || CURRENT SERVER || ': Message ' || message_id ||
        ' was found!'') NOTE(''<h2>The message id ' || message_id || 
        ' was sent at ' || TIMESTAMP(message_timestamp, 0) || 
        ' to user ' || message_queue_name || 
        '.</h2><b>Message text:</b> ' || message_text || 
        '<p><b>From job:</b> ' || from_job || ''') CONTENT(*HTML)' AS command
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
    LIMIT 1;


--
-- We can also break out the date and time from MESSAGE_TIMESTAMP and display them on separate lines:
SELECT
    'SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''WARNING from ' || CURRENT SERVER || 
        ': Message ' || message_id ||
        ' was found!'') NOTE(''<h2>The message id ' || message_id || 
        ' was sent in the previous 24 hours:</h2><p><b>Date:</b> ' || DATE(message_timestamp) || 
        '<p><b>Time:</b> ' || TIME(TIMESTAMP(message_timestamp, 0)) || 
        '<p><b>User:</b> ' || message_queue_name || 
        '<p><b>Message text:</b> ' || message_text || 
        '<p><b>From job:</b> ' || from_job || ''') CONTENT(*HTML)' AS command
    FROM qsys2.message_queue_info
    WHERE message_id = 'XYZ1234'
          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
    LIMIT 1;


--
-- We can add the QSYS2.QCMDEXC() scalar function around the command, and again use the 
-- "CREATE TABLE xxx AS (...) WITH DATA" trick to allow the statement to run in batch:
CREATE TABLE qtemp.monmsgid AS
            (SELECT QSYS2.QCMDEXC(
                    'SNDSMTPEMM RCP((''[email protected]'')) SUBJECT(''WARNING from ' || CURRENT SERVER || 
                        ': Message ' || message_id || ' was found!'') NOTE(''<h2>The message id ' || message_id ||
                        ' was sent in the previous 24 hours:</h2><p><b>Date:</b> ' || DATE(message_timestamp) || 
                        '<p><b>Time:</b> ' || TIME(TIMESTAMP(message_timestamp, 0)) || 
                        '<p><b>User:</b> ' || message_queue_name ||
                        '<p><b>Message text:</b> ' || message_text || 
                        '<p><b>From job:</b> ' || from_job || ''') CONTENT(*HTML)')
                        AS command
                    FROM qsys2.message_queue_info
                    WHERE message_id = 'XYZ1234'
                          AND message_timestamp > CURRENT TIMESTAMP - 24 HOURS
                    LIMIT 1)
            WITH DATA;


--
-- Now we can schedule this statement to run once per day.
-- In this case I've put the statement into a streammfile in the IFS instead of squeezing it into the command:
cl:ADDJOBSCDE JOB(MONMSGID) CMD('RUNSQLSTM SRCSTMF(''/home/sriedmue/MONMSGID.sql'') COMMIT(*NONE) NAMING(*SQL)') FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(100000);


--
-- The possibilities are endless.  You can customize the font and layout of the email as as much or as little
--     as you'd like, and you can apply these same principles to send alerts based on all sorts of data!
--