Report to retrieve all Stories deployed within a specific timeframe along with “Deployed to Prod"

[kartikpatil]

Hi Team,

I need to create a report or query in ServiceNow to retrieve all Stories that were deployed within a specific timeframe.

The report should include:

• Story number

• Timestamp indicating when the story was moved to the Substate: "Deployed to Production"

Essentially, I want to capture the exact date and time when the substate changed to Deployed to Production for each story and filter the results based on a selected date range.

For example, the report should be able to show stories deployed on a particular date such as 12/04/2023, along with the corresponding timestamp of the substate change.

Could someone please guide me on the best way to achieve this (reporting, database view, or querying field history)?

Thank you in advance for your help.

[Mark Manders]

That depends on how you store that data in your system. Looking at the OOB rm_story table, there is no 'substate' field, so I think it's a custom field? It could be you have some plugins I don't, but on the Story table that came with SPM, there isn't a substate field.

But no matter it being OOB or not, you still need to check if that date is stored anywhere. If you have the timestamp on the table as well, it's easy (so you probably don't have it there). You other options are metrics or Performance Analytics. OOB there is a Metric Definition called "EAP: Story state duration". This gives the exact timestamp when a state changes. Still assuming your field is custom, you could create a similar Metric Definition for substate and report from there.

You can also check on your PA jobs and see if you are already collecting the data from which you can get to the substate and just report on that, or create a job yourself, collecting the stories with that substate and report from there.

You can't report on something that isn't there, so your search starts there: what do you already have from where you can get that date stamp.

Both with PA jobs and with Metrics, you will only get the data from the moment you create them, not the past. Historical 'on change' data can't be collected, simply because it's not stored anywhere.