Home General Discussion

Is the "Affected Service" stored anywhere in the CiresonAnalytics database

Ben_McGarryBen_McGarry Customer IT Monkey ✭

I'd like to produce reports/dashboards based on the Affected Service from the CiresonAnalytics database using PowerBI. However I can't find that field in the tables. Has anyone any ideas?

Thanks a mil.

Ben

Best Answer

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited March 2022 Answer ✓

    Incidents with Impacted Affected Services defined on them? I think I can help. Take the following example:


    I have an Incident where I've defined three different Impacted Services on it. Two Business Services that came in from SCOM (Active Directory + Azure Automation) and one I created in SCSM (AdamCo).


    In VSCode, I connect to Analytics and construct the following query to retrieve IR78 and it's three Impacted Services.


    select ir.WorkItemId as 'ID',
        wiAboutCIrel.TargetTypeDisplayName
    from SM_WorkItem_Incident as ir
        inner join SM_WI_Relationship as wiAboutCIrel on ir.Id = wiAboutCIrel.SourceEntityId
    where ir.WorkItemId = 'IR78'
        and wiAboutCIrel.RelationshipTypeId = 'b73a6094-c64c-b0ff-9706-1822df5c2e82'
    


    I got here by first running the following query that let's me see ALL of the relationships, their Target, and then finally the Relationship ID (the guid seen the query above)

    select ir.WorkItemId as 'ID',
        wiAboutCIrel.TEXT_TargetTypeId,
        wiAboutCIrel.TEXT_RelationshipTypeId,
        wiAboutCIrel.RelationshipTypeId
    from SM_WorkItem_Incident as ir
        inner join SM_WI_Relationship as wiAboutCIrel on ir.Id = wiAboutCIrel.SourceEntityId
    where ir.WorkItemId = 'IR78'
    order by wiAboutCIrel.TEXT_RelationshipTypeId
    

Answers

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited March 2022 Answer ✓

    Incidents with Impacted Affected Services defined on them? I think I can help. Take the following example:


    I have an Incident where I've defined three different Impacted Services on it. Two Business Services that came in from SCOM (Active Directory + Azure Automation) and one I created in SCSM (AdamCo).


    In VSCode, I connect to Analytics and construct the following query to retrieve IR78 and it's three Impacted Services.


    select ir.WorkItemId as 'ID',
        wiAboutCIrel.TargetTypeDisplayName
    from SM_WorkItem_Incident as ir
        inner join SM_WI_Relationship as wiAboutCIrel on ir.Id = wiAboutCIrel.SourceEntityId
    where ir.WorkItemId = 'IR78'
        and wiAboutCIrel.RelationshipTypeId = 'b73a6094-c64c-b0ff-9706-1822df5c2e82'
    


    I got here by first running the following query that let's me see ALL of the relationships, their Target, and then finally the Relationship ID (the guid seen the query above)

    select ir.WorkItemId as 'ID',
        wiAboutCIrel.TEXT_TargetTypeId,
        wiAboutCIrel.TEXT_RelationshipTypeId,
        wiAboutCIrel.RelationshipTypeId
    from SM_WorkItem_Incident as ir
        inner join SM_WI_Relationship as wiAboutCIrel on ir.Id = wiAboutCIrel.SourceEntityId
    where ir.WorkItemId = 'IR78'
    order by wiAboutCIrel.TEXT_RelationshipTypeId
    
  • Ben_McGarryBen_McGarry Customer IT Monkey ✭

    Many thanks Adam. That's exactly what I needed and more!

    I'm having a look at PowerBI to create reports and dashboards out of Cireson Analytics (so much easier than the Data Warehouse) but will need the same information when we go live with the Cireson Portal - Analytics dashboards.

    Really appreciate it.

    Ben

Sign In or Register to comment.