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

Ben_McGarry

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

Adam_Dzyacky

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

Adam_Dzyacky

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_McGarry

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