I had a request some time ago to track sign on/off and disables etc. I use
this sql if you need to go back more than 3 days then change the 3 to what
you need. I run this in ACS Run SQL Script. I also put it in a program so
the manager can run it and see what happened.
;;
with tempA as (
-- Start of all jobs
SELECT
FROM_JOB,
MESSA00003 as Started_Time_Stamp,
from_user, message_text, message_id
FROM TABLE(QSYS2.HISTORY_LOG_INFO(CURRENT TIMESTAMP - 3 DAY,
CURRENT TIMESTAMP))
where message_id in('CPF1124')
),
-- End of all jobs
TempB as (
SELECT
FROM_JOB,
MESSA00003 as Ended_Time_Stamp,
from_user, message_text, message_id
FROM TABLE(QSYS2.HISTORY_LOG_INFO(START_TIME => CURRENT TIMESTAMP - 3 DAY,
END_TIME => CURRENT TIMESTAMP))
where message_id in ('CPC1126', 'CPI59F0', 'CPI1126', 'CPI1127' -- Time-out
, 'CPF1164' -- end
, 'CPF5140', 'CPC1125' -- stopped
)
),
-- Why end of jobs
TempC as (
SELECT
FROM_JOB,
MESSA00003 as Ended_Time_Stamp,
from_user, message_text, message_id
FROM TABLE(QSYS2.HISTORY_LOG_INFO(CURRENT TIMESTAMP - 3 DAY,
CURRENT TIMESTAMP))
where message_id in ('CPI1127') -- Time-out
),
-- Bad password or disabled
TempD as (
SELECT case
when message_id = 'CPF2234' -- Bad Password
THEN substr(trim(cast(replace(
REGEXP_SUBSTR(trim(message_text), '([^ ]*) ?', 1, 9, '', 1)
,'.',' ')as char(10) ccsid 37)),1,10)
when message_id = 'CPF1393' -- User Disabled
THEN substr(trim(cast(replace(
REGEXP_SUBSTR(trim(message_text), '([^ ]*) ?', 1, 3, '', 1)
,'.',' ')as char(10) ccsid 37)),1,10)
end from_user
,from_job as from_job
,message_timestamp as start_TS
,message_timestamp as end_TS
,case
WHEN message_id = 'CPF2234' THEN 'Bad Password '
WHEN message_id = 'CPF1393' THEN 'User Disabled '
end Reason
,message_id as msg_ID
,message_text as msg_txt
FROM TABLE(QSYS2.HISTORY_LOG_INFO(START_TIME => CURRENT TIMESTAMP - 3 DAY,
END_TIME => CURRENT TIMESTAMP))
where Message_id = 'CPF2234' -- Bad password
or Message_id = 'CPF1393' -- Disabled Profile
)
-- Display results
select --a.from_user, A.FROM_JOB,
distinct
case when b.message_id = 'CPC1125'
THEN substr(trim(cast(
REGEXP_SUBSTR(trim(b.message_text), '([^/]*)/?', 1, 2, '',
1)
as char(10) ccsid 37)),1,10)
else a.from_user
end as From_User,
case when b.message_id = 'CPC1125'
THEN substr(trim(cast(
REGEXP_SUBSTR(trim(b.message_text), '([^ ]*) ?', 1, 2, '',
1)
as char(30) ccsid 37)),1,30)
else a.from_job
end as From_job,
Started_Time_Stamp as Started_time_stamp,
b.Ended_time_stamp as Ended_Time_stamp,
substr(trim(cast(
CASE WHEN b.message_id = 'CPF1164' and
c.message_id is null THEN 'Sign On/Off'
WHEN b.message_id = 'CPF1164' and
c.message_id = 'CPI1127' THEN 'Timeout ' --||
-- (
-- SELECT right(Current_Character_value,3)
-- FROM qsys2.SYSTEM_VALUE_INFO
-- WHERE SYSTEM_VALUE_NAME = 'QINACTITV' ) ||
-- ' min'
WHEN b.message_id = 'CPF5140' THEN 'Click X'
WHEN b.message_id = 'CPC1125' THEN 'Ended by ' ||
REGEXP_SUBSTR(trim(b.message_text), '([^ ]*) ?', 1, 7, '', 1)
else 'Sign Off??'
END as char(25) ccsid 37)),1,25) as Reason
,a.message_id as A_Msg_ID
,b.message_id as B_Msg_ID
,c.message_id as C_Msg_ID
,' ' as D_Msg_ID
,substr(cast(b.message_Text as char(200) ccsid 37),1,100) as
Message_Text
from TempA A inner join TempB b on A.FROM_JOB = B.FROM_JOB
full outer join TempC c on substr(char(b.ended_time_stamp),1,20) =
substr(char(c.ended_time_stamp),1,20)
where a.from_user not like 'Q%' and
a.from_user not in ('ASI', 'ASIIBMI', 'MIMIXOWN', 'E2A1ADMIN',
'ONLINE', 'ONLINE2', 'CENT', 'EV1') and
a.from_job not like '%/UNIMSG%' and
a.from_job not like '%/DCNEWCRD%' and
a.from_job not like '%/REORG%' and
a.from_job not like '%/INFO01%' and
a.from_job not like '%/REPRUN%' and
a.from_job not like '%/RPWRKX%' and
a.from_job not like '%/SVCLT%' and
a.from_job not like '%/RVIEDO%' and
a.from_job not like '%/RVIEDT%' and
a.from_job not like '%/DEQRKPCTST%' and
a.from_job not like '%/QUSER/QSRCSRVS%' and --TCP RmtCmd Srvr jobs
a.from_job not like '%/QUSER/QCTXDMON%' and --Transform Services
a.from_job not like '%/QUSER/QZDASOINIT%' and --Transform Services
a.from_job not like '%/QB5SERVER%' and --Debugger
a.from_job not like '%/QB5PHSRV%' and --Debugger
a.from_job not like '%/QS%' and
a.from_job not like '%/QUSER/QZRCSRVS%' --Transform Services
union all
select d.from_user as from_user
,'N/A' as from_job --d.from_job
,d.start_ts as Started_Time_Stamp
,d.end_ts as ended_Time_Stamp
,d.reason as reason
,' ' as A_Msg_ID
,' ' as B_Msg_ID
,' ' as C_Msg_ID
,d.msg_ID as d_Msg_ID
,cast(d.msg_txt as char(100) ccsid 37) as message_text
from TempD d
order by 1, 3
;;
On Mon, Nov 1, 2021 at 10:42 AM Rob Berendt <rob@xxxxxxxxx> wrote:
https://www.ibm.com/docs/en/i/7.4?topic=journal-setting-up-security-auditing
https://www.ibm.com/docs/en/i/7.4?topic=auditors-unauthorized-access
https://www.ibm.com/docs/en/i/7.4?topic=journal-using-chgsecaud-set-up-security-auditing
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jim
Hawkins
Sent: Monday, November 1, 2021 11:35 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: RE: Disabled User ID without a corresponding invalid sign-in
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.
Thank you Rob,
I got nothing from the journal history, I even added PW and SO to the
list. We had a user that I know used the incorrect password on Friday
enough times to get disabled, and that doesn't even show up.
I used *curchain. *first (date) and *last.
I did apply the latest Cume over the weekend, prior to that we were 1 back
on v7.4.
I have rarely found a need for the journaling, and haven't really looked
at it. So it may not be configured to capture this information, or I could
be missing something in what I am doing .
Regards,
Jim Hawkins
Programmer Analyst
Interkal LLC
Kalamazoo, MI
If you're very current on OS and PTF's you can try the AF and CP entries
at https://www.ibm.com/docs/en/i/7.4?topic=services-audit-journal-entry
If you're not too far behind you can try those same entries here:
https://www.ibm.com/docs/en/i/7.4?topic=services-display-journal-table-function
and get remote port, remote IP address, etc.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
midrange.com
