Custom Report for computer logons based on an AD group

[jbryant]

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

[jbryant]

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]

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]

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 · Accepted solution]

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