→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Steffen82
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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

View solution in original post

3 REPLIES 3
Susan_A
Lansweeper Alumni
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
Engaged Sweeper
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
Lansweeper Alumni
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