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 Number Of Application Hang Event ID 1002 Count , Null
by  HRS   Go to last post Go to first unread
Last post: 11/29/2019 9:42:49 PM(UTC)
Action Change Windows domain PC Name
by  DaveDischord  
Go to last post Go to first unread
Last post: 11/27/2019 10:36:02 PM(UTC)
Lansweeper LsRemote.exe Background Image Removal
by  Martin Frey   Go to last post Go to first unread
Last post: 11/27/2019 11:40:23 AM(UTC)
Lansweeper List of Software Publisher´s
by  fuesselorg  
Go to last post Go to first unread
Last post: 11/22/2019 5:11:42 PM(UTC)
Lansweeper Infopath installer help
by  Dave Ward   Go to last post Go to first unread
Last post: 11/12/2019 11:16:51 AM(UTC)
Lansweeper Remote Registry 2019
by  gareauk  
Go to last post Go to first unread
Last post: 10/24/2019 7:33:06 PM(UTC)
Lansweeper Deploy
by  CyberCitizen   Go to last post Go to first unread
Last post: 10/10/2019 2:31:27 AM(UTC)
Action Backup Computer with Disk2VHD to network share
by  pryan67  
Go to last post Go to first unread
Last post: 10/7/2019 3:36:05 PM(UTC)