Report of IR's by Location

Scott_WrightScott_Wright Customer IT Monkey ✭
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?

Best Answer

Answers

  • Shane_WhiteShane_White Cireson Support Ninja IT Monkey ✭✭✭✭
    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_WrightScott_Wright Customer IT Monkey ✭
    edited May 23
    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?
  • Scott_WrightScott_Wright Customer IT Monkey ✭
    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!
Sign In or Register to comment.