cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jbryant
Engaged Sweeper II
I'm trying to generate a custom report that will tell me what computers were logged onto by individual users from an Active Directory group. I found 2 useful reports on the forums but I cannot seem to get them to work together.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssetGroups.AssetGroup,
tblAssets.Username,
tblCPlogoninfo.logontime
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where tblAssetGroups.AssetGroup = 'PTShared' And tblAssetCustom.State = 1


And this is the other report I found:

Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblAssets.AssetName,
tblAssets.Domain
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID


Thank you in advance,
Jimmy
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
The following report lists AD users being member of a specific AD group together with the assets they logged on to. Ensure that the OU in which your AD groups are is being scanned with Domain User Scanning.

Select Top 100000 tblADusers.Displayname,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.AssetName,
tblAssets.AssetID,
tLastLogon.[last logon]
From tblADusers
Left Join (Select tblCPlogoninfo.AssetID,
Max(tblCPlogoninfo.logontime) As [last logon],
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain
From tblCPlogoninfo
Group By tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) tLastLogon On tLastLogon.Username =
tblADusers.Username And tLastLogon.Domain = tblADusers.Userdomain
Left Join tblAssets On tLastLogon.AssetID = tblAssets.AssetID
Inner Join tblADMembership On tblADusers.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name Like 'Example AD group'
Order By tblADusers.Displayname,
tLastLogon.[last logon] Desc

View solution in original post

4 REPLIES 4
jbryant
Engaged Sweeper II
Daniel,

That could very well be the cause. I am scanning about 4000 assets with SQL compact. Will start the migration process once I get proper SQL licensing. Thank you very much for your help.

Regards,
Jimmy
Daniel_B
Lansweeper Alumni
We could not reproduce performance issues with this report. Possibly your database is quite large or you are scanning too many assets with SQL compact.
Could you truncate event logs and compact your database according to the instructions in this KB article. In case you are scanning more than 1000 assets and your database is running on SQL Compact, migrate it to SQL server Express or higher edition. Instructions can be found here.
jbryant
Engaged Sweeper II
Daniel,

Thank you for the custom report, but when I attempt to run it, the screen is stuck at "Processing...". I gave it an hour and its still sitting there. I tried 3 different AD groups and still stuck at the same screen.

Regards,
Jimmy
Daniel_B
Lansweeper Alumni
The following report lists AD users being member of a specific AD group together with the assets they logged on to. Ensure that the OU in which your AD groups are is being scanned with Domain User Scanning.

Select Top 100000 tblADusers.Displayname,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.AssetName,
tblAssets.AssetID,
tLastLogon.[last logon]
From tblADusers
Left Join (Select tblCPlogoninfo.AssetID,
Max(tblCPlogoninfo.logontime) As [last logon],
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain
From tblCPlogoninfo
Group By tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) tLastLogon On tLastLogon.Username =
tblADusers.Username And tLastLogon.Domain = tblADusers.Userdomain
Left Join tblAssets On tLastLogon.AssetID = tblAssets.AssetID
Inner Join tblADMembership On tblADusers.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name Like 'Example AD group'
Order By tblADusers.Displayname,
tLastLogon.[last logon] Desc