cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
chriscornelis
Engaged Sweeper
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
1 ACCEPTED SOLUTION
brandon_jones
Champion Sweeper III
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

View solution in original post

2 REPLIES 2
chriscornelis
Engaged Sweeper
Thank you !
brandon_jones
Champion Sweeper III
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