Notification

Icon
Error

Adding top user to asset report

Posted: Friday, September 20, 2013 5:30:08 PM(UTC)
feffrey

feffrey

Member Original PosterPosts: 11
0
Like
This issue has been solved! Click here to view the solution
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.

Code:
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
Lansweeper
#1Lansweeper Member Posts: 13,442  
posted: 9/23/2013 5:23:58 PM(UTC)
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
feffrey
#2feffrey Member Original PosterPosts: 11  
posted: 9/24/2013 11:00:23 PM(UTC)
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.
Code:
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
Lansweeper
#3Lansweeper Member Posts: 13,442  
posted: 9/25/2013 10:24:15 AM(UTC)
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
#4feffrey Member Original PosterPosts: 11  
posted: 9/25/2013 6:06:52 PM(UTC)
Thank you! That is working great!
Argon0
#5Argon0 Member Posts: 43  
posted: 3/26/2020 5:20:51 PM(UTC)
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.c...n--If-Any.aspx#post62586 I.e. that gets the last device a user has logged into, but...

Any help?

Cheers

Active Discussions

Lansweeper Report creation based on FilePathFull for Installation
by  Pang  
Go to last post Go to first unread
Last post: Yesterday at 2:50:23 PM(UTC)
Lansweeper Software Version Compatibility Report (Solved)
by  RC62N   Go to last post Go to first unread
Last post: 7/13/2020 5:04:10 PM(UTC)
Lansweeper Reports Email Question
by  muffintopman  
Go to last post Go to first unread
Last post: 7/13/2020 3:23:23 PM(UTC)
Lansweeper Ripple20
by  Hendrik.VE  
Go to last post Go to first unread
Last post: 7/8/2020 8:09:35 AM(UTC)
Lansweeper All USB devices connected
by  earmor   Go to last post Go to first unread
Last post: 7/7/2020 1:35:37 PM(UTC)
Lansweeper Software audit with installation date
by  RC62N  
Go to last post Go to first unread
Last post: 7/3/2020 3:39:41 PM(UTC)