/*
 *
 * Concurrent User Calculation
 * (Provided by Smart IS)
 * https://www.smart-is.com
 *
 * Permission is granted to use, copy, and modify this script for personal,
 * internal, or commercial use.
 *
 * NOTICE REQUIREMENT:
 * This notice and the Smart IS URL above must be retained in all copies and
 * modified versions of this script. This notice may not be removed, obscured,
 * or altered, including when the script is modified for local or internal use.
 *
 * Change Log
 *     2026-09-15 Initial
 *     2026-09-18 Support SQLServer
 *
 * This script calculates peak concurrent user counts from login and logout
 * events recorded in sys_audit across one or more WMS instances.
 *
 * A user is considered active beginning at the timestamp of a successful
 * login. The inferred session ends at the earliest of:
 *
 * 1. The next login or logout event recorded for the same user.
 * 2. The configured maximum session duration (uc_max_session_hrs).
 * 3. The current time, when evaluating a session that has not otherwise
 * reached either of the above boundaries.
 *
 * The maximum session duration provides a deterministic upper bound for
 * sessions when a corresponding logout event is not present in sys_audit.
 *
 * Sessions use a half-open time interval: [login time, logout time). A user
 * is therefore active at the exact login timestamp but is no longer active
 * at the inferred session-end timestamp.
 *
 * Concurrent users are counted as distinct user IDs active at the same
 * instant. Multiple simultaneous sessions for the same user ID therefore
 * count as one concurrent user.
 *
 * Before concurrency is calculated, overlapping sessions for the same user
 * are merged. For local reporting the merge is by system and user ID. For
 * global reporting it is by user ID across all systems after normalization
 * to UTC. This preserves distinct-user counting when sessions overlap.
 *
 * Each merged session contributes a +1 event at login and a -1 event at
 * logout. Events at the same timestamp are combined and a running sum gives
 * concurrency at every state change. Hour-boundary values and within-hour
 * event peaks are combined to produce peak concurrency for every hour.
 *
 * When uc_count_system_users is 0, only users defined in les_usr_ath are
 * included in the concurrent-user count. When it is 1, all user IDs found
 * in qualifying login events are eligible to be counted.
 *
 * Input
 * server_list - give a coma-separated list of instances we will parallel execute on
 * uc_max_session_hrs - this is maximum session length in hours when logot is not found.  8 is default
 * uc_since_dt - is date/time since we do the sys_audit lookup.  Default is sysdate - 5
 * uc_sys_audit_table_name - defaults to sys_audit.  it is a parmeter so that we can test for hypothetical cases
 * uc_count_system_users - if 1 then any user that is recorded in sys_audit is recorded, so even system users like PCKRELMGR counts
 * if 0 we count only those that exist in les_usr_ath
 * Note that if looking at very old data les_usr_ath may have been removed.
 * But as a general rule an organization is expected to never remove les_usr_ath so it is a fair way
 * to determine human user.
 * uc_output_mode - defines what we want to see as output
 * CONCUR_BY_HOUR_GLOBAL shows count by hours (utc) at enterprise level.  You can sort desc by count to see max
 * CONCUR_BY_HOUR_LOCAL shows count by hours for each instance in its own timezone
 * RAW - will show each user id as well - so raw data used
 *
 * uc_drop_temp_table - 1 or 0
 * uc_login_signature - default to Login succ% so this defines what defines a start of a session
 * uc_logout_signature - defaults to Logout% so this defines what ends a session
 *
 */
publish data
where server_list = "server1,server2"
and uc_max_session_hrs = 8
and uc_since_dt = sysdate - 90
and uc_drop_temp_table = 1
and uc_drop_temp_table_at_end = 1
and uc_sys_audit_table_name = 'SYS_AUDIT'
and uc_login_signature = 'Login succ%'
and uc_logout_signature = 'Logout%'
/*
 *
 * how do we treat a user not defined in les_usr_ath?
 *
 * 1 = count the user
 * 0 = do not count the user
 *
 */
and uc_count_system_users = 1

/*
 *
 * final output control
 *
 * RAW
 * detailed user rows at each concurrency check point
 *
 * CONCUR_BY_HOUR_GLOBAL
 * global peak concurrent distinct users by utc hour
 *
 * CONCUR_BY_HOUR_LOCAL
 * peak concurrent distinct users by instance/local hour
 *
 */
and uc_output_mode = 'CONCUR_BY_HOUR_LOCAL'
|
try
{
    if ( dbtype() = 'MSSQL' )
        publish data
        where uc_varchar_typ_id = 'varchar'
        and uc_int_typ_id = 'int'
        and uc_timezone_offset_typ_id = 'decimal(10,4)'
        and uc_timestamp_typ_id = 'datetime2(0)'
        and uc_date_typ_id = 'datetime2(0)'
        and sql_expr_min_hour_login_dte = "dateadd(hour, datediff(hour, 0, cast(min(login_dte) as datetime)), 0)"
        and sql_expr_max_hour_logout_dte = "dateadd(hour, datediff(hour, 0, cast(max(logout_dte) as datetime)), 0)"
        and sql_expr_trunc_login_dte = "dateadd(hour, datediff(hour, 0, cast(login_dte as datetime)), 0)"
        and sql_expr_min_hour_login_dte_utc = "dateadd(hour, datediff(hour, 0, cast(min(login_dte_utc) as datetime)), 0)"
        and sql_expr_max_hour_logout_dte_utc = "dateadd(hour, datediff(hour, 0, cast(max(logout_dte_utc) as datetime)), 0)"
        and sql_expr_trunc_login_dte_utc = "dateadd(hour, datediff(hour, 0, cast(login_dte_utc as datetime)), 0)"
        and sql_expr_trunc_event_dte = "dateadd(hour, datediff(hour, 0, cast(event_dte as datetime)), 0)"
        and sql_expr_trunc_event_dte_utc = "dateadd(hour, datediff(hour, 0, cast(event_dte_utc as datetime)), 0)"
        and sql_expr_logout_dte =
            "case"
         || " when coalesce(next_auddte, getdate()) < dateadd(hour, "
         || @uc_max_session_hrs
         || ", auddte)"
         || " then coalesce(next_auddte, getdate())"
         || " else dateadd(hour, "
         || @uc_max_session_hrs
         || ", auddte)"
         || " end"
        and sql_qq_local_hours = " select b.system,"
                              || "        dateadd(hour, n.hour_num, b.min_hour) hour_start"
                              || "   from bounds b"
                              || "        cross join"
                              || "        ("
                              || "            select cast(row_number() over (order by object_id) - 1 as int) hour_num"
                              || "              from sys.all_objects"
                              || "        ) n"
                              || "  where n.hour_num <= datediff(hour, b.min_hour, b.max_hour)"
        and sql_qq_utc_hours = " select dateadd(hour, n.hour_num, b.min_hour_utc) hour_start_utc"
                            || "   from bounds b"
                            || "        cross join"
                            || "        ("
                            || "            select cast(row_number() over (order by object_id) - 1 as int) hour_num"
                            || "              from sys.all_objects"
                            || "        ) n"
                            || "  where n.hour_num <= datediff(hour, b.min_hour_utc, b.max_hour_utc)"
        and sql_qq_analyze = "update statistics usr_temp_concur_anal"
    else
        publish data
        where uc_varchar_typ_id = 'varchar2'
        and uc_int_typ_id = 'number'
        and uc_timezone_offset_typ_id = 'number'
        and uc_timestamp_typ_id = 'timestamp'
        and uc_date_typ_id = 'date'
        and sql_expr_min_hour_login_dte = "trunc(min(login_dte), 'hh24')"
        and sql_expr_max_hour_logout_dte = "trunc(max(logout_dte), 'hh24')"
        and sql_expr_min_hour_login_dte_utc = "trunc(min(login_dte_utc), 'hh24')"
        and sql_expr_max_hour_logout_dte_utc = "trunc(max(logout_dte_utc), 'hh24')"
        and sql_expr_trunc_event_dte = "trunc(event_dte, 'hh24')"
        and sql_expr_trunc_event_dte_utc = "trunc(event_dte_utc, 'hh24')"
        and sql_expr_logout_dte =
            "case"
         || " when next_auddte is null"
         || " then auddte + ("
         || @uc_max_session_hrs
         || " / 24)"
         || " when next_auddte < auddte + ("
         || @uc_max_session_hrs
         || " / 24)"
         || " then next_auddte"
         || " else auddte + ("
         || @uc_max_session_hrs
         || " / 24)"
         || " end"
        and sql_qq_local_hours = " select /*+materialize*/"
                              || "        b.system,"
                              || "        b.min_hour + (n.hour_num / 24) hour_start"
                              || "   from bounds b"
                              || "        cross join"
                              || "        ("
                              || "            select level - 1 hour_num"
                              || "              from dual"
                              || "            connect by level <= (select max(((max_hour - min_hour) * 24) + 1) from bounds)"
                              || "        ) n"
                              || "  where n.hour_num <= ((b.max_hour - b.min_hour) * 24)"
        and sql_qq_utc_hours = " select /*+materialize*/"
                            || "        b.min_hour_utc + (n.hour_num / 24.0) hour_start_utc"
                            || "   from bounds b"
                            || "        cross join"
                            || "        ("
                            || "            select level - 1 hour_num"
                            || "              from dual"
                            || "            connect by level <= ((select max_hour_utc - min_hour_utc from bounds) * 24) + 1"
                            || "        ) n"
        and sql_qq_analyze = "analyze table usr_temp_concur_anal estimate statistics"
    |
    {
        if ( @uc_drop_temp_table = 1 )
            [
            drop table usr_temp_concur_anal
            ]
            catch (-955,-942,-3701)
        ;
        {
            [
            create table usr_temp_concur_anal
            (
                uc_row_type         @uc_varchar_typ_id:raw (100),
                usr_id              @uc_varchar_typ_id:raw (100),
                system              @uc_varchar_typ_id:raw (100),
                uc_defined_usr_flg  @uc_int_typ_id:raw,
                login_dte           @uc_date_typ_id:raw,
                logout_dte          @uc_date_typ_id:raw,
                login_dte_utc       @uc_date_typ_id:raw,
                logout_dte_utc      @uc_date_typ_id:raw,
                hour_start          @uc_timestamp_typ_id:raw,
                check_time          @uc_timestamp_typ_id:raw,
                hour_start_utc      @uc_timestamp_typ_id:raw,
                check_time_utc      @uc_timestamp_typ_id:raw,
                uc_timzon_cd        @uc_varchar_typ_id:raw (100),
                uc_timzon_offset    @uc_timezone_offset_typ_id:raw
            )
            ]
            catch (-955)
            ;
            [
            create index usr_temp_concur_anal_ix on usr_temp_concur_anal
            (
                uc_row_type, system, usr_id, login_dte, logout_dte
            )
            ]
            catch (-955)
            ;
            [
            create index usr_temp_concur_anal_ix_utc on usr_temp_concur_anal
            (
                uc_row_type, usr_id, login_dte_utc, logout_dte_utc
            )
            ]
            catch (-955)
        }
        ;
        parallel ( @server_list )
        {
            get system timezone offsets
            |
            {
                [/*#nobind*/
                /*
                 *
                 * get login/logout events
                 *
                 */
                with events as
                (
                    select /*+materialize*/
                           usr_id,
                           auddte,
                           case
                               when exec_cmd like @uc_login_signature then 'login'
                               when exec_cmd like @uc_logout_signature then 'logout'
                           end event_type
                    from @uc_sys_audit_table_name:raw sys_audit
                    where auddte > @uc_since_dt:date
                    and
                    (
                        exec_cmd like @uc_login_signature
                        or exec_cmd like @uc_logout_signature
                    )
                ),
    
                /*
                 *
                 * determine the next event for the same user
                 *
                 */
                ordered_events as
                (
                    select /*+materialize*/
                           usr_id,
                           auddte,
                           event_type,
                           lead(auddte) over
                           (
                               partition by usr_id
                               order by auddte
                           ) next_auddte
                    from events
                ),
    
                /*
                 *
                 * infer sessions
                 *
                 * a session ends at the earliest of:
                 *
                 * 1. the users next event
                 * 2. current time if there is no next event
                 * 3. configured maximum session duration
                 *
                 */
                sessions as
                (
                    select /*+materialize*/
                           usr_id,
                           auddte login_dte,
                           case
                               when nvl(next_auddte, sysdate) < auddte + (@uc_max_session_hrs / 24.0)
                                   then nvl(next_auddte, sysdate)
                               else auddte + (@uc_max_session_hrs / 24.0)
                           end logout_dte
                    from ordered_events
                    where event_type = 'login'
                ),
    
                /*
                 *
                 * determine whether each user exists in les_usr_ath
                 *
                 */
                session_users as
                (
                    select /*+materialize*/
                           s.usr_id,
                           s.login_dte,
                           s.logout_dte,
                           max
                           (
                               decode
                               (
                                   les_usr_ath.usr_id,
                                   null, 0,
                                   1
                               )
                           ) uc_defined_usr_flg
                    from sessions s
                         left outer join les_usr_ath
                           on les_usr_ath.usr_id = s.usr_id
                    group by s.usr_id,
                             s.login_dte,
                             s.logout_dte
                ),
    
                /*
                 *
                 * sessions that are actually eligible to be counted
                 *
                 */
                eligible_sessions as
                (
                    select /*+materialize*/
                           usr_id,
                           login_dte,
                           logout_dte,
                           uc_defined_usr_flg,
                           login_dte
                               + (@StoredTimZonOffset / 24) login_dte_utc,
                           logout_dte
                               + (@StoredTimZonOffset / 24) logout_dte_utc
                    from session_users
                    where @uc_count_system_users = 1
                       or uc_defined_usr_flg = 1
                )
    
                /*
                 *
                 * only session rows need to be returned from each instance.
                 *
                 * concurrency is calculated centrally from session boundary
                 * events, avoiding the candidate-point interval self join.
                 *
                 */
                select 'session' uc_row_type,
                       usr_id,
                       uc_defined_usr_flg,
                       login_dte,
                       logout_dte,
                       login_dte_utc,
                       logout_dte_utc,
                       null hour_start,
                       null check_time,
                       null hour_start_utc,
                       null check_time_utc,
                       @StoredTimZonCd uc_timzon_cd,
                       @StoredTimZonOffset uc_timzon_offset
                from eligible_sessions
                ]
            }
        }
        |
        publish data combination
        where res = @resultset
        and system = @system
    }
    >> res
    |
    {
        publish data combination 
        where res = @res
        |
        create record
        where table = 'usr_temp_concur_anal'
        ;
        [
        @sql_qq_analyze:raw
        ]
        ;
        noop
    }
    |
    {
        /*
         *
         * -------------------------------------------------------------
         * raw
         * -------------------------------------------------------------
         *
         * show reconstructed eligible sessions.
         *
         */
        if ( @uc_output_mode = 'RAW' )
        {
            [/*#nobind*/
            select system,
                   usr_id,
                   uc_defined_usr_flg,
                   login_dte,
                   logout_dte,
                   login_dte_utc,
                   logout_dte_utc,
                   uc_timzon_cd,
                   uc_timzon_offset
            from usr_temp_concur_anal
            where uc_row_type = 'session'
            order by system,
                      login_dte,
                      usr_id
            ]
            catch (-1403,510)
        }

        /*
         *
         * -------------------------------------------------------------
         * local hourly concurrency
         * -------------------------------------------------------------
         *
         * merge overlapping sessions for the same user on the same
         * system.  each merged interval becomes a +1 login event and
         * a -1 logout event.  a running sum gives concurrency without
         * an interval self join.
         *
         */
        else if ( @uc_output_mode = 'CONCUR_BY_HOUR_LOCAL' )
        {
            [/*#nobind*/
            with sessions as
            (
                select /*+materialize*/
                       system,
                       usr_id,
                       login_dte,
                       logout_dte
                from usr_temp_concur_anal
                where uc_row_type = 'session'
            ),
            ordered_sessions as
            (
                select /*+materialize*/
                       system,
                       usr_id,
                       login_dte,
                       logout_dte,
                       max(logout_dte) over
                       (
                           partition by system, usr_id
                           order by login_dte, logout_dte
                           rows between unbounded preceding and 1 preceding
                       ) prior_max_logout_dte
                from sessions
            ),
            session_groups as
            (
                select /*+materialize*/
                       system,
                       usr_id,
                       login_dte,
                       logout_dte,
                       sum
                       (
                           case
                               when prior_max_logout_dte is null
                                 or login_dte > prior_max_logout_dte
                               then 1
                               else 0
                           end
                       ) over
                       (
                           partition by system, usr_id
                           order by login_dte, logout_dte
                           rows unbounded preceding
                       ) session_group
                from ordered_sessions
            ),
            merged_sessions as
            (
                select /*+materialize*/
                       system,
                       usr_id,
                       min(login_dte) login_dte,
                       max(logout_dte) logout_dte
                from session_groups
                group by system,
                          usr_id,
                          session_group
            ),
            bounds as
            (
                select /*+materialize*/
                       system,
                       @sql_expr_min_hour_login_dte:raw min_hour,
                       @sql_expr_max_hour_logout_dte:raw max_hour
                from merged_sessions
                group by system
            ),
            hours as
            (
                @sql_qq_local_hours:raw
            ),
            event_delta as
            (
                select /*+materialize*/
                       system,
                       login_dte event_dte,
                       1 delta
                from merged_sessions

                union all

                select /*+materialize*/
                       system,
                       logout_dte event_dte,
                       -1 delta
                from merged_sessions

                union all

                select /*+materialize*/
                       system,
                       hour_start event_dte,
                       0 delta
                from hours
            ),
            collapsed_events as
            (
                select /*+materialize*/
                       system,
                       event_dte,
                       sum(delta) delta
                from event_delta
                group by system,
                         event_dte
            ),
            event_concurrency as
            (
                select /*+materialize*/
                       system,
                       event_dte,
                       sum(delta) over
                       (
                           partition by system
                           order by event_dte
                           rows unbounded preceding
                       ) uc_concur_user_cnt
                from collapsed_events
            )
            select system,
                   @sql_expr_trunc_event_dte:raw hour_start,
                   max(uc_concur_user_cnt) uc_concur_user_cnt
            from event_concurrency
            group by system,
                    @sql_expr_trunc_event_dte:raw
            order by system,
                      hour_start
            ]
        }

        else if ( @uc_output_mode = 'CONCUR_BY_HOUR_GLOBAL' )
        {
            [/*#nobind*/
            with sessions as
            (
                select /*+materialize*/
                       usr_id,
                       login_dte_utc,
                       logout_dte_utc
                from usr_temp_concur_anal
                where uc_row_type = 'session'
            ),
            ordered_sessions as
            (
                select /*+materialize*/
                       usr_id,
                       login_dte_utc,
                       logout_dte_utc,
                       max(logout_dte_utc) over
                       (
                           partition by usr_id
                           order by login_dte_utc, logout_dte_utc
                           rows between unbounded preceding and 1 preceding
                       ) prior_max_logout_dte_utc
                from sessions
            ),
            session_groups as
            (
                select /*+materialize*/
                       usr_id,
                       login_dte_utc,
                       logout_dte_utc,
                       sum
                       (
                           case
                               when prior_max_logout_dte_utc is null
                                 or login_dte_utc > prior_max_logout_dte_utc
                               then 1
                               else 0
                           end
                       ) over
                       (
                           partition by usr_id
                           order by login_dte_utc, logout_dte_utc
                           rows unbounded preceding
                       ) session_group
                from ordered_sessions
            ),
            merged_sessions as
            (
                select /*+materialize*/
                       usr_id,
                       min(login_dte_utc) login_dte_utc,
                       max(logout_dte_utc) logout_dte_utc
                from session_groups
                group by usr_id,
                          session_group
            ),
            bounds as
            (
                select /*+materialize*/
                       @sql_expr_min_hour_login_dte_utc:raw min_hour_utc,
                       @sql_expr_max_hour_logout_dte_utc:raw max_hour_utc
                from merged_sessions
            ),
            hours as
            (
                @sql_qq_utc_hours:raw
            ),
            event_delta as
            (
                select /*+materialize*/
                       login_dte_utc event_dte_utc,
                       1 delta
                from merged_sessions

                union all

                select /*+materialize*/
                       logout_dte_utc event_dte_utc,
                       -1 delta
                from merged_sessions

                union all

                select /*+materialize*/
                       hour_start_utc event_dte_utc,
                       0 delta
                from hours
            ),
            collapsed_events as
            (
                select /*+materialize*/
                       event_dte_utc,
                       sum(delta) delta
                from event_delta
                group by event_dte_utc
            ),
            event_concurrency as
            (
                select /*+materialize*/
                       event_dte_utc,
                       sum(delta) over
                       (
                           order by event_dte_utc
                           rows unbounded preceding
                       ) uc_concur_user_cnt
                from collapsed_events
            )
            select @sql_expr_trunc_event_dte_utc:raw hour_start_utc,
                   max(uc_concur_user_cnt) uc_concur_user_cnt
            from event_concurrency
            group by @sql_expr_trunc_event_dte_utc:raw
            order by hour_start_utc
            ]
        }
    }
}
finally
{
    if ( @uc_drop_temp_table_at_end = 1 )
    {
        [
        drop table usr_temp_concur_anal
        ]
        catch (-955,-942,-3701)
    }
}