→ 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: 
garybray
Engaged Sweeper II

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!

1.png

1 ACCEPTED SOLUTION

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

View solution in original post

8 REPLIES 8
garybray
Engaged Sweeper II

Thanks Brandon!  You're a ROCKSTAR!

brandon_jones
Champion Sweeper III

You're welcome.

brandon_jones
Champion Sweeper III

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

I just replaced the code with the above and this is what I get now....08-01-2023 1-01-05 PM.png

garybray
Engaged Sweeper II

Thank you!  It kind of does....but it only pulled 2 of the 210 we have.08-01-2023 12-28-21 PM.png

brandon_jones
Champion Sweeper III

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

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

Thank you so much for your detailed response @brandon_jones  Keep up the great work!