Show the last 3 users for a computer

[Kreg]

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

[Esben_D · Accepted solution]

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]

Hi,

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

Kreg