babu_bayyana Absent Member.
Absent Member.
9213 views

How can I run a report showing all the items that are in a specific state for more than 5 days


I would like to know how create a report to show all active items that are in a specific state (Waiting For Mgmt Approval) for more than 5 days. Our primary table name is IT_RISK. We are in SBM R2 and Oracle 10g.
0 Likes
7 Replies
Highlighted
edward_whitbeck Absent Member.
Absent Member.

Re: How can I run a report showing all the items that are in a specific state for more than 5 days


Is the state you are looking to report on the state that the ticket resides in?

You could make a listing report, and set the conditions as follows:

state = Waiting for Mgmt Approval

last modified date > (5 days ago)
0 Likes
Contributor.. KDI_Rick Contributor..
Contributor..

Re: How can I run a report showing all the items that are in a specific state for more than 5 days


just imo you may want to go with the above but modify it slightly...

state = waiting for approval

AND last state change > 5 days.

Using the modified date might be tough if your tickets get updated while remaining in the 'awaiting approval' state.

We recently built a 5 day escalation to mgmt for tickets 'awaiting approval' for 5 days, and the escalation is terminated if the ticket moves forward to the next state within 5 days. So the term notification needed a state change rule -vs- a modified date rule.
0 Likes
babu_bayyana Absent Member.
Absent Member.

Re: How can I run a report showing all the items that are in a specific state for more than 5 days


Thanks for your updates, I would like to know how to setup a notification that will send an e-mail to the user when the item sit in that state for more than five days. Could you please share that implementation steps.

Thanks in advance for your great cooperation.
0 Likes
edward_whitbeck Absent Member.
Absent Member.

Re: How can I run a report showing all the items that are in a specific state for more than 5 days


-Create a rule for the action, IE "state changes to waiting approval" and owner = current user (or which ever group you want the notification to go to)

-Create a notification, "ticket in waiting approval for 5 days" that sends to all users-Create an escalation that is triggered when rule: state changes to waiting approval, and set it to fire after 5 days. upon escalation, send notification.

Hope this helps!
0 Likes
mobermaier Absent Member.
Absent Member.

Re: How can I run a report showing all the items that are in a specific state for more than 5 days


or create a listing report with advanced SQL condition:

(our primary table is called USR_SOFTWARE_DEVELOPMENT, replace with IT_RISK)

@WHERE USR_SOFTWARE_DEVELOPMENT.TS_ID in -- IMPORTANT: Needs table name prefix here

(select scr.ts_id -- internal ID / Primary Key

from usr_software_development scr, ts_states st

where scr.ts_laststatechangedate) -- and State is
0 Likes
mobermaier Absent Member.
Absent Member.

Re: How can I run a report showing all the items that are in a specific state for more than 5 days


in that style you could then query the change history ts_changes, ts_changeactions to get around notifications.
0 Likes
jeff_malin Absent Member.
Absent Member.

Re: How can I run a report showing all the items that are in a specific state for more than 5 days


One note to remember if you're writing queries against the various Change tables is that while Date/Time fields in User tables changed to native Date/Time format with the migration from TeamTrack-->SBM, System tables like TS_CHANGES and TS_CHANGEACTIONS still use UTC Seconds date format.

So if you're doing a SQL query against a primary table and want to filter by a value of '5 days ago' you would write it as "where ts_laststatechangedate >= dateadd(d, -5, getdate())", however to write the same kind of query against ts_changes, you would say "where ts_time >= datediff(s, '1/1/1970', getdate()) - 86400*5" (where 5 is the # of days to look back and 86400 is the number of seconds in a day)
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.