→ 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: 
mlevar
Engaged Sweeper
I'm attempting to run a report to find all devices that either have Config > User Info > Last logon as blank/null or that have a logon time older than X days but i keep getting blank results. Would someone be able to assist? Thank you.

I've used this as a baseline (but this does not include assets with no logon data)


Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblAssets.AssetName,
tblAssets.Domain
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
1 REPLY 1
Andy_Sismey
Champion Sweeper III
Hi ,

I think this should do what you need, I have set the Days to 90 :

Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblAssets.AssetName,
tblAssets.Domain
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 <= DateAdd(day, -90, GetDate()) Or
SubQuery.LastLogon Is Null)