cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
leblanc_daniel_
Engaged Sweeper III
Need a report with asset with Windows 7 ,User name domain, full name and last login

Thanks you!!!!
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
I've included a sample report below. If you are interested in building or modifying reports, we do 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.
  • Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblOperatingsystem.Caption As OS,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblCPlogoninfo.logontime As LastLogon,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As UserDomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblCPlogoninfo.Username And
tblADusers.Userdomain = tblCPlogoninfo.Domain
Inner Join (Select Top 1000000 tblCPlogoninfo.AssetID,
Max(tblCPlogoninfo.logontime) As Max
From tblCPlogoninfo
Group By tblCPlogoninfo.AssetID) SubQuery On SubQuery.AssetID =
tblCPlogoninfo.AssetID And SubQuery.Max = tblCPlogoninfo.logontime
Where tblAssetCustom.State = 1 And tsysOS.OSname = 'win 7'
Order By tblAssets.Domain,
tblAssets.AssetName

View solution in original post

2 REPLIES 2
leblanc_daniel_
Engaged Sweeper III
Thank you Susan!!
Susan_A
Lansweeper Alumni
I've included a sample report below. If you are interested in building or modifying reports, we do 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.
  • Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblOperatingsystem.Caption As OS,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblCPlogoninfo.logontime As LastLogon,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As UserDomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblCPlogoninfo.Username And
tblADusers.Userdomain = tblCPlogoninfo.Domain
Inner Join (Select Top 1000000 tblCPlogoninfo.AssetID,
Max(tblCPlogoninfo.logontime) As Max
From tblCPlogoninfo
Group By tblCPlogoninfo.AssetID) SubQuery On SubQuery.AssetID =
tblCPlogoninfo.AssetID And SubQuery.Max = tblCPlogoninfo.logontime
Where tblAssetCustom.State = 1 And tsysOS.OSname = 'win 7'
Order By tblAssets.Domain,
tblAssets.AssetName