cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jmichiels
Engaged Sweeper III
Hello,

I want make/have a query that show a list with user that have more then 1 computers as last logged in.
In that way we can figure out who have still old computer or second computer which is not allowed for us.
Does somebody have similar report or can create for me and another users.
Thanks! Lansweeper is great software!
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
jmichiels wrote:
Hello

I want make/have a query that show a list with user that have more then 1 computers as last logged in.
In that way we can figure out who have still old computer or second computer which is not allowed for us.
(working good)

But I noticed that the report also include secondary PC that are not-active which make just 1 computer and not more then one PC.


Is it possible to configure only show computers and secondary pc that are also active.

You can use the modified version of Daniel's report below to only take active computers into account.
Select Top 1000000 tblAssets.AssetID,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select tblAssets.Username,
tblAssets.Userdomain,
Count(tblAssets.AssetID) As number
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.Username,
tblAssets.Userdomain
Having Count(tblAssets.AssetID) > 1) tCount On tCount.Username =
tblAssets.Username And tCount.Userdomain = tblAssets.Userdomain
Where tblAssetCustom.State = 1
Order By tCount.Username

View solution in original post

5 REPLIES 5
jmichiels
Engaged Sweeper III
Hello

I want make/have a query that show a list with user that have more then 1 computers as last logged in.
In that way we can figure out who have still old computer or second computer which is not allowed for us.
(working good)

But I noticed that the report also include secondary PC that are not-active which make just 1 computer and not more then one PC.


Is it possible to configure only show computers and secondary pc that are also active.
Susan_A
Lansweeper Alumni
jmichiels wrote:
Hello

I want make/have a query that show a list with user that have more then 1 computers as last logged in.
In that way we can figure out who have still old computer or second computer which is not allowed for us.
(working good)

But I noticed that the report also include secondary PC that are not-active which make just 1 computer and not more then one PC.


Is it possible to configure only show computers and secondary pc that are also active.

You can use the modified version of Daniel's report below to only take active computers into account.
Select Top 1000000 tblAssets.AssetID,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select tblAssets.Username,
tblAssets.Userdomain,
Count(tblAssets.AssetID) As number
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.Username,
tblAssets.Userdomain
Having Count(tblAssets.AssetID) > 1) tCount On tCount.Username =
tblAssets.Username And tCount.Userdomain = tblAssets.Userdomain
Where tblAssetCustom.State = 1
Order By tCount.Username
jmichiels
Engaged Sweeper III
Thanks! That is exactly what I want see as report!
Daniel_B
Lansweeper Alumni
With a subquery which counts the number of assets having one specific user as last logged on user you can get this list:

Select Top 1000000 tblAssets.AssetID,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select tblAssets.Username,
tblAssets.Userdomain,
Count(tblAssets.AssetID) As number
From tblAssets
Group By tblAssets.Username,
tblAssets.Userdomain
Having Count(tblAssets.AssetID) > 1) tCount On tCount.Username =
tblAssets.Username And tCount.Userdomain = tblAssets.Userdomain
Where tblAssetCustom.State = 1
Order By tCount.Username
MakeBug
Champion Sweeper
You could simply create a report that lists all users with their devices and then sort it by username.
There might be a different/better way but as you are, I'm also too lazy to figure it out at the moment