cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
NoZart
Engaged Sweeper II
Hi, i am still learning Lansweeper and try to get my head around the report builder.

I want to
list all users from an OU (done)
now i need to attach the last asset they were logged on (and when)

i am stumped by this simple problem, maybe i can learn from a solution 🙂

Thanks in advance!
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
The report below should work better. (Replace what's marked in bold.) The previous report we provided listed last seen assets and the users that logged into them.
Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
tblADusers.OU,
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
Left Join tblADusers On tblADusers.Username = tblCPlogoninfo.Username And
tblADusers.Userdomain = tblCPlogoninfo.Domain
Where tblADusers.OU = 'OU=Hemoco,DC=lansweeper,DC=local'

View solution in original post

4 REPLIES 4
NoZart
Engaged Sweeper II
Thanks, works perfectly!
Hemoco
Lansweeper Alumni
The report below should work better. (Replace what's marked in bold.) The previous report we provided listed last seen assets and the users that logged into them.
Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
tblADusers.OU,
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
Left Join tblADusers On tblADusers.Username = tblCPlogoninfo.Username And
tblADusers.Userdomain = tblCPlogoninfo.Domain
Where tblADusers.OU = 'OU=Hemoco,DC=lansweeper,DC=local'
NoZart
Engaged Sweeper II
Many thanks, turns out this was a little more complex than i thought 😉

I get some double entries though, user that have logged on more machines get one entry per machine - this is no problem on my side, just FYI.
Hemoco
Lansweeper Alumni
this should work:

Select Top 1000000 tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tblADusers.Displayname, tblAssets.Username) As Displayname,
tblAssets.Lastseen,
tblAssets.AssetID,
tblAssets.AssetName,
Coalesce(tsysOS.Image, 'notscanned.png') As compimage
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tblADusers On tblAssets.Userdomain = tblADusers.Userdomain And
tblAssets.Username = tblADusers.Username
Order By tblAssets.Lastseen Desc