cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Kreg
Engaged Sweeper II
Hi there,

i would like to know if it's possible to have a query to show :
Computer for a specific asset group with the last connection time and the number of "usage" for the last 3 months.

I have this query but I don't know why I've a lot of entry for each PCs (and I do not see how to show the usage for the last 3 months) :


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssetGroups.AssetGroup,
tblAssets.Username,
tblCPlogoninfo.logontime
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where tblAssetGroups.AssetGroup = 'MyGroup' And tblAssetCustom.State = 1


Many thanks 🙂
Kreg
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
The table tblCPlogoninfo contains the Asset ID, username and time when a user logon was detected by Lansweeper. It does not provide any information about how long the user was logged on. If the computer was not scanned while a user logged on, there will also be no entry. A calculation of uptime or usage time isn't possible through this data.

You could generate a report based on the field tblAssets.Uptime, which contains the number of seconds the machine was online since the last reboot. Possibly you could schedule such a report every evening to get an estimation of computer uptimes.

View solution in original post

3 REPLIES 3
Susan_A
Lansweeper Alumni
The easiest way to do this would be per computer. See sample report below.
Select Distinct Top 5 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.Image As icon,
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblCPlogoninfo.logontime
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetName = 'your computer name' And tblAssetCustom.State = 1
Order By tblCPlogoninfo.logontime Desc
Kreg
Engaged Sweeper II
Many thanks for your answer.
I'll do differently

Moreover, can I show the 5 last users connect to these computers?
Daniel_B
Lansweeper Alumni
The table tblCPlogoninfo contains the Asset ID, username and time when a user logon was detected by Lansweeper. It does not provide any information about how long the user was logged on. If the computer was not scanned while a user logged on, there will also be no entry. A calculation of uptime or usage time isn't possible through this data.

You could generate a report based on the field tblAssets.Uptime, which contains the number of seconds the machine was online since the last reboot. Possibly you could schedule such a report every evening to get an estimation of computer uptimes.