Video Screencast Help

List of EM and Survery Tickets

Created: 22 Aug 2013 • Updated: 30 Aug 2013 | 8 comments
snm1502's picture
This issue has been solved. See solution.

Hello,

We have been requested to provide a list of all EM (EM-) and Survey tickets (Survey-) where the associated IM ticket is not closed yet.

I am successful to get the list of EM and Survey tickets that are not closed however, now seeking advise / guidance / help / reference to link / join so that associated IM tickets and their status is listed.

Below is the query for reference:

select rp.reportprocessid, rp.result,rp.projectname from ReportProcess rp
where (rp.projectname like '%email%' and rp.result <> 'closed') or
(rp.projectname like '%survey%' and rp.Result like 'in process')

order by rp.ReportProcessID

Anything on this would be highly helpful.

Thanks
snm1502

Operating Systems:

Comments 8 CommentsJump to latest comment

michael.george's picture

Off the top of my head, I think you'd want to join to the ReportProcessRelationship table as that's where the connection between two processes is stored.

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

snm1502's picture

Thanks for the reply Michael,

However, I tried to join with ReportProcessRelationship and I do get some results for Survey tickets, but 0 results for EM. Probably I am missing something in Joins.

It would be great, if you would be able to please help me with the column I may use.

Thanks
snm1502

michael.george's picture

I'm guessing you've got the right query, actually. I think the problem is in your logic. But let's step through to make sure that I'm not missing anything. Also, for what it's worth, I'm running 7.5 here and my old 7.1 DB is no longer attached, so if you are using 7.1, this may not be exactly the same and may not work.

So, here's what I quickly put together based on your original. As with most things, there are probably many ways to write the query.

 

select rp.reportprocessid as ChildProcID
     ,rp.result as ChildResult
     ,rp.projectname as ChildProject
     ,rp2.ReportProcessID as ParentProcID
     ,rp2.Result as ParentResult
from ReportProcess rp
  join ReportProcessRelationship rpr on rp.SessionID = rpr.ChildProcessID
  join ReportProcess rp2 on rpr.ParentProcessID = rp2.SessionID
where (rp.projectname like '%email%' and rp.result <> 'closed') or
  (rp.projectname like '%survey%' and rp.result not like 'in process')
order by rp.ReportProcessID
 
On my system, the out of the box survey doesn't seem to ever hit 'in process' as a status, so mine either end up in 'survey sent' or 'survey completed'. So in my case, I have to change that second to last line to be 'not like "survey completed"'.
 
But you say you got survey results, so we'll not worry about that too much. Focusing on the email, well, I'm not sure what's up there. In my case, whenever someone has to classify an incoming email ticket, that email process has ended with a 'result' column of 'closed'. I went into the table and manually changed one back to a 'result' of 'open' and run the above query and it then showed up on my list along with the associated IM ticket.

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

SOLUTION
snm1502's picture

Hello Michael,

Thanks again for the reply.

If I understand the logic from your query correct, it will first look for the session ids from relationship table and then the join would check for those sessions to be present in Report Process table for IM #s. It looks like this is what I wanted.

However, the query still returned 0 results. So I made a quick change to the where clause in query as rp.result like 'in process' as I just wanted the list of records that are in process. Still got 0 results.

Then making a quick change in original query (new query is pasted below), I verified if the records for open EM / Survey tickets are even present in ReportProcessRelationship table where sessionid = childprocessid or parentprocessid and got 0 results.

select * from ReportProcessRelationship

where ChildProcessID in --ran same query with ParentProcessID in
(select rp.SessionID from ReportProcess rp
where (rp.projectname like '%email%' and rp.result <> 'closed') or
(rp.projectname like '%survey%' and rp.Result like 'in process'))

This confirmed that the open EM / Survey tickets do not have any other tickets related. Hope my understanding is correct.

My question is, is there any other table in the database that might give us the list of IM tickets associated to open EM / Survey tickets. I went through another table related to IM (ServiceDeskIncidentManagement) but did not find any luck.

Any advice / guidance / reference on this would be great.

Thanks again
snm1502

michael.george's picture

What version of SD are you using this on?

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

snm1502's picture

Hello Michael,

I am using it on SD 7.1..  Have got a query for this from support. It looks like the EM tickets does not have IM tickets related to them, because from this query as well I get the list of IM #s for Survey tickets however, not for EM.

Below is the query for your reference:

select rp.ReportProcessID, rp.Result, rp.ProjectName, dim.process_id [Incident], rp2.Result
from ReportProcess rp with (nolock)
left outer join ReportProcessRelationship rel with (nolock)
on rp.SessionID = rel.ParentProcessID
left outer join ServiceDeskIncidentManagement dim with (nolock)
on (dim.session_id = rel.ChildProcessID)
left outer join ReportProcess rp2 with (nolock)
on rp2.SessionID = dim.session_id
where rp.ProjectName = 'SD.Email.InboundManagement' and rp.Result != 'Closed'
union
select rp.ReportProcessID, rp.Result, rp.ProjectName, dim.process_id [Incident], rp2.Result
from ReportProcess rp with (nolock)
left outer join ReportProcessRelationship rel with (nolock)
on (rp.ReportProcessID = rel.ParentProcessID)
left outer join ServiceDeskIncidentManagement dim with (nolock)
on (dim.process_id = rel.ChildProcessID)
left outer join ReportProcess rp2 with (nolock)
on rp2.SessionID = dim.session_id
where rp.ProjectName = 'SD.CustomerServiceSurvey'
and rel.Name is not null and rp.Result = 'In Process'

The results from this looks good.

Thanks
snm1502

SOLUTION
michael.george's picture

I attached my old 7.1 DB and I think that email classification tickets definitely are related to incidents they create. In the process view page, anything that shows up in the 'related processes' view part coresponds to an entry in the relationship table. In order for the above query to work, by design, the email process would have to still be open. That shouldn't happen unless there is an error in the process. When a email is classified as junk, no IM is created and the email process ends. When It's marked to become a ticket, an IM is created and the email process ends. So either way, that email process shouldn't still be open when an IM is related to it.

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

snm1502's picture

Thanks for the explanation Michael.

However, it looks like it is behaving a little different here, there is a huge list of tickets in status "Classify Email Message" and doesnt have an IM related.

Does it mean that these email tickets are not processed and must have broken for some reason?

Email tickets that are processed and completed they are marked as "Closed", so this ensures that the list of EMs I am getting are still open.

Thanks
snm1502