000026281 - RSA Archer SQL to collect 90-day metrics on record updates and user logins

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000026281
Applies To5.x
IssueMethod for tracking user logins and record updates via the database across an instance of RSA Archer.
Resolution

The following SQL query is broken in to two main parts: Content Updates and User logins.  There is an aggregation of 30, 60 and 90 day counts.


Content updates are comprised of any update or record creation.   So if a record is updated twice in one day that will be reflected in the 30-day detail.


User logins are comprised of any users that logs in to the system.  If a user logs in more than once this report will reflect that behavior.  This count includes data feed logins that may not be available via the user interface


IF OBJECT_ID('tempdb..#contentCounts') IS NOT NULL


    DROP TABLE #contentCounts


 


CREATE TABLE #contentCounts


    (


      updates DECIMAL(12, 2) ,


      updateDate SMALLDATETIME


    )


 


INSERT  INTO #contentCounts


        ( updates ,


          updateDate


        )


        SELECT  COUNT(1.0) ,


                DATEADD(dd, DATEDIFF(dd, 0, update_date), 0) AS [when]


        FROM    dbo.tblIVContent AS c


        WHERE     update_date >= DATEADD(dd, -90, GETUTCDATE())


        GROUP BY DATEADD(dd, DATEDIFF(dd, 0, update_date), 0)


        ORDER BY 2


 


SELECT  ( SELECT    SUM(updates) / 30


          FROM      #contentCounts


          WHERE     updateDate >= DATEADD(dd, -30, GETUTCDATE())


        ) AS [ Content Updates last 30] ,


        ( SELECT    SUM(updates) / 60


          FROM      #contentCounts


          WHERE     updateDate >= DATEADD(dd, -60, GETUTCDATE())


        ) AS [last 60] ,


        ( SELECT    SUM(updates) / 90


          FROM      #contentCounts


          WHERE     updateDate >= DATEADD(dd, -90, GETUTCDATE())


        ) AS [last 90]


SELECT '30 Day Detail'


SELECT    CAST(updates AS INT),


          updateDate


          FROM      #contentCounts


          WHERE     updateDate > DATEADD(dd, -30, GETUTCDATE())


  


DROP TABLE #contentCounts


 


IF OBJECT_ID('tempdb..#UserCounts') IS NOT NULL


    DROP TABLE #UserCounts


 


CREATE TABLE #UserCounts


    (


      users DECIMAL(12, 2) ,


      loginDate SMALLDATETIME


    )


 


INSERT INTO #UserCounts


        ( users, loginDate )


        SELECT  COUNT(1.0) ,


                DATEADD(dd, DATEDIFF(dd, 0, login_time), 0) AS [when]


        FROM    dbo.tblLoginHistory AS c


        WHERE  login_time >= DATEADD(dd, -90, GETUTCDATE())


        GROUP BY DATEADD(dd, DATEDIFF(dd, 0, login_time), 0)


        ORDER BY 2


       


SELECT  ( SELECT    SUM(users) / 30


          FROM      #userCounts


          WHERE     loginDate >= DATEADD(dd, -30, GETUTCDATE())


        ) AS [ User Logins last 30] ,


        ( SELECT    SUM(users) / 60


          FROM      #userCounts


          WHERE     loginDate >= DATEADD(dd, -60, GETUTCDATE())


        ) AS [last 60] ,


        ( SELECT    SUM(users) / 90


          FROM      #userCounts


          WHERE     loginDate >= DATEADD(dd, -90, GETUTCDATE())


        ) AS [last 90]


SELECT '30 Day Detail'


SELECT    CAST(users AS INT),


          loginDate


          FROM      #userCounts


          WHERE     loginDate > DATEADD(dd, -30, GETUTCDATE())

Legacy Article IDa59771

Attachments

    Outcomes