→ 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: 
denis_ev
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
David_G
Lansweeper Employee
Lansweeper Employee
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

View solution in original post

2 REPLIES 2
denis_ev
Engaged Sweeper
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
Lansweeper Employee
Lansweeper Employee
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