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

[Migration User]

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

[Migration User]

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

[Migration User]

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?

[Migration User]

That query does use the Inv_OU_Membership table as alias T3.

[Migration User]

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

[Migration User]

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

[Migration User]

Yeah I noticed after I posted that :)

[Migration User]

I'll try this on the weekend!  Thanks for the help bbiggs!