Last used system of users in a specific OU

[chriscornelis]

Hello,

I am having issues creating a report.
What I would like is a report of the computers/systems used by users in a specific OU.
So far example a list of all the users in a specific AD OU with the last system they logged on to.

How can I create a report like this ?

Thank you

Chris

[chriscornelis]

Thank you !

[brandon_jones · Accepted solution]

See if this gives you what you want. On the where line you will need to replace the name of your OU. Be sure to leave the percent signs. I also included an optional time where you can limit the logons listed in the query. This will shorten the query time. If you want all logons showed, you can just remove " tblCPlogoninfo.logontime > GetDate() - 5 And" This will also only show active assets.

Select Top 1000000 tblAssets.AssetID,
tblCPlogoninfo.Username,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblCPlogoninfo.logontime,
tblADusers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblADusers On tblCPlogoninfo.Username = tblADusers.Username
Where tblCPlogoninfo.logontime > GetDate() - 5 And tblADusers.OU Like '%bar%'
And tblAssetCustom.State = 1