Report of IR's by Location

Scott_Wright

We use the Asset Management Location attribute on all of our work items.  I'd like to create a Power BI report to list Work Items by Location.  How do I create a query to link the relationship between WI and Location?

Shane_White

Hi @Scott_Wright

How is this one:

SELECT ir.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'Id', aml.DisplayName as 'LocationName',

ir.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as 'Title',

lt.LTValue as 'Classification',

lt2.LTValue as 'SupportGroup',

lt3.LTValue as 'Status',

FORMAT(ir.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688, 'dd/MM/yyyy') as 'CreatedDate'

  FROM [ServiceManager].[dbo].[MT_Cireson$AssetManagement$Location] as aml

  JOIN dbo.Relationship as r

  ON  r.TargetEntityId = aml.BaseManagedEntityId and r.RelationshipTypeId='D96C8B59-8554-6E77-0AA7-F51448868B43' 

  JOIN dbo.BaseManagedEntity as bme

  ON r.SourceEntityId = bme.BaseManagedEntityId

  JOIN dbo.MT_System$WorkItem$Incident as ir

  ON bme.DisplayName = ir.DisplayName

  JOIN Dbo.LocalizedText as LT

  ON ir.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = LT.MPElementId

  AND Lt.LanguageCode='ENU'

  AND LT.LTStringType=1

  JOIN Dbo.LocalizedText as LT2

  ON ir.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = LT2.MPElementId

  AND LT2.LanguageCode='ENU'

  AND LT2.LTStringType=1

  JOIN Dbo.LocalizedText as LT3

  ON ir.Status_785407A9_729D_3A74_A383_575DB0CD50ED = LT3.MPElementId

  AND LT3.LanguageCode='ENU'

  AND LT3.LTStringType=1

  Where (ir.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C like 'IR%')

  

You could then do the same thing with SR/PR/CR and do a union to join all the tables but that would be quite long for me to post here haha! Just make sure the AS 'ColumnNames' all match up for the union to work.

Hope this helps

Shane

Shane_White

Hi @Scott_Wright

Run this against your Service Manager Database and let me know if this is kind of along the lines on what you wanted?

SELECT aml.DisplayName as 'LocationName', bme.DisplayName as 'TicketName',

 aml.Name_95003569_4B1C_C946_F24F_71EF9033F9A8 as 'Location',

  aml.LocationState_BD74724A_69C1_B971_4FDE_19AE2C068F1C as 'LocationState',

   aml.LocationCountry_3D103163_F8A1_4009_75E0_E9B397EA7C24 As 'LocationCountry'

  FROM [ServiceManager].[dbo].[MT_Cireson$AssetManagement$Location] as aml

  JOIN dbo.Relationship as r

  ON  r.TargetEntityId = aml.BaseManagedEntityId

  JOIN dbo.BaseManagedEntity as bme

  ON r.SourceEntityId = bme.BaseManagedEntityId

  Where (bme.DisplayName like 'IR%'

   OR bme.DisplayName like 'SR%'

    OR bme.DisplayName like 'CR%'

OR bme.DisplayName like 'PR%')

Thanks,
Shane.

Scott_Wright

Hi Shane,

First, thank you for taking the time to share your solution.  It's close, but I need to isolate the WI ID, so I can look up details from the SCSM DB (unless thay are also contained in the ServiceManager DB).  I need to list:
WI, Location Name, Title, classification, support group, status, created date

Example:

"IR1234" "Store124" "Application Not Working" "Software Error" "Software Support Team" "Active" "05/23/19" 

Do you think this is possible?

Shane_White

Hi @Scott_Wright

How is this one:

SELECT ir.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'Id', aml.DisplayName as 'LocationName',

ir.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as 'Title',

lt.LTValue as 'Classification',

lt2.LTValue as 'SupportGroup',

lt3.LTValue as 'Status',

FORMAT(ir.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688, 'dd/MM/yyyy') as 'CreatedDate'

  FROM [ServiceManager].[dbo].[MT_Cireson$AssetManagement$Location] as aml

  JOIN dbo.Relationship as r

  ON  r.TargetEntityId = aml.BaseManagedEntityId and r.RelationshipTypeId='D96C8B59-8554-6E77-0AA7-F51448868B43' 

  JOIN dbo.BaseManagedEntity as bme

  ON r.SourceEntityId = bme.BaseManagedEntityId

  JOIN dbo.MT_System$WorkItem$Incident as ir

  ON bme.DisplayName = ir.DisplayName

  JOIN Dbo.LocalizedText as LT

  ON ir.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = LT.MPElementId

  AND Lt.LanguageCode='ENU'

  AND LT.LTStringType=1

  JOIN Dbo.LocalizedText as LT2

  ON ir.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = LT2.MPElementId

  AND LT2.LanguageCode='ENU'

  AND LT2.LTStringType=1

  JOIN Dbo.LocalizedText as LT3

  ON ir.Status_785407A9_729D_3A74_A383_575DB0CD50ED = LT3.MPElementId

  AND LT3.LanguageCode='ENU'

  AND LT3.LTStringType=1

  Where (ir.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C like 'IR%')

  

You could then do the same thing with SR/PR/CR and do a union to join all the tables but that would be quite long for me to post here haha! Just make sure the AS 'ColumnNames' all match up for the union to work.

Hope this helps

Shane

Scott_Wright

This is great!  For some reason, it's only finding 8 records, but it's enough to get me started.  Thank you for all your help!