Report with last several users logged in

[lunja]

We recently purchased Lansweeper and are now building reports.
One of the reports we are trying to build is for switches with information about users of the PC's.
Attached is our report that works perfectly, except that we need to know last 3 users logged in onto PC (instead of just last one).
Any chance of helping us achieve that?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets_1.AssetName As Switch,
tblSNMPInfo.ifName As Port,
tblAssets.Description,
tblSNMPAssetMac.LastSeen
From tsysAssetTypes
Inner Join tblAssets On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetMacAddress
On tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex And
tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Inner Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID = tblAssets_1.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.Manufacturer <> 'VMware, Inc.' And
tblComputersystem.Domainrole = 1 And tblAssetCustom.State = 1
Order By Switch,
Port


Thanks,

[lunja]

Thanks, that will be perfect.

[Hemoco · Accepted solution]

We have made a report where you can see the last 3 logon attempts for each asset. It is impossible to make report that show you the last 3 user that have logged in to an asset.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblAssets_1.AssetName As Switch,
tblSNMPInfo.ifName As Port,
tblAssets.Description,
tblSNMPAssetMac.LastSeen,
q1.Username,
q1.logontime
From tsysAssetTypes
Inner Join tblAssets On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetMacAddress
On tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex And
tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Inner Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID = tblAssets_1.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join (Select Top 1000000 P.AssetID As ID,
P.logontime,
P.Username
From (Select tblAssets.AssetID,
tblCPlogoninfo.logontime,
tblCPlogoninfo.Username,
Row_Number() Over (Partition By tblAssets.AssetID Order By
tblCPlogoninfo.logontime Desc) As Seq
From tblAssets
Inner Join tblCPlogoninfo On tblCPlogoninfo.AssetID = tblAssets.AssetID) P
Where P.Seq <= 3
Order By ID,
P.logontime Desc) As q1 On q1.ID = tblAssets.AssetID
Where tblAssetCustom.Manufacturer <> 'VMware, Inc.' And
tblComputersystem.Domainrole = 1 And tblAssetCustom.State = 1
Order By Switch,
Port,
tblAssets.AssetName