SQL Query for Assets Primary User

Cody_Mathis

Hello, I'm looking to query for the Hardware Assets primary user but I'm not really seeing where this might be in the tables. Any tips?

charlemagne_abonado

Hi Cody,

Can you try this query below?
This is looking on hardware asset table, relationship table, RelationshipType Type and MT_Microsoft$AD$UserBase table.

SELECT TOP 1000 HW.BaseManagedEntityId,

 HW.DisplayName,

 Rel.TargetEntityId,

 UB.OrganizationalUnit_13D8EA29_A659_92AE_C78F_8047469D8C96,

 UB.UPN_7641DFF7_7A20_DC04_FC1C_B6FA8715DA02

  FROM MT_Cireson$AssetManagement$HardwareAsset HW

  inner join Relationship Rel on HW.BaseManagedEntityId = Rel.SourceEntityId

  inner join RelationshipType RT on RT.RelationshipTypeId = Rel.RelationshipTypeId

  inner join MT_Microsoft$AD$UserBase UB on UB.BaseManagedEntityId = Rel.TargetEntityId

  where RT.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasPrimaryUser'

Thanks

james_kleinschnitz

What databases are you looking in?

Cody_Mathis

I've been looking in both ServiceManager and ServiceManagement. I see the Cireson Hardware Asset table, but no columns seem to fit the bill. I see some potential columns I could use to join to another table, but I'm not so sure where the data is that I want to join to.

charlemagne_abonado

Hi Cody,

Can you try this query below?
This is looking on hardware asset table, relationship table, RelationshipType Type and MT_Microsoft$AD$UserBase table.

SELECT TOP 1000 HW.BaseManagedEntityId,

 HW.DisplayName,

 Rel.TargetEntityId,

 UB.OrganizationalUnit_13D8EA29_A659_92AE_C78F_8047469D8C96,

 UB.UPN_7641DFF7_7A20_DC04_FC1C_B6FA8715DA02

  FROM MT_Cireson$AssetManagement$HardwareAsset HW

  inner join Relationship Rel on HW.BaseManagedEntityId = Rel.SourceEntityId

  inner join RelationshipType RT on RT.RelationshipTypeId = Rel.RelationshipTypeId

  inner join MT_Microsoft$AD$UserBase UB on UB.BaseManagedEntityId = Rel.TargetEntityId

  where RT.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasPrimaryUser'

Thanks