The request... find and query the most recent SNTP activity log.

Find and read the SNTP activity log.sql

--
-- Subject: The request... find and query the most recent SNTP activity log
-- Author: Scott Forstie  
-- Date  : June, 2022
-- Features Used : This Gist uses MESSAGE_QUEUE_INFO, rtrim, hex, hextoraw, interpret, IFS_READ_UTF8, and CTEs
--
-- Background: Many clients use an Simple Network Time Protocol (SNTP) client to keep the current time
--             on their IBM i in relative sync with a time server.
--             On the IBM i, the TCP9105 message appears in the System Operator message queue, 
--             pointing the admin to a log of SNTP activity.
--
-- This particular request was complicated by the fact that the TCP9105 message token was encoded as FOR BIT DATA.
-- Also note that the IFS stream file has CCSID 37, but contains character data encoded as UNICODE.
--
stop;

--
-- Review instances of the SNTP activity log (TCP9105) on the QSYS/QSYSOPR message queue 
--
select qsysopr.*
  from table (
      qsys2.MESSAGE_QUEUE_INFO()
    ) qsysopr
  where message_ID = 'TCP9105'
  order by message_timestamp desc;
stop;

--
-- Extract the IFS path used by the SNTP activity log, for the most recent log
--
select rtrim(qsysopr.message_tokens) as SNTP_activity_log_IFS_path
  from table (
      qsys2.MESSAGE_QUEUE_INFO()
    ) qsysopr
  where message_ID = 'TCP9105'
  order by message_timestamp desc
  limit 1;
stop;

--
-- Convert the text in message_tokens from FOR BIT DATA, into a usable form
--
 select qsysopr.message_tokens as sntp_path_for_bit_data,
        hex(qsysopr.message_tokens) as as_hex_char,
        hextoraw(hex(qsysopr.message_tokens)) as_raw_binary,
        interpret(hextoraw(hex(qsysopr.message_tokens)) as char(4096)) as_char,
        rtrim(interpret(hextoraw(hex(qsysopr.message_tokens)) as char(4096))) as_varchar   
      from table (
          qsys2.MESSAGE_QUEUE_INFO()
        ) qsysopr
      where message_ID = 'TCP9105'
      order by message_timestamp desc
      limit 1;
stop;

--
-- Read the most recent SNTP activity log
--
with sntp_log (ifs_path) as (
    -- find the IFS path name to the most recent SNTP activity log
    select rtrim(interpret(hextoraw(hex(qsysopr.message_tokens)) as char(4096)))    
      from table (
          qsys2.MESSAGE_QUEUE_INFO()
        ) qsysopr
      where message_ID = 'TCP9105'
      order by message_timestamp desc
      limit 1)
  select log.*
    from sntp_log, lateral (
           select *
             from table (
                 -- read the contents of the log
                 qsys2.ifs_read_utf8(PATH_NAME => ifs_path)
               )
         ) log;
stop;