Home General Discussion

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

Haridas_PratiharHaridas_Pratihar Customer IT Monkey ✭

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?

Best Answer


  • Options
    Haridas_PratiharHaridas_Pratihar Customer IT Monkey ✭

    Any suggestion highly appreciated!!!

  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    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?

  • Options
    Haridas_PratiharHaridas_Pratihar Customer IT Monkey ✭

    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.

  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited March 2021

    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.

  • Options
    Haridas_PratiharHaridas_Pratihar Customer IT Monkey ✭
    edited March 2021

    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?

  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    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.

  • Options
    Haridas_PratiharHaridas_Pratihar Customer IT Monkey ✭
    Answer ✓

    Thank you!!! got it.

Sign In or Register to comment.