Windows: Last logon detected during scanning, per user (Built-in)

[Nick_VDB]

Added in v.6.0.100

The report below will give back Windows computers the last users that logged on to the computer.

The report will only list assets that meet all of the following criteria:

• The asset state is set to "active".
  
• The asset has been successfully scanned at least once.
  
• The asset is a Windows computer.
  
• The asset has user logon information.
  

Select Top 1000000 UserInfo.Username,
  UserInfo.Domain As Userdomain,
  UserInfo.LastLogon As UserLastLogon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried
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 tblState.Statename = 'Active'
Order By Userdomain,
  UserInfo.Username