Report to show last logged on user

[dshu]

Hi,

I'm trying to create a report that shows the following -

IP Location
Asset Name
Last logged on user - not as the username, but as the display name
Last seen date


...but can't get the user to display as either first name + last name or display name, only as the username.



Thanks in advance.

[tagenalum]

I tried both queries, but the first one (Users and the last PC they logged on) gives no results.

Are you sure it is correct? I did a copy / paste into the report editor.

Thanks.

[Hemoco]

tagenalum wrote:
I tried both queries, but the first one (Users and the last PC they logged on) gives no results.

Are you sure it is correct? I did a copy / paste into the report editor.

Thanks.

This report only lists assets that belong to a group other than the Default Group. You can remove the asset group criterion, as shown below.

Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblAssets.AssetID,
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

[dshu]

Thanks for the quick response. The second is exactly what I was looking for.

Dan

[Hemoco]

This report gives the Users and the last PC they logged on.

Select Top 1000000 tblAssets.AssetName,
SubQuery.Username As Username,
SubQuery.Domain As Domain,
tblAssets.AssetID,
SubQuery.LastLogon,
tblAssetGroups.AssetGroup
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
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetGroups.Builtin = 'false'


This report gives the user logged on when the computer was last scanned:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Displayname,
tblAssetGroups.AssetGroup
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 tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain
Where tblAssetCustom.State = 1