User not seen in X days

[Ian_Prentice]

Hi folks,

hope someone can help.

Is there, or can someone help with a report that would list AD users who have not logged on in a specific range - say 30 days?

My SQL knowledge is pretty poor, so I don't know where to start in writing one myself.

Thanks!
Ian

[Ian_Prentice]

Thanks for this!

I will go try it out.

Cheers!

[David_G]

You can use the report below to find AD users who have not logged in the last 30 days. You will have to keep in mind, though, that Lansweeper only scans the users logged in to an asset during a scan.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.

• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

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 < GetDate() - 30
Order By Userdomain,
  SubQuery.Username,
  tblAssets.AssetName