Create Report : Audit VMs Last Logon > 90Days

[ggalati]

Looking for assistance creating a report that will provide a list of VMs that no one has logged into for at least 90 days, created the below report but does not seem to coincide with the logon times as I see VMs in the list that were recently logged into:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tblOperatingsystem.Caption As OS,
tblADusers.Displayname As [Managed By]
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblADObjects On tblADComputers.ManagerADObjectId =
tblADObjects.ADObjectID
Inner Join tblADusers On tblADObjects.ADObjectID = tblADusers.ADObjectID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
From tblCPlogoninfo Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon Inner Join tblAssets On
tblAssets.AssetID = tblCPlogoninfo.AssetID
Where SubQuery.LastLogon >= GetDate() - 90) And
tblAssetCustom.Manufacturer Like '%vmware%' And tblOperatingsystem.Caption Not
Like '%server%'
Order By tblAssets.AssetName

Any help would be greatly appreciated.

[ASismey · Accepted solution]

Hi, Give this a go :

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblassets.Lasttried,
  tblassetcustom.Manufacturer,
  Query1.[Max Last Logon]
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join (Select Max(Distinct tblCPlogoninfo.logontime) As [Max Last Logon],
      tblCPlogoninfo.AssetID
    From tblCPlogoninfo
    Group By tblCPlogoninfo.AssetID) Query1 On Query1.AssetID =
      tblassets.AssetID
Where tblassetcustom.Manufacturer Like '%vmware%' And Query1.[Max Last Logon] <=
  GetDate() - 90 And tblassetcustom.State = 1