CA Service Management

Dear,

Good Morning John.

Please fill us in on the details of this question? To give us a start to get looking for you on this.

E.G.
Which product are you having this question for? And which release of that product?
What functionality are you referring to?
For which database do you then need the query to be executed?

Your help will be appreciated.

Thanks in advance for your understanding and kind regards, Louis.

Why is this answer marked as correct answer? This is not even an answer

Dear, 

Hello,

Adding to Louis's question, could you also describe what exactly you are trying to do with the SQL Query?  Is this for a report that is being developed?  What sort of data are you attempting to extract?

Can you provide a screencap of the Service Management interface with the content you are interested in?

John,

While I do not know if we can give you a specific Oracle query, I can tell you that your screencap is an activity log entry.

From looking at SQL Server, the basic query I could suggest trying, which may translate well into Oracle:

The query I'd reccomend:

Select act_log.description from act_log where act_log.type = 'RE' and act_log.call_req_id IN (Select call_req.persid from call_req where call_req.ref_num = 'XXXX')

Tables

act_log is the table that contains the content you are looking for.

call_req is the table that stores requests, incidents, and problems

Fields
act_log.description contains the answer text you are asking about
act_log.type is a "Resolved" status, which is an attribute that is associated with such entries that name the answer text
act_log.call_req_id and call_req.persid is the internal Persid value for call requests (incident tickets)
call_req.ref_num is the case number whose answer you are looking for.

 I need a query with the answer of the case

Hello John,

I think this is the query you need, please never forget to use with (nolock) when you are running queries directly in MS SQL DB:

select REF_NUM No_Caso, sla_violation violacion_sla, CR_STAT.SYM estado,
status, open_date fecha_apertura, time_spent_sum, close_date, resolve_date fecha_resolucion,
asignado.email_address as asginado, grupo.last_name as grupo, prob_ctg.SYM categoria, priority,
open_date, close_date, priority, type tipo, summary resumen, CALL_REQ.description descripcion,
(select top 1 description from act_log with(nolock) where call_req.persid = act_log.call_req_id and type = 'SOLN' order by time_spent desc) Solucion
from call_req with(nolock)
join ca_contact asignado with(nolock) on asignado.CONTACT_UUID = call_req.assignee
join ca_contact grupo with(nolock) on grupo.CONTACT_UUID = call_req.group_id
join prob_ctg with(nolock) on PROB_CTG.persid = call_req.category
join cr_stat with(nolock) on Call_req.status = CR_STAT.code
where grupo.Last_name LIKE '%seguridad%Informatica';

Kind Regards,

Yonatan Sosa

I need Query in oracle