Usage computer for last 3 months ?

[Kreg]

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

[Susan_A]

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]

Many thanks for your answer.
I'll do differently

Moreover, can I show the 5 last users connect to these computers?

[Daniel_B · Accepted solution]

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.