‎08-01-2023 05:55 PM - edited ‎08-01-2023 06:19 PM
We have approximately 210+ Dell Latitude 7490 laptops being scanned. I want to create a report to show the Last user: for each of those machines by service tag, so that I can cross reference which of those users have already received updated equipment/models. That way I can start removing/retiring these old 7490's. Can someone please help me in creating a report that will accomplish this? Right now I've just been doing a mouseover and looking at the last user, but this is very time consuming. Thank You!
Solved! Go to Solution.
‎08-01-2023 08:06 PM
Opps.. I made a typo. This should work:
Select Distinct Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblAssetCustom.Serialnumber,
tblAssetCustom.Model,
UserInfo.Username
From tblassets
Inner Join tblCPlogoninfo On tblassets.AssetID = tblCPlogoninfo.AssetID
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Inner Join tblAssetCustom On tblAssetCustom.AssetID =
tblCPlogoninfo.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active'
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) As UserInfo On tblCPlogoninfo.Domain =
UserInfo.Domain And tblCPlogoninfo.logontime = UserInfo.LastLogon And
tblCPlogoninfo.Username = UserInfo.Username
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblassets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblassets.LocationID
Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblassets.OScode = tsysOS.OScode
Where tblAssetCustom.Model Like '%Latitude 7490%' And tblState.Statename =
'Active'
Order By UserInfo.Username
‎08-01-2023 08:08 PM
Thanks Brandon! You're a ROCKSTAR!
‎08-01-2023 08:11 PM
You're welcome.
‎08-01-2023 07:56 PM
I had to refine it a little:
Select Distinct Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblAssetCustom.Serialnumber,
tblAssetCustom.Model,
UserInfo.Username
From tblassets
Inner Join tblCPlogoninfo On tblassets.AssetID = tblCPlogoninfo.AssetID
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Inner Join tblAssetCustom On tblAssetCustom.AssetID =
tblCPlogoninfo.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active'
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) As UserInfo On tblCPlogoninfo.Domain =
UserInfo.Domain And tblCPlogoninfo.logontime = UserInfo.LastLogon And
tblCPlogoninfo.Username = UserInfo.Username
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblassets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblassets.LocationID
Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblassets.OScode = tsysOS.OScode
Where tblAssetCustom.Model Like '% Latitude 7490%' And tblState.Statename =
'Active'
Order By UserInfo.Username
‎08-01-2023 08:02 PM
I just replaced the code with the above and this is what I get now....
‎08-01-2023 07:30 PM - edited ‎08-01-2023 07:30 PM
Thank you! It kind of does....but it only pulled 2 of the 210 we have.
‎08-01-2023 07:11 PM
See if this is what you need. This report includes only the Dell Latitude 7490's.
Select Distinct Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblassetcustom.Serialnumber,
tblassetcustom.Model,
tblassets.Username
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tblUsersHist On tblassets.AssetID = tblUsersHist.AssetID
Where tblassetcustom.State = 1 And tblassetcustom.Model Like '%Latitude 7490%'
Order By tblassetcustom.Serialnumber
‎08-01-2023 08:06 PM
Opps.. I made a typo. This should work:
Select Distinct Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblAssetCustom.Serialnumber,
tblAssetCustom.Model,
UserInfo.Username
From tblassets
Inner Join tblCPlogoninfo On tblassets.AssetID = tblCPlogoninfo.AssetID
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Inner Join tblAssetCustom On tblAssetCustom.AssetID =
tblCPlogoninfo.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active'
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) As UserInfo On tblCPlogoninfo.Domain =
UserInfo.Domain And tblCPlogoninfo.logontime = UserInfo.LastLogon And
tblCPlogoninfo.Username = UserInfo.Username
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblassets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblassets.LocationID
Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblassets.OScode = tsysOS.OScode
Where tblAssetCustom.Model Like '%Latitude 7490%' And tblState.Statename =
'Active'
Order By UserInfo.Username
‎08-02-2023 10:36 AM
Thank you so much for your detailed response @brandon_jones Keep up the great work!
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now