Report for last computers a User has used

[Steffen82]

As part of the discovery project we are doing we need to create a list of all of the users on our domain with:

User ID:
Firstname:
Lastname:
Computers Logged onto:

Can anyone help - it is probably a simple report

This is the information I get if I type my name into Lansweeper

Username: BRITSB3-ADM
Display name: Steffen Burgess
Last Computer: Dczc12434t6

Computers logged onto
4 items found (only last 50 displayed)

Logon Computer Domain Description OS SP
30/06/2015 14:05:24 Dczc12434t6 ds Win 7 1
30/06/2015 14:05:22 Dczc12434t4 ds Win 7 1
30/06/2015 13:20:15 Dczc1074l84 ds Win 7 1

[Susan_A]

Use the report below instead to list only the last logon event for each user.

Select Top 1000000 SubQuery.Username,
  SubQuery.Domain As Userdomain,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.Name,
  tblADusers.Displayname,
  SubQuery.LastLogon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tsysOS.OSname,
  tblAssets.SP
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
  Inner Join tblADusers On tblADusers.Username = SubQuery.Username And
    tblADusers.Userdomain = SubQuery.Domain
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode

[Steffen82]

That's amazing thanks.

The report is massive is it too cheaky to ask how it could be changed to last computer used or last 2 computers used? instead of all of the computers they have used.

Thank you very much for the fast reply though

[Susan_A · Accepted solution]

A sample report that lists the information you are after can be seen below. Instructions for running reports can be found here. If you are interested in building or modifying reports, we would recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Updating to Lansweeper 5.3, if you haven't already. Lansweeper 5.3 includes a database dictionary, which is linked at the top of the report builder. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 tblADusers.Username,
  tblADusers.Userdomain,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.Name,
  tblADusers.Displayname,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tsysOS.OSname,
  tblAssets.SP,
  tblCPlogoninfo.logontime
From tblADusers
  Inner Join tblCPlogoninfo On tblADusers.Username = tblCPlogoninfo.Username And
    tblADusers.Userdomain = tblCPlogoninfo.Domain
  Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Order By tblADusers.Userdomain,
  tblADusers.Username,
  tblCPlogoninfo.logontime Desc