Hardware Asset per Location sql query ?
Stephane_Bouillon
Customer Advanced IT Monkey ✭✭✭
Hi, I'm writing a report on our hardware assets, and I would like to join the asset to the location it is related to. What I have so far is
SELECT HWA.Name_8B938849_A621_7754_D33B_BD8E54DC10B4 as [Asset], <br>CASE WHEN HWA.ObjectStatus_4AE3E5FE_BC03_1336_0A45_80BF58DEE57B = 'acdcedb7-100c-8c91-d664-4629a218bd94' THEN 'Deployed'<br>ELSE 'Other'<br>END as [Status],<br>Manufacturer_C022FE4D_F552_A5A7_15BE_2B1BB900E9A6 as [Manufacturer], Model_A2D42F16_E0FC_C126_4460_F3A252B7C9EF as [Model],<br>YEAR(ReceivedDate_F3EA1950_6656_8251_AB6B_89372E4878DE) as [Year],<br>ReceivedDate_F3EA1950_6656_8251_AB6B_89372E4878DE as [Received]<br>FROM MT_Cireson$AssetManagement$HardwareAsset HWAI would also like to include the warranty start and end dates and the catalog item fields
0
Best Answers
-
Geoff_Ross
Cireson Consultant O.G.
Hello Stephane,
You will need to join to the RelationshipGenericView view for any of these items. Here's the location addition:SELECT HWA.Name_8B938849_A621_7754_D33B_BD8E54DC10B4 as [Asset], CASE WHEN HWA.ObjectStatus_4AE3E5FE_BC03_1336_0A45_80BF58DEE57B = 'acdcedb7-100c-8c91-d664-4629a218bd94' THEN 'Deployed' ELSE 'Other' END as [Status], Manufacturer_C022FE4D_F552_A5A7_15BE_2B1BB900E9A6 as [Manufacturer], Model_A2D42F16_E0FC_C126_4460_F3A252B7C9EF as [Model], YEAR(ReceivedDate_F3EA1950_6656_8251_AB6B_89372E4878DE) as [Year], ReceivedDate_F3EA1950_6656_8251_AB6B_89372E4878DE as [Received], HWALoc.TargetObjectDisplayName as Location --------------------------------NEW FROM MT_Cireson$AssetManagement$HardwareAsset HWA JOIN RelationshipGenericView HWALoc ---------------------------------------NEW ON HWA.BaseManagedEntityId = HWALoc.SourceObjectId ------------------------NEW AND HWALoc.RelationshipId = '98dad5a4-3880-7a7e-c2ae-b5a89aa964ae' --------NEW
You will need to look up the Id of that particular relationship. I always use PowerShell.PS C:\Users\geoffross> Get-SCSMRelationshipClass Cireson.AssetManagement.HardwareAssetHas | Select-Object Name, Id Name Id ---- -- Cireson.AssetManagement.HardwareAssetHasAssociatedCI eafcd80a-546b-fde6-8fc6-5e247f345821 Cireson.AssetManagement.HardwareAssetHasCatalogItem 9c76eda6-7603-9d1b-673f-e1e06999ebf5 Cireson.AssetManagement.HardwareAssetHasCostCenter 5ce07bee-d4f3-9ed4-2e95-7eb6558c34b5 Cireson.AssetManagement.HardwareAssetHasHardwareAsset 834b3a81-be9d-720c-d5a3-7c7d06e7833b Cireson.AssetManagement.HardwareAssetHasInvoice de3a80fe-ed1c-6117-3ac1-ed1f9ddcc221 Cireson.AssetManagement.HardwareAssetHasLease e6a968f8-a01e-2b02-56db-61744c2211a9 Cireson.AssetManagement.HardwareAssetHasLocation 98dad5a4-3880-7a7e-c2ae-b5a89aa964ae Cireson.AssetManagement.HardwareAssetHasOrganization 29234d2d-1e45-febe-5ef2-45bf9d3c5dad Cireson.AssetManagement.HardwareAssetHasPrimaryUser 28ba5ae2-dfaa-4b7a-ecd6-10cec092c957 Cireson.AssetManagement.HardwareAssetHasPurchase c084b489-a8be-f4bd-f65b-7fc6fd028af1 Cireson.AssetManagement.HardwareAssetHasPurchaseOrder 514b5285-1f7b-398c-7839-f43c2e1a3148 Cireson.AssetManagement.HardwareAssetHasSupportContract e2b50bab-81f4-4041-04b7-8a4615a27bbb Cireson.AssetManagement.HardwareAssetHasVendor 829383eb-3e95-e0ba-0eda-a67d1a4a9f94 Cireson.AssetManagement.HardwareAssetHasWarranty 7feb2584-810c-45f0-93e0-242be10eb52d
Then you can select the TargetObjectDisplayName column from the RelationshipGenericView view to select the DisplayName of the related object. If you want to pull back other properties of the related object, eg Start Date of a warranty, you will need to join again to the table for that item, on TargetObjectId and then you can select any property on that class.
Good luck.5 -
Geoff_Ross
Cireson Consultant O.G.
Hi Stephane,
For that one you need to JOIN to the DisplayString table to turn that Id into the string in the required language.SELECT HWA.Name_8B938849_A621_7754_D33B_BD8E54DC10B4 as [Asset], CASE WHEN HWA.ObjectStatus_4AE3E5FE_BC03_1336_0A45_80BF58DEE57B = 'acdcedb7-100c-8c91-d664-4629a218bd94' THEN 'Deployed' ELSE 'Other' END as [Status], Manufacturer_C022FE4D_F552_A5A7_15BE_2B1BB900E9A6 as [Manufacturer], Model_A2D42F16_E0FC_C126_4460_F3A252B7C9EF as [Model], YEAR(ReceivedDate_F3EA1950_6656_8251_AB6B_89372E4878DE) as [Year], ReceivedDate_F3EA1950_6656_8251_AB6B_89372E4878DE as [Received], TypeDisplay.DisplayName as Type -----------------------------------------------------------NEW FROM MT_Cireson$AssetManagement$HardwareAsset HWA JOIN DisplayStringView TypeDisplay --------------------------------------------------------NEW ON HWA.HardwareAssetType_07E9CFB0_049C_855E_D6CA_135808C8B964 = TypeDisplay.MPElementId ---NEW AND TypeDisplay.LanguageCode = 'BEL' ------------------------------------------------------NEW
A few things to consider...
These examples all use Inner Joins, you may need to consider Outer depending on how you want the data to come back if there is no relationship / enumeration.
Secondly, you are pulling back the ObjectStatus which is the ConfigItem level property that just controls if it is deleted or not. Everything will have that value for ObjectStatus except items that have been deleted. You might want to use HardwareAssetStatus and translate using the technique above.
Finally, you might want to consider pulling you data from the Asset Management Cache tables, either via SQL Query or even by oData. The data in these tables is already flattened so you can pull back all the relationships and enumeration translations without any joins.5
Answers
You will need to join to the RelationshipGenericView view for any of these items. Here's the location addition:
You will need to look up the Id of that particular relationship. I always use PowerShell.
Then you can select the TargetObjectDisplayName column from the RelationshipGenericView view to select the DisplayName of the related object. If you want to pull back other properties of the related object, eg Start Date of a warranty, you will need to join again to the table for that item, on TargetObjectId and then you can select any property on that class.
Good luck.
For that one you need to JOIN to the DisplayString table to turn that Id into the string in the required language.
A few things to consider...
These examples all use Inner Joins, you may need to consider Outer depending on how you want the data to come back if there is no relationship / enumeration.
Secondly, you are pulling back the ObjectStatus which is the ConfigItem level property that just controls if it is deleted or not. Everything will have that value for ObjectStatus except items that have been deleted. You might want to use HardwareAssetStatus and translate using the technique above.
Finally, you might want to consider pulling you data from the Asset Management Cache tables, either via SQL Query or even by oData. The data in these tables is already flattened so you can pull back all the relationships and enumeration translations without any joins.