OU users and the machine they last were logged on

[NoZart]

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!

[NoZart]

Thanks, works perfectly!

[Hemoco · Accepted solution]

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]

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]

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