How can I report user inputs associated with request from DWH databases?

Haridas_Pratihar

I am trying to pull the associated user inputs against IR's and SR's from DWH server. Can anyone guide me to find out the related tables?

Haridas_Pratihar

Thank you!!! got it.

Haridas_Pratihar

Any suggestion highly appreciated!!!

Adam_Dzyacky

I could be mistaken, but I don't believe the User Input is saved to the Data Warehouse.

I suppose the first question I have is what's the end goal of your report?

Haridas_Pratihar

Sorry I was not clear about my requirement. In request offering, we search for software asset and select the particular software asset as application where we are having the issue or required access or request changes during IR or SR creation process. I am having difficulties to find out that associated software asset in data warehouse database for reporting against IR and SR. I can able to find out the other input fields. Please let me know if you need more information.

Adam_Dzyacky

Ahhhh got it.

So it's a Change, Service, or Incident about an Asset. First thing you'll need to make sure is the Asset Management DW Management Packs are imported. This will make sure your Assets are available for querying in the SCSM DW.

Once that's done, that means you can expect to find a few new tables such as:

• CiresonSoftwareAssetDimvw
• CiresonHardwareAssetDimvw
• CiresonHardwareAssetRelatesToAssociatedCIFactvw
• CiresonHardwareAssetRelatesToLocationFactvw
• CiresonLocationDimvw

And a bunch more that can help you swing around from Work Items -> Assets and vice versa. To get things started...

select ir.title, ir.Description, ci.DisplayName
from incidentdim as ir
    inner join entitydim as e on ir.entitydimkey = e.entitydimkey
    inner join workitemdim as wi on e.EntityDimKey = wi.EntityDimKey
    left join WorkItemAboutConfigItemFactvw as wiaci on wi.WorkItemDimKey = wiaci.WorkItemDimKey
        inner join ConfigItemDim as ci on wiaci.WorkItemAboutConfigItem_ConfigItemDimKey = ci.ConfigItemDimKey

There is a slight distinction worth highlighting above and it's the difference between Work Item Relates to Config Item and Work Item About Config Item. Depending on how you've set the relationship up in the Request Offering or how people are linking it will change which relationship you need to query against.

Haridas_Pratihar

I am stuck at one point. Let me share how it designed. We have software asset and organization (Support Group). When we create application, we map that with particular support group. During Incident or service request creation, when we select the application, there is automated activity run in runbook server and ticket assigned to the related support group.

I track down the activity ->entity -> workitemdim -> WorkItemAboutConfigItemFactvw ->ConfigItemDim

select ac.id, ci.DisplayName
from Activitydim as ac
    inner join entitydim as e on ac.entitydimkey = e.entitydimkey
    inner join workitemdim as wi on e.EntityDimKey = wi.EntityDimKey
    left join WorkItemAboutConfigItemFactvw as wiaci on wi.WorkItemDimKey = wiaci.WorkItemDimKey
    inner join ConfigItemDim as ci on wiaci.WorkItemAboutConfigItem_ConfigItemDimKey = ci.ConfigItemDimKey

I am struggling to find out the related IR's or SR's which tagged with this activity. Do you have any suggestion?

Adam_Dzyacky

Hope you don't mind, just did a quick SQL/Code format above first 😀

So going down this path you've started with an Activity and then gone to all of its Config Items. What you're missing is crawling up the tree from the Activity to the parent Work Item. But you can go about this either way:

• Start at the Activity and work your way up (you are halfway there in your example)
• Start at the Parent Work Item and work your way down (I am halfway there in my example)

The relationship table we are both missing here is WorkItemContainsActivityFactvw. This will allow you to approach this query really in either direction you prefer. But how often you need to query depends on the complexity of where your Activity is in relation to the Parent Work Item.

Haridas_Pratihar

Thank you!!! got it.