CA Service Management

Hello !

I have a few reports on CABI 14.1 and i require the last activity log of the tickets in a colum 

I used the Action description of Request activity folder but it berings me all the changes of each ticket and i one need the last one

Do you know how displays that field or what is the query that i must use ?

Regards

Christian,

You could try to look for all Action Descriptions (of a given ticket) with the latest last_mod_dt  or highest id   (higest act_log.id  or highest alg.id )

_R

But I need to do it in CABI not in the SQL studio , by the moment displays all the action logs ,  i tried with a formula but dont  show me the last description of active log 

1) You'd definitely need a join between Request Activity class ->  Call Request ID    and    Request Details -> ID   

2) Then you'd need the a CABI function like maximum(Request Activity class -> ID)  See this:  (https://communities.ca.com/message/116975088?commentID=116975088#comment-116975088 ) 

_R

The join between Request Activity class -> Call Request id and Request Details id need to do in SQL ? then i need to use the CABI function maximum?

No, it can be done in CABI report itself..    Something like this :  How to avoid getting a cartesian product of two table records in a CA Service Desk Manager CA Business Intelligence repo… 

_R

Hi Raghu !

But i dont know what is  the sintax of the condition of maximum or higest   alg.iaction_desc for cr.id

Can you help me with that?

Thanks for your help

Now i have this Query 

SELECT
cr.type_symbol,
cr.tenant_name,
cr.ref_num,
cr.request_area_symbol,
cr.assignee_combo_name,
cr.open_date,
cr.summary,
cr.status_symbol,
cr.last_mod_dt,
alg.action_desc
FROM
cr,
alg
WHERE
(
cr.active = 1
AND
cr.id = alg.id

)

Regards

Sorry slapping on something really basic here if that helps - 

select cr.ref_num, cr.type_symbol, alg.action_desc

from cr, alg

where alg.id=(select max(alg1.id) from alg alg1 where alg1.call_req_id = cr.persistent_id)

and alg.call_req_id = cr.persistent_id

_R

Thaks for the help  Raghu , 

Sorry for the lack of knowledge

Hi Christian,

If Raghu's previous post has helped, can you please just go ahead and mark that as the answer on this one?

Thanks,

Jon I.

oh no problem at all.   I'm not sure about the performance of the query with huge amount of data though. You may need to tweak it a bit per need

_R