Getting only the first Owner of an Asset

[ToMonkey]

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 · Accepted solution]

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]