Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.

Help creating a SQL report for user logon and logoff times for specific OU

Created: 31 Jan 2013 • Updated: 01 Feb 2013 | 7 comments
This issue has been solved. See solution.

I've been trying to create a SQL query to get a report on user logon and logoff times for a specific OU.

I've tried just about everything and I'm out of ideas.

I've got this to get the logon report:

 

SELECT    DISTINCT T1.[Guid] AS 'Guid',
        T1.[Name] AS 'Name',
        T0.[Event] AS 'Event',
        T0.[User] AS 'User',
        T0.[Domain] AS 'Domain',
        T0.[Time] AS 'Time'
FROM [vResourceEx] T1
INNER JOIN [Evt_AeX_Client_LogOn] T0 ON T1.[Guid] = T0.[_ResourceGuid]
WHERE T1.[ResourceTypeGuid] = '493435F7-3B17-4C4C-B07F-C23E7AB7781F'
  AND T1.[IsManaged] = 1
  AND CONVERT(CHAR(10),T0.[Time],101) BETWEEN '01/31/2013' AND '01/31/2013'
  AND CONVERT(CHAR(10),T0.[Time],114) BETWEEN '00:00' AND '23:59'
ORDER BY T0.[Time] ASC
 
But I need to only do the report on a certain OU.  Any help would be appreciated.
 
Thanks

Comments 7 CommentsJump to latest comment

bbiggs's picture

Here is one way to do it. You'll have to edit the where statement to define what OU you are looking for.

The only catch is that you will have had to import user from your AD environment to know what ou the user is a member of.

SELECT DISTINCT T1.[Guid] AS 'Guid',
T1.[Name] AS 'Name',
T0.[Event] AS 'Event',
T0.[User] AS 'User',
T0.[Domain] AS 'Domain',
T0.[Time] AS 'Time',
T3.[Distinguished Name]
FROM [vComputer] T1
INNER JOIN [Evt_AeX_Client_LogOn] T0 ON T1.[Guid] = T0.[_ResourceGuid]
INNER JOIN [vUser] T2 ON T0.[User] = T2.[Name]
INNER JOIN [Inv_OU_Membership] T3 ON T2.[Guid] = T3.[_ResourceGuid]
AND T3.[IsDirectMember] = 1
WHERE T1.[IsManaged] = 1
AND CONVERT(CHAR(10),T0.[Time],101) BETWEEN '01/31/2013' AND '01/31/2013'
AND CONVERT(CHAR(10),T0.[Time],114) BETWEEN '00:00' AND '23:59'
AND T3.[Distinguished Name] like '%'
ORDER BY T0.[Time] ASC

Eunox's picture

I'll try it tomorrow morning.  This will probably satisfy the request.  But let me be a little more specific on what was requested.

Management basically wants to see what computers in this OU are not being used a lot.  These are multiuser machines and they just want to see if some are not being used as often as they should be.  There's a chance that a user from a different OU could log into these machines, so this report could miss some computers that aren't being used.

Is there anyway to use the Inv_OU_Membership database table?

mclemson's picture

That query does use the Inv_OU_Membership table as alias T3.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

bbiggs's picture

Here is a modified query that should be closer to what you want. It should now give you all the login events of computers from a specific OU instead of keying off the user's OU. For this query to work the user and computer will have to have been imported from AD. SELECT DISTINCT T1.[Guid] AS '_ItemGuid', T1.[Name] AS 'Name', T0.[Event] AS 'Event', T0.[User] AS 'User', T0.[Domain] AS 'Domain', T0.[Time] AS 'Time', T3.[Distinguished Name] AS 'User Distinguished Name' FROM [vComputer] T1 INNER JOIN [Evt_AeX_Client_LogOn] T0 ON T1.[Guid] = T0.[_ResourceGuid] INNER JOIN [vUser] T2 ON T0.[User] = T2.[Name] INNER JOIN [Inv_OU_Membership] T3 ON T2.[Guid] = T3.[_ResourceGuid] AND T3.[IsDirectMember] = 1 INNER JOIN [Inv_OU_Membership] T4 ON T1.[Guid] = T4.[_ResourceGuid] AND T4.[IsDirectMember] = 1 WHERE T1.[IsManaged] = 1 AND CONVERT(CHAR(10),T0.[Time],101) BETWEEN '01/31/2013' AND '01/31/2013' AND CONVERT(CHAR(10),T0.[Time],114) BETWEEN '00:00' AND '23:59' AND T4.[Distinguished Name] like '%' ORDER BY T0.[Time] ASC

bbiggs's picture

Here it is again with hopefully a better format:

SELECT DISTINCT T1.[Guid] AS '_ItemGuid',
T1.[Name] AS 'Name',
T0.[Event] AS 'Event',
T0.[User] AS 'User',
T0.[Domain] AS 'Domain',
T0.[Time] AS 'Time',
T3.[Distinguished Name] AS 'User Distinguished Name'

FROM [vComputer] T1
INNER JOIN [Evt_AeX_Client_LogOn] T0 ON T1.[Guid] = T0.[_ResourceGuid]
INNER JOIN [vUser] T2 ON T0.[User] = T2.[Name]
INNER JOIN [Inv_OU_Membership] T3 ON T2.[Guid] = T3.[_ResourceGuid]
AND T3.[IsDirectMember] = 1
INNER JOIN [Inv_OU_Membership] T4 ON T1.[Guid] = T4.[_ResourceGuid]
AND T4.[IsDirectMember] = 1
WHERE T1.[IsManaged] = 1
AND CONVERT(CHAR(10),T0.[Time],101) BETWEEN '01/31/2013' AND '01/31/2013'
AND CONVERT(CHAR(10),T0.[Time],114) BETWEEN '00:00' AND '23:59'
AND T4.[Distinguished Name] like '%'
ORDER BY T0.[Time] ASC

SOLUTION