→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JTempleton
Engaged Sweeper III
I am looking for a little help with my report. Currently, it will show multiple users for each computer. How can I get it only show the last user who logged onto that computer? Here is my report:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tsysIPLocations.IPLocation As [Office Location],
tblAssets.AssetName As [Computer Name],
tblAssets.Username As [Last Logon User],
tblAssets.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Serial Number],
tblAssets.Memory,
ProcCapacity.CPU,
tblProcessor.NumberOfLogicalProcessors As Procs,
tblProcessor.NumberOfCores As Cores,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.OSname As [Operating System],
tblAssets.IPAddress,
tblAssets.Lastseen
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tsysIPLocations.IPLocation Like '%'
Order By [Office Location],
[Computer Name]

Thanks for all the help!
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please use the following report:

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tsysIPLocations.IPLocation As [Office Location],
tblAssets.AssetName As [Computer Name],
tblAssets.Username As [Last Logon User],
tblAssets.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Serial Number],
tblAssets.Memory,
ProcCapacity.CPU,
tblProcessor.NumberOfLogicalProcessors As Procs,
tblProcessor.NumberOfCores As Cores,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.OSname As [Operating System],
tblAssets.IPAddress,
tblAssets.Lastseen
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tsysIPLocations.IPLocation Like '%'
Order By [Office Location],
[Computer Name]

View solution in original post

2 REPLIES 2
JTempleton
Engaged Sweeper III
Thank you so much!!
Hemoco
Lansweeper Alumni
Please use the following report:

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tsysIPLocations.IPLocation As [Office Location],
tblAssets.AssetName As [Computer Name],
tblAssets.Username As [Last Logon User],
tblAssets.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Serial Number],
tblAssets.Memory,
ProcCapacity.CPU,
tblProcessor.NumberOfLogicalProcessors As Procs,
tblProcessor.NumberOfCores As Cores,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.OSname As [Operating System],
tblAssets.IPAddress,
tblAssets.Lastseen
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where tsysIPLocations.IPLocation Like '%'
Order By [Office Location],
[Computer Name]