I need a query to determine if an actual user looked at a ticket before it was moved to Resolved. I've run into some issues with this b/c sometimes a user doesn't look at it but may make a rule change that affects several tickets after although once the status hits a 4 it will be gone in the future. The only way I thought of this was running the query below for all updates to accounts that aren't resolved '4' and then for those that are '4'. I really want to look at all the incidents before resolved and make sure a user updated it (not sysadmin). Any suggestions? I was thinking an exist
Also, once they are in resolved I wanted a query to determine how many days they moved in and out of resolved (if at any), how many times, and what the reason was (incident_status_id will change) ?? If a user changes a rule sometimes they don't realize the effects on several other rules (where the incident was't resolved for several weeks and now resolved). So this would be a good test for all resolved although I'd want to look at it before resolved and then those rules that keep changing once a ticket is in resolved status.
I work with the business now and am having a hard time getting anything from Tech. I somehow have read-only access to the database but can't even review the schema to see if the joins are correct and its something they haven't shared. I found a query online to find the scehma name but can't find anything in relation to primary keys/foreign keys. It's an Oracle database. Any suggestions??
select incident_id, incident_version_id, incident_status_id, updated_by
from dbo.incident i
where incident_status_id != '4'
abd updated_by != 'sysadmin'
group by incident_id, action_id, updated_by