trying to create a report for Last user: of a specific model

[garybray]

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!

[garybray]

Thanks Brandon!  You're a ROCKSTAR!

[brandon_jones]

You're welcome.

[brandon_jones]

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

[garybray]

I just replaced the code with the above and this is what I get now....

[garybray]

Thank you!  It kind of does....but it only pulled 2 of the 210 we have.

[brandon_jones]

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

[brandon_jones · 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

[Mercedes_O]

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