Need help to get lost hours for specific impacted business service

[Abbas_5]

Hello @JuliusV ,

Please refer to the below steps:

To get the "lost hours" for a specific impacted business service in ServiceNow, you can leverage the "svc_ci_assoc" table, which links together Configuration Items (CIs) and Business Services, allowing you to query related incidents and calculate the downtime based on their resolution time and impacted service information; you can either create a custom report or use a script to automate the calculation. 

Key steps:

• Identify the business service:

  Access the relevant business service record in the "cmdb_ci_service" table. 
• Query the "svc_ci_assoc" table:

  You can use a query to retrieve all CIs associated with the target business service. 
• Filter incidents:

  For each associated CI, query the "incident" table to find all incidents where that CI is listed as impacted. 
• Calculate downtime:

  • For each incident, calculate the duration between the "opened_at" and "closed_at" timestamps. 
  • If necessary, filter out incidents not related to the specific business service using additional conditions.
• Aggregate results:

  Sum up the total downtime across all relevant incidents to get the "lost hours" for the impacted business service. 

How to implement:

• Custom report:

  • Create a new report on the "incident" table. 
  • Add filters to select incidents where the impacted CI is associated with the target business service (using the "svc_ci_assoc" table). 
  • Include fields like "opened_at", "closed_at", and "business service" in the report. 
  • Use report calculations to calculate the time difference between "opened_at" and "closed_at" for each incident. 
  • Group the results by business service to get the total downtime per service. 
• Script:

  • Write a script that loops through all incidents associated with the target business service using the "svc_ci_assoc" table. 
  • For each incident, calculate the downtime and accumulate it in a variable. 
  • Output the final "lost hours" value. 

Important considerations:

• Data quality: Ensure your CMDB is well-maintained with accurate CI-to-business service relationships to get accurate results. 
• Time zone management: Consider time zone conversions when calculating downtime if needed. 
• Custom fields: Depending on your requirements, you might need to create custom fields on the "incident" table to track additional information related to impact analysis. 

If it is helpful, please mark it as helpful and accept the correct solution. In the future, it might be helpful for someone to refer to this solution.

Thanks & Regards,

Abbas Shaik