Adding top user to asset report

[feffrey]

I have a report of all of our computers. I would like to add a column that list the user who logs into it the most.
I've seen some example reports and queries, but it looks like most of them list all users, and then the primary machine they use, where I need it reverse. Here is what our report looks like now.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  TsysChassisTypes.ChassisName,
  tblAssetCustom.Serialnumber,
  tblNetwork.MACaddress,
  tsysOS.OSname As OS,
  tsysOS.Image As icon,
  tblAssets.Processor As CPU,
  tblComputersystem.NumberOfLogicalProcessors As Cores,
  Replace(Replace(tblAssetCustom.PurchaseDate, '00:00:00', ''), '12:00AM',
  '') As [Purchase Date],
  Replace(Replace(tblAssetCustom.Warrantydate, '00:00:00', ''), '12:00AM',
  '') As [Warrenty EXP Date],
  tblADComputers.OU As Dept,
  tblState.Statename,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Model <> 'Virtual Machine' And tblNetwork.IPEnabled = 1 And
  tblComputersystem.Domainrole = 1
Order By tblAssets.AssetName

[feffrey]

Thank you! That is working great!

[Hemoco]

feffrey wrote:
is there a way to change that they are still on the report, but it is blank for the user?

Right-click on the link between tblAssets and SubQuery3 and tick "Select all rows from tblAssets".

[feffrey]

That works really good, except that about 105 computers get cut out of the report with that query. 66 are non-active while the rest are active. Others are lab computers that would not have a top user. Some of them are computers that just got deployed, and haven't had anyone login to them yet.
If a top user can't be determined does it drop it off the report? If that is the case is there a way to change that they are still on the report, but it is blank for the user?
I made some changes to the report and added a few things.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  SubQuery3.Username,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  TsysChassisTypes.ChassisName,
  tblAssetCustom.Serialnumber,
  tblNetwork.MACaddress,
  tsysOS.OSname As OS,
  tsysOS.Image As icon,
  tblAssets.Processor As CPU,
  tblComputersystem.NumberOfLogicalProcessors As Cores,
  tblAssets.Memory,
  tblADComputers.OU As Dept,
  Replace(Replace(tblAssetCustom.PurchaseDate, '00:00:00', ''), '12:00AM',
  '') As [Purchase Date],
  Replace(Replace(tblAssetCustom.Warrantydate, '00:00:00', ''), '12:00AM',
  '') As [Warrenty EXP Date],
  tblState.Statename,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join (Select Top 1000000 tblAssets.AssetID,
    Max(SubQuery1.Logins) As MaxLogins
  From tblAssets
    Inner Join (Select Top 1000000 tblAssets.AssetID,
      tblCPlogoninfo.Domain,
      tblCPlogoninfo.Username,
      Count(tblCPlogoninfo.ID) As Logins
    From tblAssets
      Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
    Where tblCPlogoninfo.logontime > GetDate() - 30
    Group By tblAssets.AssetID,
      tblCPlogoninfo.Domain,
      tblCPlogoninfo.Username) SubQuery1 On SubQuery1.AssetID =
      tblAssets.AssetID
  Group By tblAssets.AssetID) SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
  Inner Join (Select Top 1000000 tblAssets.AssetID,
    tblCPlogoninfo.Domain,
    tblCPlogoninfo.Username,
    Count(tblCPlogoninfo.ID) As Logins
  From tblAssets
    Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
  Where tblCPlogoninfo.logontime > GetDate() - 30
  Group By tblAssets.AssetID,
    tblCPlogoninfo.Domain,
    tblCPlogoninfo.Username) SubQuery3 On SubQuery3.AssetID = SubQuery2.AssetID
    And SubQuery3.Logins = SubQuery2.MaxLogins
Where tblAssetCustom.Model <> 'Virtual Machine' And tblNetwork.IPEnabled = 1 And
  tblComputersystem.Domainrole = 1
Order By tblAssets.AssetName

[Argon0]

Great. Almost what I need...

I want to add to this two columns:
1) Last user to log onto asset (is this just username from TBLAssets)
2) Time that user last logged on (which is, I believe, in TBLCPLogoninfo)

I saw a similar one over here: https://www.lansweeper.com/forum/yaf_postsm62586_User-Last-Login--If-Any.aspx#post62586 I.e. that gets the last device a user has logged into, but...

Any help?

Cheers

[Hemoco · Accepted solution]

We have added the Main user based on the logins of the last 30 days.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssetCustom.Serialnumber,
tblNetwork.MACaddress,
tsysOS.OSname As OS,
tsysOS.Image As icon,
tblAssets.Processor As CPU,
tblComputersystem.NumberOfLogicalProcessors As Cores,
Replace(Replace(tblAssetCustom.PurchaseDate, '00:00:00', ''), '12:00AM',
'') As [Purchase Date],
Replace(Replace(tblAssetCustom.Warrantydate, '00:00:00', ''), '12:00AM',
'') As [Warrenty EXP Date],
tblADComputers.OU As Dept,
tblState.Statename,
tblAssets.Lastseen,
SubQuery3.Domain,
SubQuery3.Username,
SubQuery3.Logins
From tblAssets
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join (Select Top 1000000 tblAssets.AssetID,
Max(SubQuery1.Logins) As MaxLogins
From tblAssets
Inner Join (Select Top 1000000 tblAssets.AssetID,
tblCPlogoninfo.Domain,
tblCPlogoninfo.Username,
Count(tblCPlogoninfo.ID) As Logins
From tblAssets
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where tblCPlogoninfo.logontime > GetDate() - 30
Group By tblAssets.AssetID,
tblCPlogoninfo.Domain,
tblCPlogoninfo.Username) SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Group By tblAssets.AssetID) SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
Inner Join (Select Top 1000000 tblAssets.AssetID,
tblCPlogoninfo.Domain,
tblCPlogoninfo.Username,
Count(tblCPlogoninfo.ID) As Logins
From tblAssets
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where tblCPlogoninfo.logontime > GetDate() - 30
Group By tblAssets.AssetID,
tblCPlogoninfo.Domain,
tblCPlogoninfo.Username) SubQuery3 On SubQuery3.AssetID = SubQuery2.AssetID
And SubQuery3.Logins = SubQuery2.MaxLogins
Where tblAssetCustom.Model <> 'Virtual Machine' And tblNetwork.IPEnabled = 1 And
tblComputersystem.Domainrole = 1
Order By tblAssets.AssetName