Hardware list custom report - duplicate devices/rows when including "Used By" association

[terryas]

Hey everyone!
I've created a custom report for our cybersecurity folks, but am having trouble with duplicate rows/devices.
One of the data points they'd like in the report is the current/last user assigned to the device. We've been using the "Used by" user association to map relationships between endpoints and the users they're assigned too. 

However, whenever I run the report I end up with duplicate rows if a device has had multiple "used by" associations (i.e. - an entry for user "John" that ended a month ago, and a new one for user "Jane" that began a month ago)

Below is the SQL of the query the report uses, I'm hoping someone knows a quick/easy way of only including the last (if any) "used by" association; to prevent duplicate rows in the report, or has any other constructive feedback. I'm definitely not a SQL expert, so I'm sure the query is far from ideal

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.Image As icon,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname + ', ' + tblAssets.Version + ' (' + tblAssets.OScode +
  ')' As 'Firmware Version',
  tblAssetCustom.Custom1 As [IA or IA-Enabled],
  tblAssetCustom.Custom2 As Purpose,
  tblAssetCustom.Custom3 As [Virtual Device],
  tblAssetCustom.Custom4 As [IP range (If DHCP Device)],
  tblAssets.IPAddress As [IP Address],
  tsysIPLocations.IPLocation As [IP Location],
  tblAssetCustom.Serialnumber As [Serial Number],
  tblAssets.Mac As [Mac Address],
  tblAssetCustom.BarCode,
  tblAssetCustom.Custom5 As [DITAC Internal],
  tblAssetUserRelations.Username
From tblAssets
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblAssetUserRelations On
      tblAssets.AssetID = tblAssetUserRelations.AssetID
Where tblAssets.AssetID In (Select tblAssetGroupLink.AssetID
    From tblAssetGroupLink
    Where tblAssetGroupLink.AssetGroupID = 24) And tblAssetCustom.State = 1 And
  (tblAssetUserRelations.Type = 12 Or tblAssetUserRelations.Type Is Null)
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.Image,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname + ', ' + tblAssets.Version + ' (' + tblAssets.OScode + ')',
  tblAssetCustom.Custom1,
  tblAssetCustom.Custom2,
  tblAssetCustom.Custom3,
  tblAssetCustom.Custom4,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Serialnumber,
  tblAssets.Mac,
  tblAssetCustom.BarCode,
  tblAssetCustom.Custom5,
  tblAssetUserRelations.Username
Order By tblAssets.AssetName

[balebaje]

Good afternoon, I have used this code and it works fine, but for some time now I need to add to this report the machines that the user logs in, i.e. the "Assets Relations" + "computers logged". I'm thinking about it but I can't get it, any help please?

Thanks

[valentyn]

Select Top 1000000 tblAssets.AssetID,
tblAssetCustom.Custom8 As [PM ID],
tblAssetCustom.Custom1 As ID,
tblAssetCustom.Custom2 As TAG,
tblAssets.AssetName,
tblAssets.Username,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Custom3 As [HW Type],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblState.Statename As State,
tsysAssetRelationTypes.RelationTypeIcon10 As Icon3,
tsysAssetRelationTypes.Name As RelationType,
Case
When tblAssetUserRelations.Userdomain Is Null Then ''
When tblADusers.Displayname Is Null Or
tblADusers.Displayname = '' Then tblAssetUserRelations.Userdomain + '\' +
tblAssetUserRelations.Username
Else tblADusers.Displayname
End As hyperlink_name_RelationUser,
tblAssetCustom.Custom4 As Holding,
Case
When tblAssetUserRelations.Userdomain Is Null Then ''
Else '/user.aspx?username=' + tblAssetUserRelations.Username +
'&userdomain=' + tblAssetUserRelations.Userdomain
End As hyperlink_RelationUser,
tblAssetCustom.Comments,
tblAssets.Description,
Case
When tblAssetUserRelations.EndDate < GetDate() Then '#dddddd'
Else '#ffffff'
End As backgroundcolor,
tblAssetUserRelations.StartDate As RelationStartDate,
tblAssets.Lastseen As [last successful scan],
tblAssetUserRelations.Comments As RelationComments,
tblAssetCustom.Custom5 As Floor,
tblAssetCustom.Custom6 As Office,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.KernelVersion)
As OS,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Serialnumber,
tblAssets.Mac,
tblAssetCustom.BarCode As Barcode,
tblAssets.Firstseen As [Create at],
tblAssetCustom.PurchaseDate,
tblAssets.Processor As Processor,
tblAssets.Memory,
tblAssetCustom.Custom9 As [Licensed laptop],
tblAssetCustom.Custom17 As ABM,
tblAssetCustom.Custom14 As [Policy Сompliance],
tblADusers.email,
tblAssetCustom.Location,
tblAssetCustom.Custom12 As site,
tblAssetCustom.Custom13 As Supplier,
tblAssetCustom.Custom16 As [Price(EUR)],
tblAssetUserRelations.EndDate,
tblAssetCustom.Custom15,
tblAssetCustom.Custom18
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Left Outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Outer Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Outer Join tblADusers On
tblADusers.Userdomain = tblAssetUserRelations.Userdomain And
tblADusers.Username = tblAssetUserRelations.Username
Where tblAssetUserRelations.EndDate Is Null
Order By tblAssetUserRelations.EndDate Desc,
RelationStartDate Desc,
tblAssets.AssetName,
tblAssets.Domain

[balebaje]

Thanks for your help Valentyn, but I keep getting the same thing, all the "Assets Relations" and not the "computers logged", what am I missing? I'm desperate, normally all the queries I do come up, but this one I've been doing for several days and it's impossible.

[valentyn]

Try this code, I think it will help to see only the current relation

Select Top 1000000 tblAssets.AssetID,
tblAssetCustom.Custom8 As [PM ID],
tblAssetCustom.Custom1 As ID,
tblAssetCustom.Custom2 As TAG,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Custom3 As [HW Type],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblState.Statename As State,
tsysAssetRelationTypes.RelationTypeIcon10 As Icon3,
tsysAssetRelationTypes.Name As RelationType,
Case
When tblAssetUserRelations.Userdomain Is Null Then ''
When tblADusers.Displayname Is Null Or
tblADusers.Displayname = '' Then tblAssetUserRelations.Userdomain + '\' +
tblAssetUserRelations.Username
Else tblADusers.Displayname
End As hyperlink_name_RelationUser,
tblAssetCustom.Custom4 As Holding,
Case
When tblAssetUserRelations.Userdomain Is Null Then ''
Else '/user.aspx?username=' + tblAssetUserRelations.Username +
'&userdomain=' + tblAssetUserRelations.Userdomain
End As hyperlink_RelationUser,
tblAssetCustom.Comments,
tblAssets.Description,
Case
When tblAssetUserRelations.EndDate < GetDate() Then '#dddddd'
Else '#ffffff'
End As backgroundcolor,
tblAssetUserRelations.StartDate As RelationStartDate,
tblAssets.Lastseen As [last successful scan],
tblAssetUserRelations.Comments As RelationComments,
tblAssetCustom.Custom5 As Floor,
tblAssetCustom.Custom6 As Office,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.KernelVersion)
As OS,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Serialnumber,
tblAssets.Mac,
tblAssetCustom.BarCode As Barcode,
tblAssets.Firstseen As [Create at],
tblAssetCustom.PurchaseDate,
tblAssets.Processor As Processor,
tblAssets.Memory,
tblAssetCustom.Custom9 As [Licensed laptop],
tblAssetCustom.Custom17 As ABM,
tblAssetCustom.Custom14 As [Policy Сompliance],
tblADusers.email,
tblAssetCustom.Location,
tblAssetCustom.Custom12 As site,
tblAssetCustom.Custom13 As Supplier,
tblAssetCustom.Custom16 As [Price(EUR)],
tblAssetUserRelations.EndDate,
tblAssetCustom.Custom15,
tblAssetCustom.Custom18
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Left Outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Outer Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Outer Join tblADusers On
tblADusers.Userdomain = tblAssetUserRelations.Userdomain And
tblADusers.Username = tblAssetUserRelations.Username
Where tblAssetUserRelations.EndDate Is Null
Order By tblAssetUserRelations.EndDate Desc,
RelationStartDate Desc,
tblAssets.AssetName,
tblAssets.Domain

[mwrobo09]

you could remove the tblAssetUserRelations.username and substitute with tblAssets.Userdomain, tblAssets.Username

This will only show the last person that was the last person that logged into the machine since the last scan.