→ 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: 
Kreg
Engaged Sweeper II
Hi there,

I'm trying to make a report to sort all my PCs to show the lastest 3 users and logon time by computer.
But actually I don't know how to limit to the last 3 users. Please, do you have an idea how to do that ?

Actually here is my report (that showing all logon connections... so useless! )

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssets.AssetID,
tblAssets.Username,
tsysIPLocations.IPLocation,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.email,
tblADusers.Department,
tblADusers.EmployeeID,
tblOperatingsystem.Caption,
tblAssets.Firstseen,
tblBIOS.Manufacturer,
tblAssetCustom.Model,
tblBIOS.SerialNumber,
tblstate.Statename,
tblADComputers.OU,
tblOperatingsystem.InstallDate,
tblCPlogoninfo.logontime,
tblCPlogoninfo.Username As Username1
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblstate On tblstate.State = tblAssetCustom.State
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where tblstate.Statename = 'active' And tblComputersystem.Domainrole < 2
Order By tsysIPLocations.IPLocation


Many thanks 🙂
Kreg
1 ACCEPTED SOLUTION
Esben_D
Lansweeper Employee
Lansweeper Employee
An example of such a report can be found below. The report works with the ROW_NUMBER function to be able to limit the results displayed per asset. More information about the function can be found here: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
subquery1.Username,
subquery1.Domain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
subquery1.logontime,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select Row_Number() Over (Partition By tblCPlogoninfo.AssetID Order
By tblCPlogoninfo.AssetID) As RowNumber,
tblCPlogoninfo.AssetID,
tblCPlogoninfo.logontime,
tblCPlogoninfo.Domain,
tblCPlogoninfo.Username
From tblCPlogoninfo) As subquery1 On subquery1.AssetID = tblAssets.AssetID
Where subquery1.RowNumber < 4 And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
subquery1.logontime Desc

View solution in original post

2 REPLIES 2
Esben_D
Lansweeper Employee
Lansweeper Employee
An example of such a report can be found below. The report works with the ROW_NUMBER function to be able to limit the results displayed per asset. More information about the function can be found here: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
subquery1.Username,
subquery1.Domain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
subquery1.logontime,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select Row_Number() Over (Partition By tblCPlogoninfo.AssetID Order
By tblCPlogoninfo.AssetID) As RowNumber,
tblCPlogoninfo.AssetID,
tblCPlogoninfo.logontime,
tblCPlogoninfo.Domain,
tblCPlogoninfo.Username
From tblCPlogoninfo) As subquery1 On subquery1.AssetID = tblAssets.AssetID
Where subquery1.RowNumber < 4 And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
subquery1.logontime Desc
Kreg
Engaged Sweeper II
Hi,

Any chance to do that ? You think is it possible ?
Thanks

Kreg

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now