Notification

Icon
Error

Getting only the first Owner of an Asset - View of assets for CMDB in Service Desk requring first owner only.

Posted: Monday, July 22, 2019 3:45:30 PM(UTC)
ToMonkey

ToMonkey

Member Original PosterPosts: 7
0
Like
This issue has been solved! Click here to view the solution
I've wrote this report so I can import the data into our helpdesk systems CMDB, this is a nightly process and the CMDB is a read only direct from Lansweeper. This report gives me all the detail except I've been trying to get only the latest owner where there are multiples. I can get them all but I only need the latest.

Does anyone have any idea how I would do this. I've been working on this now for over a week, I've scoured the forums but not found anything that will work.

I wanted to do the subquery descending on start date and only the top 1 but when I did that I only got one record instead of 2000.

I'm stuck, any ideas?



Select Top 10000 tblAssets.AssetID As [Asset System ID],
tblAssets.AssetID As RecID,
tsysIPLocations.IPLocation As [Location - Building],
tblAssets.AssetName As [Host Name],
tsysOS.OSname As [Operating System],
tblAssets.Domain As [User Domain],
Left(tblAssetCustom.Serialnumber, 80) As [Serial Number],
Left(tblAssetCustom.Model, 80) As Model,
ProcCapacity.CPU As [CPU Type],
ProcCapacity.Name As [CPU Edition],
ProcCapacity.MaxClockSpeed As [CPU Speed],
ProcCapacity.NrOfProcessors As [Number CPUs],
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expires],
tblSystemEnclosure.ChassisTypes,
tblComputersystem.Domainrole,
tblAssets.Memory As [Physical Memory],
tblAssets.Mac As [Mac Address],
tblAssets.Lastseen As [Last Modified Date Time],
tblAssets.Firstseen As [Created Date Time],
tblAssets.Assettype,
tblSystemEnclosure.Manufacturer,
TsysChassisTypes.ChassisName As [Computer Type],
'LANSweeper' As [Asset Source],
Case
When (tblState.Statename) = 'Active' Then 'Active'
When (tblState.Statename) = 'Broken' Then 'Down'
When (tblState.Statename) = 'In repair' Then 'In Repair'
When (tblState.Statename) = 'Stock' Then 'In Stock'
When (tblState.Statename) = 'Spare' Then 'In Stock'
When (tblState.Statename) = 'Non-active' Then 'Retired'
When (tblState.Statename) = 'Stolen' Then 'Stolen/MIA'
Else 'Stolen/MIA'
End As [Selected Status],
Left(tblAssets.SP, 30) As [Operating System Service Pack],
tblAssets.IPAddress As [IP Address],
tblOperatingsystem.SerialNumber As [Operating System License Key],
tblOperatingsystem.Version As [Operating System Version],
tblOperatingsystem.OSLanguage As [Operating System Country Code],
Left(tblBIOS.SMBIOSBIOSVersion, 50) As [BIOS Version],
tblBIOS.ReleaseDate As [BIOS Date],
tblBIOS.Manufacturer As BIOS,
SubQuery.Username As [User Name],
tblADusers.Name As [Primary User Full Name],
tblADusers.email As [Primary User Email],
Case
When (tsysIPLocations.IPLocation) = 'xxxx' Then 'xxxxx'
Else 'Unknown'
End As [Owned By Team],
Case
When (tsysIPLocations.IPLocation) = 'xxxx' Then 'xxxx'
Else 'Unknown'
End As [Owned By],
Case
When (tsysIPLocations.IPLocation) = 'xxxx' Then 'xxxx'
Else 'Unknown'
End As [Owned By Email]
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Left Join (Select Top 10000 tblAssetUserRelations.AssetID,
tsysAssetRelationTypes.Name,
tblAssetUserRelations.Username,
tblAssetUserRelations.Userdomain,
tblAssetUserRelations.Comments,
tblAssetUserRelations.StartDate
From tblAssetUserRelations
Inner Join tsysAssetRelationTypes On
tsysAssetRelationTypes.RelationTypeID = tblAssetUserRelations.Type
Where tsysAssetRelationTypes.Name = 'owned by'
Order By tblAssetUserRelations.StartDate) SubQuery On SubQuery.AssetID =
tblAssets.AssetID
Inner Join tblADusers On tblADusers.Username = SubQuery.Username And
tblADusers.Userdomain = SubQuery.Userdomain
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblComputersystem.Domainrole = 1
Order By [Location - Building],
[Host Name]
ToMonkey
#1ToMonkey Member Original PosterPosts: 7  
posted: 7/23/2019 9:57:08 AM(UTC)
Worked it out finally, I added another sub query to give me the max date and then used that in the first sub query to get only one record, it works now.

Other things I tried, sorting the first sub query in descending order and choosing only the top 1, using the max clause in the first sub query neither worked.

Maybe will help someone else though.



Select Top 10000 tblAssets.AssetID As [Asset System ID],
tblAssets.AssetID As RecID,
tsysIPLocations.IPLocation As [Location - Building],
tblAssets.AssetName As [Host Name],
tsysOS.OSname As [Operating System],
tblAssets.Domain As [User Domain],
Left(tblAssetCustom.Serialnumber, 80) As [Serial Number],
Left(tblAssetCustom.Model, 80) As Model,
ProcCapacity.CPU As [CPU Type],
ProcCapacity.Name As [CPU Edition],
ProcCapacity.MaxClockSpeed As [CPU Speed],
ProcCapacity.NrOfProcessors As [Number CPUs],
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expires],
tblSystemEnclosure.ChassisTypes,
tblComputersystem.Domainrole,
tblAssets.Memory As [Physical Memory],
tblAssets.Mac As [Mac Address],
tblAssets.Lastseen As [Last Modified Date Time],
tblAssets.Firstseen As [Created Date Time],
tblAssets.Assettype,
tblSystemEnclosure.Manufacturer,
TsysChassisTypes.ChassisName As [Computer Type],
'LANSweeper' As [Asset Source],
Case
When (tblState.Statename) = 'Active' Then 'Active'
When (tblState.Statename) = 'Broken' Then 'Down'
When (tblState.Statename) = 'In repair' Then 'In Repair'
When (tblState.Statename) = 'Stock' Then 'In Stock'
When (tblState.Statename) = 'Spare' Then 'In Stock'
When (tblState.Statename) = 'Non-active' Then 'Retired'
When (tblState.Statename) = 'Stolen' Then 'Stolen/MIA'
Else 'Stolen/MIA'
End As [Selected Status],
Left(tblAssets.SP, 30) As [Operating System Service Pack],
tblAssets.IPAddress As [IP Address],
tblOperatingsystem.SerialNumber As [Operating System License Key],
tblOperatingsystem.Version As [Operating System Version],
tblOperatingsystem.OSLanguage As [Operating System Country Code],
Left(tblBIOS.SMBIOSBIOSVersion, 50) As [BIOS Version],
tblBIOS.ReleaseDate As [BIOS Date],
tblBIOS.Manufacturer As BIOS,
SubQuery.Username As [User Name],
SubQuery1.ThDate,
tblADusers.Name As [Primary User Full Name],
tblADusers.email As [Primary User Email],
Case
When (tsysIPLocations.IPLocation) = 'xxxx' Then 'xxxxx'
Else 'Unknown'
End As [Owned By Team],
Case
When (tsysIPLocations.IPLocation) = 'xxxx' Then 'xxxx'
Else 'Unknown'
End As [Owned By],
Case
When (tsysIPLocations.IPLocation) = 'xxxx' Then 'xxxx'
Else 'Unknown'
End As [Owned By Email]
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Left Join (Select Top 10000 tblAssetUserRelations.AssetID,
Max(tblAssetUserRelations.StartDate) As ThDate
From tblAssetUserRelations
Where tblAssetUserRelations.Type = 1
Group By tblAssetUserRelations.AssetID) SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Left Join (Select Top 10000 tblAssetUserRelations.AssetID,
tsysAssetRelationTypes.Name,
tblAssetUserRelations.Username,
tblAssetUserRelations.Userdomain,
tblAssetUserRelations.Comments,
tblAssetUserRelations.StartDate
From tblAssetUserRelations
Inner Join tsysAssetRelationTypes On
tsysAssetRelationTypes.RelationTypeID = tblAssetUserRelations.Type
Where tsysAssetRelationTypes.Name = 'owned by'
Order By tblAssetUserRelations.StartDate) SubQuery On SubQuery.AssetID =
tblAssets.AssetID And SubQuery.StartDate = SubQuery1.ThDate
Inner Join tblADusers On tblADusers.Username = SubQuery.Username And
tblADusers.Userdomain = SubQuery.Userdomain
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblComputersystem.Domainrole = 1
Order By [Location - Building],
[Host Name]

Active Discussions

Lansweeper Modify "....less than 1GB free HD..." Report
by  Cripple.Zero   Go to last post Go to first unread
Last post: 8/15/2019 7:11:10 PM(UTC)
Lansweeper Individual asset compliance
by  ToeCutter  
Go to last post Go to first unread
Last post: 8/15/2019 11:40:57 AM(UTC)
Lansweeper Nvidia August Security Bulletin
by  Esben.D   Go to last post Go to first unread
Last post: 8/14/2019 9:00:17 AM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  Esben.D  
Go to last post Go to first unread
Last post: 8/14/2019 8:40:45 AM(UTC)
Lansweeper Help Desk report charted by Fiscal Year
by  Tom Londe   Go to last post Go to first unread
Last post: 8/12/2019 11:39:05 PM(UTC)
Lansweeper Windows: Unauthorized Administrators (Built-in)
by  bnishan  
Go to last post Go to first unread
Last post: 8/12/2019 9:02:30 PM(UTC)
Report Center Top 5 Average RAM Usage
by  AlbertD   Go to last post Go to first unread
Last post: 8/12/2019 5:36:20 PM(UTC)
Lansweeper SWAPGS Report
by  Bginchereau  
Go to last post Go to first unread
Last post: 8/12/2019 2:25:22 PM(UTC)