Client Management Suite

Hi all.

I'm trying to find or create a report that would allow us to see how long users are logged in with a specific account.  We're trying to track down users who stay logged in with their admin accounts for excessive periods of time.

Has anyone done this?

Dave

You could do this with a pretty simple query, but would need to know exactly what type of data you are looking for.

Are you looking for users that have logged in and are still logged in after a certain amount of time?

Are you looking for a legacy report for those that have logged in and logged off and the duration that they were logged in?

For instance, the following query will give you the computerName, User and Duration of login for a list of specified users.  The criteria specified is that the logoff occurred within the last 30 days and the duration the user was loggedin was greater than 10 hours and the userid you are looking for is admin1 or admin2.  You can change any of those criterion to get a result you are looking for.

SELECT c.Name AS ComputerName, l.[User] as LoggedUser, CONVERT(FLOAT,l.Duration / 60.0) AS DurationHrs
FROM vComputer c
INNER JOIN Evt_AeX_Client_LogOn l ON l._ResourceGuid = c.Guid
WHERE l.Event = 'Logoff'
  AND l.Duration <> 0
  AND CONVERT(FLOAT,l.Duration / 60.0) > 10
  AND l._eventTime > DATEADD(Day, -30, CURRENT_TIMESTAMP)
  AND l.[User] IN ('admin1', 'admin2')

Depending on what you are really after, you can use vComputer and Evt_AeX_Client_Logon to get what you are looking for.

RS

RS--

Thank you for your reply.  This is an excellent start and I'll spend some time playing around with this query.

What I'd really like to see is the duration ANY admin user is logged in during a given time frame (day, week, month).  So rather than keying in on a specific username, we'd prefer it be "any account with admin rights".

Our environment has a few different scenarios:  1) The user's primary domain account has been added to the local admin group, 2)  The user has a local account that has been added to the local admin group, 3) The user has a secondary domain account that has been added to the local admin group. 

We suspect that an unfortunate number of users are regularly logged in with one of these types of admin accounts, and we'd like to have some hard numbers to take to Data Security and our managers to demonstrate the extent of the problem.

Right now we have a report that just tells us which accounts have admin rights, but it does nothing to tell us how often those accounts are used, or for how long.

Thanks again!  I really appreciate your reply.

Dave

Easy enough:

SELECT w.Name AS ComputerName, l.[User] as LoggedUser, CONVERT(FLOAT,l.Duration / 60.0) AS DurationHrs
FROM Wrksta w
INNER JOIN Evt_AeX_Client_LogOn l ON l._ResourceGuid = w.Guid
INNER JOIN AeXInv_AeX_OS_User_Profiles p ON p.WrkstaId = w.WrkstaId AND p.[User] = l.[User]
WHERE l.Event = 'Logoff'
  AND l.Duration <> 0
  AND CONVERT(FLOAT,l.Duration / 60.0) > 10
  AND l._eventTime > DATEADD(Day, -30, CURRENT_TIMESTAMP)
  AND p.[Admin Access] = 1
ORDER BY ComputerName, l.Duration DESC

The AeXInv_AeX_OS_User_Profiles view allows us to see which profiles are on the machine and which ones have admin access.  So, we join up to that group and that gets us away from having to specify actual user ids.

For the event time, I still this it is easiest to use the logoff events simply b/c it has a corresponding logon event.  Instead of doing something like AND l._eventTime > DATEADD(Day, -30, CURRENT_TIMESTAMP), you could do something like AND l._eventTime BETWEEN  '11/30/2008' AND '03/15/2009'.

However, this still doesn't solve the problem of a user logging and and net yet logging out.  A user with admin rights could have logged in a month ago and not yet logged out, so we don't pick it up in this query.  I will need to think on that one a little bit.

RS

RS--

This is awesome!  It would have taken me the better part of two days to figure it out for myself -- my SQL skills are lacking.

I really appreciate the time and effort you put into this!  Thumbs up!

Dave