cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
CollinB
Engaged Sweeper
Hi All,

When a user leaves the business their computer is often redeployed to someone else and the change sometimes gets overlooked. The Report "Computer: Not seen in the last x days" does a great job at tracking assets that have "disappeared" but doesn't help with assets that simply change hands.

I believe a report that shows any assets that have had a different user log into a computer and sort this by date would solve this issue.

Any help or other ideas with this would be much appreciated.

Regards
Collin
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
You could either use the list of scanned user logons or alternatively consider using asset user relations. These are stored in table tblAssetUserRelations after manually entering them on asset pages. You could filter on the date when an asset user relation was created.

If you prefer using the first method, please find an example report below which lists computers onto which more than one user logged on during the last 30 days. Please note that a computer needs to be scanned while a user logs on to it in order to have correct data in your Lansweeper database.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username As [last logged on user],
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join (Select tblCPlogoninfoDistinct.AssetID,
Count(tblCPlogoninfoDistinct.Username) As [number users]
From (Select Distinct tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) tblCPlogoninfoDistinct
Group By tblCPlogoninfoDistinct.AssetID
Having Count(tblCPlogoninfoDistinct.Username) > 1) tCPlogoninfo
On tblAssets.AssetID = tCPlogoninfo.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
You could either use the list of scanned user logons or alternatively consider using asset user relations. These are stored in table tblAssetUserRelations after manually entering them on asset pages. You could filter on the date when an asset user relation was created.

If you prefer using the first method, please find an example report below which lists computers onto which more than one user logged on during the last 30 days. Please note that a computer needs to be scanned while a user logs on to it in order to have correct data in your Lansweeper database.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username As [last logged on user],
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join (Select tblCPlogoninfoDistinct.AssetID,
Count(tblCPlogoninfoDistinct.Username) As [number users]
From (Select Distinct tblCPlogoninfo.AssetID,
tblCPlogoninfo.Username
From tblCPlogoninfo
Where tblCPlogoninfo.logontime > GetDate() - 30) tblCPlogoninfoDistinct
Group By tblCPlogoninfoDistinct.AssetID
Having Count(tblCPlogoninfoDistinct.Username) > 1) tCPlogoninfo
On tblAssets.AssetID = tCPlogoninfo.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName