Troubleshooting
Problem
This document provides information on how to use two different queries on the history log to create a table showing when people signed on and off the system.
The two queries show different ways to format the data.
Symptom
I want to keep track of sign on and sign off for my company and what to try something other than using security auditing (QAUDJRN)
Diagnosing The Problem
The IBM i History Log (QHST) may provide enough information for your needs.
Resolving The Problem
This document provides information on how to use two different queries on the history log to create a table showing when people signed on and off the system. The two queries show different ways to format the data.
It is possible to query the history log to create a table showing when people signed on and off the system, using the table function HISTORY_LOG_INFO documented at:
HISTORY_LOG_INFO table function
From an SQL interface (such as STRSQL) you can use the SQL:
The first example indicates when each person signed on and signed off including the job name and number:
SELECT
FROM_JOB,
case when message_id = 'CPF1124' then 'signed on at'
else 'signed off at'
end as status,
MESSA00003 as Time_Stamp
FROM TABLE(QSYS2.HISTORY_LOG_INFO())
where message_id = 'CPF1164'
or message_id = 'CPF1124'
order by MESSA00003 with sample output:
FROM_JOB EVENT TIME_STAMP
447103/QTMHHTTP/QHTTP signed on at 2019-11-13-00.00.04.779512
447104/QSYS/QPMHDWRC signed on at 2019-11-13-00.00.05.054331
447099/QSYS/QSQDOALERT signed off at 2019-11-13-00.00.05.083479
447104/QSYS/QPMHDWRC signed off at 2019-11-13-00.00.06.071791A second sample puts the sign on and sign off information on the same line:
with TempA as (
SELECT
FROM_JOB,
MESSA00003 as Started_Time_Stamp
FROM TABLE(QSYS2.HISTORY_LOG_INFO())
where message_id = 'CPF1124'
), TempB as (
SELECT
FROM_JOB,
MESSA00003 as Ended_Time_Stamp
FROM TABLE(QSYS2.HISTORY_LOG_INFO())
where message_id = 'CPF1164'
)
select A.FROM_JOB, Started_Time_Stamp, Ended_time_stamp
from TempA A inner join TempB b on A.FROM_JOB = B.FROM_JOBwith sample output:
FROM_JOB STARTED_TIME_STAMP ENDED_TIME_STAMP
447099/QSYS/QSQDOALERT 2019-11-13-00.00.02.827910 2019-11-13-00.00.05.083479
447101/QSYS/QYMEPFRCVT 2019-11-13-00.00.02.830545 2019-11-13-00.00.08.286517
447100/QSYS/QYMEARCPMA 2019-11-13-00.00.02.843531 2019-11-13-00.00.07.101908
447103/QTMHHTTP/QHTTP 2019-11-13-00.00.04.779512 2019-11-13-04.00.01.166132 Older method using DSPLOG to a spooled file:
| 1. | Display the history log to a spooled file. For example, use the following command:The time can be limited. Limiting the messages is optional. The query works with or without limiting the messages but will take more time if you do not limit the output to CPF1124 and CPF1164. |
| 2. | Then, create a flat physical file by using the following command:If necessary, first create the library -yourlib- using the CRTLIB command. |
| 3. | Copy the spooled file to the physical file using the following command: |
| 4. |
In SQL, we filter the information for the first file out to only what we need; that is, remove the headers that were created from the spooled file. The statement varies if a different library or file name is used. Run the query from Interactive SQL, Query Manager, Operations Navigator->Run SQL scripts, or other SQL-type interfaces. This was written by using a slash ( /) between the library name and the file name. Do this with the following command in SQL: If you see a msgSQL5016 (Qualified object name HST not valid) change: |
| 5. | Re-set the relative record numbers in preparation for the next SQL statement. From the command line, use the following command: |
| 6. | Caution: This query definition is not supported and is offered on an as is basis. Any assistance from Software Support personnel in getting this working must be handled on a Consulting basis. Run the following query. The statement varies if a different library or file name is used. Run the query from Interactive SQL, Query Manager, Operations Navigator->Run SQL scripts, or other SQL-type interfaces. This was written by using a slash ( /) between the library name and the file name. |
The first example indicates when each person signed on and signed off including the job name and number:
Notes:
1. Detailed explanations of this document will require a consulting agreement.
2. NULL values in the output are rows we did not have complete information.
with temp1a as
(select substr(hst,38,29) as base,
substr(hst,30,7) as JOB_NUM,
rrn(a) as RRN1,
case when substr(hst,1,7) = 'CPF1124'
then 'signed on' else 'signed off'
end as STATUS
from -yourlib-/hst a
where substr(hst,1,1) <> ' ' and
( substr(hst,1,7) = 'CPF1124'
or substr(hst,1,7) = 'CPF1164')),
temp1b as (
select
char(substr(base,1,position('/' in base)-1),10)
as JOB_USER,
char(substr(base,position('/' in base)+1,
(case when position('started' in base) = 0 then
position('ended' in base) else
position('started' in base) end) -
(position('/' in base)+1)-1),10)
as JOB_NAME,
rrn1, JOB_NUM, STATUS
from temp1a ),
temp2a as (select
rrn(b) as RRN2, substr(hst,70,8) as date,
substr(hst,79,8) as time
from -yourlib-/hst b
where substr(hst,1,1) = ' ')
select
job_user, status, date, time,
'with job name' as WITH_JOB_NAME, job_name,
'using job number' as USING_JOB_NUMBER, job_num
from temp1b inner join temp2a on rrn1 + 1 = rrn2
with sample output:
JOB_USER STATUS DATE TIME WITH_JOB_NAME JOB_NAME USING_JOB_NUMBER JOB_NUM
---------- ---------- ---------- -------- ------------- ---------- ---------------- -------
TFTP signed off 12/20/2001 03.58.45 with job name QTTFT00083 using job number 284259
PM400 signed on 12/20/2001 04.00.27 with job name Q1PPMSUB using job number 284282
PM400 signed off 12/20/2001 04.00.28 with job name Q1PPMSUB using job number 284282
TFTP signed on 12/20/2001 04.28.45 with job name QTTFT00074 using job number 284283
TFTP signed on 12/20/2001 04.28.45 with job name QTTFT00095 using job number 284284
AVIDS signed off 12/20/2001 04.29.49 with job name QPADEV001R using job number 284256
TCP signed off 12/20/2001 04.38.54 with job name QTFTP00283 using job number 284180
TCP signed on 12/20/2001 04.38.54 with job name QTFTP00301 using job number 284285
TFTP signed off 12/20/2001 04.58.45 with job name QTTFT00054 using job number 284265
PM400 signed on 12/20/2001 05.00.28 with job name Q1PPMSUB using job number 284286
PM400 signed off 12/20/2001 05.00.29 with job name Q1PPMSUB using job number 284286
ZY0235 signed off 12/20/2001 05.19.54 with job name QPADEV000K using job number 284235
TFTP signed off 12/20/2001 05.28.45 with job name QTTFT00088 using job number 284266
RP signed off 12/20/2001 05.38.39 with job name CRPH using job number 284260
TFTP signed on 12/20/2001 05.58.45 with job name QTTFT00058 using job number 284288
TFTP signed on 12/20/2001 05.58.45 with job name QTTFT00100 using job number 284287
HQB signed on 12/20/2001 06.00.00 with job name ANZDFTPWD1 using job number 284289
A second sample puts the sign on and sign off information on the same line:
with temp1a as (
select substr(hst,38,29) as BASE,
substr(hst,30,7) as JOB_NUM,
rrn(a) as RRN1,
substr(hst,27,105) as TEXT,
substr(hst,1,7) as MSGID
from -yourlib-/hst a
where substr(hst,1,1) <> ' ' and
( substr(hst,1,7) = 'CPF1124'
or substr(hst,1,7) = 'CPF1164') ),
temp1b as (
select
char(substr(base,1,position('/' in base)-1),10)
as JOB_USER,
char(substr(base,position('/' in base)+1,
(case when position('started' in base) = 0 then
position('ended' in base) else
position('started' in base) end) -
(position('/' in base)+1)-1),10)
as JOB_NAME,
rrn1, text, JOB_NUM, msgid
from temp1a ),
temp2a as (
select
rrn(b) as RRN2, substr(hst,70,8) as DATE,
substr(hst,79,8) as TIME
from -yourlib-/hst b
where substr(hst,1,1) = ' '),
temp3a as (
select date, time, job_num, msgid, job_user, job_name
from temp1b inner join temp2a on rrn1 + 1 = rrn2
where msgid = 'CPF1124'),
temp4a as (
select date, time, job_num, msgid
from temp1b inner join temp2a on rrn1 + 1 = rrn2
where msgid = 'CPF1164')
select
job_user,
'signed on' as SIGNON, a.date as SIGNON_DATE,
a.time as SIGNON_TIME,
'and signed off' as SIGNED_OFF,
b.date as SIGNOFF_DATE, b.time as SIGNOFF_TIME,
'with job name' as WITH_JOB_NAME, job_name
from temp3a a left outer join temp4a b on a.job_num = b.job_num
with sample output as follows:
JOB_USER SIGNON SIGNON_DATE SIGNON_TIME SIGNED_OFF SIGNOFF_DATE SIGNOFF_TIME WITH_JOB_NAME JOB_NAME
---------- --------- ----------- ----------- -------------- ------------ ------------ ------------- ----------
USER signed on 12/13/2001 04.13.08 and signed off 12/13/2001 04.13.15 with job name QZRCSRVS
USER signed on 12/13/2001 04.13.08 and signed off 12/13/2001 04.13.27 with job name QZRCSRVS
NOTES signed on 12/13/2001 04.15.23 and signed off 12/13/2001 04.15.30 with job name CHRONOS
TFTP signed on 12/13/2001 02.56.23 and signed off 12/13/2001 04.56.23 with job name QTTFT00082
PM400 signed on 12/13/2001 05.00.01 and signed off 12/13/2001 05.00.02 with job name Q1PPMSUB
NOTES signed on 12/13/2001 05.00.24 and signed off 12/13/2001 05.00.50 with job name STATLOG
NOTES signed on 12/13/2001 05.15.28 and signed off 12/13/2001 05.15.34 with job name CHRONOS
TFTP signed on 12/13/2001 02.56.23 and signed off 12/13/2001 05.26.23 with job name QTTFT00061
HQB signed on 12/13/2001 06.00.00 and signed off 12/13/2001 06.00.01 with job name ANZDFTPWD1
TF signed on 12/13/2001 06.00.00 and signed off 12/13/2001 06.00.01 with job name VDTRECEIVE
PM400 signed on 12/13/2001 06.00.03 and signed off 12/13/2001 06.00.03 with job name Q1PCM2
PM400 signed on 12/13/2001 06.00.03 and signed off 12/13/2001 06.00.04 with job name Q1PPMCHK
PM400 signed on 12/13/2001 06.00.04 and signed off 12/13/2001 06.00.05 with job name Q1PPMSUB
NOTES signed on 12/13/2001 06.15.31 and signed off 12/13/2001 06.15.37 with job name CHRONOS
TFTP signed on 12/13/2001 04.26.23 and signed off 12/13/2001 06.26.23 with job name QTTFT00058
SECOFR signed on 12/13/2001 06.43.42 and signed off 12/13/2001 06.43.44 with job name Q4CC
SYS signed on 12/13/2001 06.43.46 and signed off 12/13/2001 06.44.19 with job name QYPSGETINVHistorical Number
25394513
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
Operating System
Software version:
All Versions
Operating system(s):
IBM i
Document number:
640759
Modified date:
02 October 2024
UID
nas8N1017156