Confirm

Expand all | Collapse all

SQL and Report - Count User Logins Confirm OnDemand and ConfirmConnect

  • 1.  SQL and Report - Count User Logins Confirm OnDemand and ConfirmConnect

    Posted 10-10-2019 04:28
    Is there are available SQL already writen to Count the number of times people have logged in using Confirm on a pc and a mobile device?

    I have tried with this code below but it shows thousands of "logins" for some people and the user_log.source_flag shows either A,C or is blank.  I cannot find any details of what A and C represent.  Probably C = ConfirmConnect and A is a Web based login.

    You could of course add an extra line to view details of a specific work Group should you require :
      eg. work_group.work_group_code = 'STR' AND

    It would be very nice if this was made available in a Report in ADManagerPlus along with all the other reports similar to "Recently Logged On Users" where you can select the desired time period.

    Thanks for any information.
    ======================================================================
    Code I currently use is :
    SELECT
    user_login.login_name,
    user_login.first_name,
    user_login.surname,
    count ( user_login.login_name ) as Quantity_Of_Logins,
    work_group.work_group_name,
    user_log.source_flag
    FROM
    user_login, user_log,
    work_group
    WHERE
    user_login.login_name = user_log.login_name AND
    work_group.work_group_code = user_login.work_group_code AND
    user_log.source_flag IS NULL AND
    user_login.surname IS NOT NULL AND
    user_log.login_time >= '01-SEP-2019'
    GROUP BY
    user_login.login_name,
    user_login.first_name,
    user_login.surname,
    work_group.work_group_name,
    user_log.source_flag
    ORDER BY
    Quantity_Of_Logins DESC,
    user_login.surname,
    user_login.first_name


    ------------------------------
    Barry Jones
    Cheshire West and Cheshire
    Chester
    ------------------------------


  • 2.  RE: SQL and Report - Count User Logins Confirm OnDemand and ConfirmConnect

    Pitney Bowes
    Posted 12-17-2019 05:22
    Edited by Steve Bish 12-17-2019 06:15
    Hi Barry,

    I've reworked the SQL below:

    SELECT
       ul.login_name,
       ul.first_name,
       ul.surname,
       COUNT(ul.login_time) AS Quantity_Of_Logins,
       work_group.work_group_name,
       ul.source_of_login
    FROM
    (SELECT
       user_log.login_name,
       user_log.first_name,
       user_log.surname,
       user_log.station_id,
       user_log.login_time,
       CASE user_log.source_flag
          WHEN 'C' THEN 'Connector'
          WHEN 'A' THEN 'SAM'
          WHEN 'Z' THEN 'ConfirmWorkzone'
       ELSE
          CASE 
             WHEN user_log.station_id IS NULL and user_log.source_flag IS NULL
             THEN 'ConfirmConnect' ELSE 'Client' 
          END
       END as Source_Of_Login,       
       user_log.source_flag
    FROM
       user_log
    WHERE
       user_log.user_status_flag IN ('I','N') AND
       user_log.login_time >= '01-SEP-2019') ul, work_group, user_login
    WHERE
       ul.login_name = user_login.login_name AND
       user_login.work_group_code = work_group.work_group_code
    GROUP BY
       ul.login_name,
       ul.first_name,
       ul.surname,
       work_group.work_group_name,
       ul.source_of_login
    ORDER BY
       ul.surname,
       ul.first_name,
       Quantity_Of_Logins;


    Reporting on ConfirmConnect is a tricky one because it creates an entry each time a response is sent back to the Database for information. For example, when refreshing your tasks or lookups. So the information recorded may not be a true representation of user activity.

    The User Log table is more an audit of information, if you would like to see who is currently using what, then I would suggest querying the licence_session table.


    Also, please see the accompanying information below to assist you with further reporting on this:

    USER_STATUS_FLAG

    • N - Logout OK
    • I - Logged in
    • P - Invalid Pwd
    • M - Max User limit
    • U - Invalid User
    • D - User Access Denied
    • E - Session Expired

    SOURCE_FLAG

    • A - SAM
    • C - Connector
    • Z - ConfirmWorkzone
    • blank - Client

    I hope this helps.

    Kind regards,
    Steve

    ------------------------------
    Steve Bish
    Senior Software Support Analyst
    PITNEY BOWES SOFTWARE, INC
    ------------------------------



  • 3.  RE: SQL and Report - Count User Logins Confirm OnDemand and ConfirmConnect

    Posted 12-18-2019 17:11
    Edited by Andrew Marks 12-18-2019 17:13
    Hi Steve,

    Thanks for the updated SQL, I had a similar query and have been looking into this.
    In regards to the system recording all the 'connections' via ConfirmConnect these should just be filtered out shouldn't they? as you mentioned above they are not 'actual' login information.

    A quick question regarding you terminology, is there a particular reason you have labelled what I believe is the initial ConfirmConnect login 'Connector' and the subsequent 'Responses' ConfirmConnect? Just wanted to check I wasn't missing something.

    I put a query together previously that looks similar to yours (although we don't breakdown by workgroup) and thought I would share it:
    SELECT 
    	[login_name] AS 'Login Id'
    	,[first_name] + ' ' + [surname] AS ' Full Name'
    	,COUNT([licence_no]) AS 'Number of Logins'
    	,CAST([login_time] AS DATE) AS 'Login Date'
    	,CASE
    		WHEN [source_flag] = 'C' THEN 'Confirm Connect'
    		WHEN [source_flag] = 'A' THEN 'Strategic Asset Management'
    		WHEN [source_flag] = 'Z' THEN 'Confirm Workzone'
    		WHEN [station_id] IS NOT NULL THEN 'Confirm Enterprise'
    	END AS 'Source'
      FROM [Confirm_Prod].[dbo].[user_log]
      WHERE (station_id IS NOT NULL OR source_flag IS NOT NULL) AND [user_status_flag] IN ('I','N') AND [login_time] >= '01-JAN-2019'
      GROUP BY [login_name],
    	CAST([login_time] AS DATE),
    	[first_name],
    	[surname],
    	CASE
    		WHEN [source_flag] = 'C' THEN 'Confirm Connect'
    		WHEN [source_flag] = 'A' THEN 'Strategic Asset Management'
    		WHEN [source_flag] = 'Z' THEN 'Confirm Workzone'
    		WHEN [station_id] IS NOT NULL THEN 'Confirm Enterprise'
    	END
      ORDER BY [login_name], [Number of Logins]​


    ------------------------------
    Andrew Marks
    Tasmania Parks and Wildlife Service
    Hobart
    ------------------------------