Report to list assets with a change in user

[CollinB]

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

[Daniel_B · Accepted solution]

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