Re: Disabled User ID without a corresponding invalid sign-in

[K Crawford <kscx3ksc@xxxxxxxxx>]

One thing we noticed was the amount of 'Click X' (closed the window using
the X in the upper right corner) in the REASON column.  We started asking
the users why.  Got anything from 'I didn't know I was supposed to sign
off' to 'session frozen/stuck'.  It pointed out that we had a set of
programs that had some issues, and needed some fixing.

The manager spot checks her employees to make sure the clock in/out times
and sign on times are reasonably close.

One thing I did omit in my example was I replaced the number of days with
docsys.@vTmpSql03 and issued this command before the run of the SQL Script.
-- Set the number of Days
CREATE OR REPLACE VARIABLE docsys.@vTmpSql03 numeric(6) DEFAULT '3'
That way I can change the days with minimal work.

I also put this in an RPGLE program so the manager can run it and create a
report and csv.

Kerwin


On Tue, Nov 2, 2021 at 8:05 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

> Wow... this fantastic.
>
> Thanks!
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of K
> Crawford
> Sent: Monday, November 1, 2021 12:15 PM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: Re: Disabled User ID without a corresponding invalid sign-in
>
> 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
>
>
> --
> KCrawford
> --
> 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