Weekly report showing daily user log in totals

Hi,

I am trying to build a chart that will allow me to understand daily user log in totals, so I can build a picture of if we are using Solarwinds as intended internally.

I attempted to use the last log in time, which I pulled from Orion.Accounts but I am struggling to manipulate this into any form of chart.

Could anyone please offer some assistance?

Kind Regards

Find more posts tagged with

accounts

report

Accepted answers

Seashore

Below query looks in the audit log for the login-events. So you see how many logins there are per day. Might be that not all have permission to read this, might need to be admin. Hope that is ok anyway.

SELECT
    COUNT(A.AuditEventID) AS QTY
    ,DATETRUNC('DAY',A.TimeLoggedUtc) AS LoginDay
FROM Orion.Auditingevents as A
WHERE 
    A.AuditingActionType.ActionType='Orion.UserLogin'
    AND A.TimeLoggedUtc>ADDDAY(-7,GETUTCDATE())
    AND A.AccountID NOT LIKE 'websitemaintenance_user_%'
GROUP BY 
    DATETRUNC('DAY',A.TimeLoggedUtc)

You can change "DATETRUNC('DAY'...." to HOUR instead of day, if you want your numbers per hour instead of per day. (Change on two places in the query)

All comments

Seashore

SELECT
    COUNT(A.AuditEventID) AS QTY
    ,DATETRUNC('DAY',A.TimeLoggedUtc) AS LoginDay
FROM Orion.Auditingevents as A
WHERE 
    A.AuditingActionType.ActionType='Orion.UserLogin'
    AND A.TimeLoggedUtc>ADDDAY(-7,GETUTCDATE())
    AND A.AccountID NOT LIKE 'websitemaintenance_user_%'
GROUP BY 
    DATETRUNC('DAY',A.TimeLoggedUtc)

You can change "DATETRUNC('DAY'...." to HOUR instead of day, if you want your numbers per hour instead of per day. (Change on two places in the query)

AzaKing

Thank you for this!

If I wanted to develop this further, would it be possible to return only unique log ins? My data provided larger numbers than expected so I think its a lot of the same people signing in due to timeouts.

Thanks again!

bobmarley

 SELECT tolocal(TimeLoggedUtc) AS [Last Login]

     ,AccountID

     ,AuditEventMessage

     FROM Orion.AuditingEvents AS AE

     WHERE daydiff(timeloggedutc ,getutcdate())<1

     AND AE.AuditingActionType.ActionType = 'Orion.UserLogin'

     AND AccountID NOT LIKE '%BobMarley%'

     AND AccountID NOT LIKE '%_system%'

     ORDER BY TimeLoggedUtc DESC</pre>