cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
feffrey
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

5 REPLIES 5
feffrey
Engaged Sweeper
Thank you! That is working great!
Hemoco
Lansweeper Alumni
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
Engaged Sweeper
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
Champion Sweeper
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
Lansweeper Alumni
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