
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-07-2013 01:39 PM
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!
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!
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-10-2013 07:13 PM
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'
4 REPLIES 4

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-11-2013 12:31 PM
Thanks, works perfectly!

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-10-2013 07:13 PM
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'

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-10-2013 12:43 PM
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.
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.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-07-2013 03:13 PM
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
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
