Adding Last user to a report

[Andy_Clay]

I am trying to add the most recent user to the following report for each asset name. It appear the data I want is in tblCPlogoninfo.Username. My problem is this file has multiple entries for each asset name based on the last login time and date so a simple join command does not give me what I need. I am totally clueless on how to build proper SQL queries so I would appreciate anyone willing to revamp this if anyone is game. Thanks in advance.


Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As Serial,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.Image As icon,
tblAssets.Lastseen
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblAssetCustom.PurchaseDate Is Not Null And tblAssetCustom.State = 1) Or
(tblAssetCustom.Warrantydate Is Not Null And tblAssetCustom.State = 1)
Order By [Warranty Expiration] Desc

[Daniel_B]

More information on LsPush can be found in this knowledge base article. LsPush is mainly designed for

• computers without a connection to your network
  
• computers for which you can't provide scanning credentials with administrator permissions
  
• computers which are secured by firewalls and therefore can't be scanned without a scanning agent
  
• issues like reliable scanning of user logons. Lansweeper can't retrieve information about logged on users if the computer wasn't scanned during the time the user was logged on. Therefore initiating an LsPush scan from a logon script helps to come around it

[Andy_Clay]

Daniel, This worked perfectly!... Thanks so much for your time and know how.

You refereed to LsPush in your solution. There seems to be some confusion here on my side of the wire regarding server LsPush. Is there a tech support phone number I could call to allow me to get into very detailed specifics on how the LsPush for the server works for remote PC's?

Thanks again.

[Daniel_B · Accepted solution]

The last scanned user is always stored in tblAssets.username. You just need to add this field to your report.

Note: User logons are only scanned if the user is logged on during scanning. If a user logs on and off while the computer isn't being scanned, Lansweeper won't get to know this. In order to make this report reliable we recommend scanning your computers with LsPush in a logon script.

Select Top 1000000 tblAssetCustom.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Username,
  tsysOS.OSname,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber As Serial,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tsysOS.Image As icon,
  tblAssets.Lastseen
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblAssetCustom.PurchaseDate Is Not Null And tblAssetCustom.State = 1) Or
  (tblAssetCustom.Warrantydate Is Not Null And tblAssetCustom.State = 1)
Order By [Warranty Expiration] Desc