Report last log on computer

[denis_ev]

Hello,

i'm trying to build a report where I can identify unused computers, which are running, but no user logged in within the last month.

This is my query, it shows me all log ons, but I only want to have the most recent.

Select Top 1000000 tblCPlogoninfo.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblCPlogoninfo.logontime,
(DatePart(mm, tblCPlogoninfo.logontime))
From tblCPlogoninfo
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where (DatePart(mm, tblCPlogoninfo.logontime)) < (DatePart(mm, GetDate()))

I'm new to all those reports, need help.
Thank you.

[denis_ev]

Thank you, yes that's right.
Well, we got the lansweeper agent connection as soon as someone logs on.
That'd should do the trick 🙂

I know some basic SQL stuff, but I have some trouble with the joins and the grouping,
but I'm working on it.
Have a good day.

Best regards,
Denis

[David_G · Accepted solution]

If I understood you correctly, you want to have a report that shows where no last logged on user was found in the lats month. You will have to keep in mind, though, that we only retrieve the logged on user for an asset if this user was logged on during a scan. We have provided you with a report that will give back assets where no last logged on user was found in the last 30 days. 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 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
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() - 30) And tsysAssetTypes.AssetTypename
  Like 'Windows%'
Order By tblAssets.Domain,
  tblAssets.AssetName