Clarity Service Management

Expand all | Collapse all

How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

  • 1.  How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-20-2016 02:45 AM

    Hi Folks,

     

    I have a requirement to show the timestamp of Group escalation and  assignee assignment in boxi report.

     

    The requests in service desk gets escalated from Group A to  Group B/Group C/Group D etc. and I need the timestamp for that group escalation. When group is escalated, Assignee is assigned after few hours or few days. So I need to fetch the timestamp of Assignee assignment as well

    So In a nutshell, I need to show timestamp of Group escalation as well as Assignee Assignment.

     

    Someone please help me how can I do this.

     

    Thanks in advance

    Manirul



  • 2.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-20-2016 03:20 AM

    I believe this could be done through Activity Log but I  am not getting idea how to achieve that. If someone has some code please help me.

     

    My report columns are  something like this:

     

    Ref Num| Group|Category|Open Date|Group Escalation Date| Assignee Assignment Date| Close Date| Duration(Close Date-Assignee Assignment Date)

     

     

    Thanks

    Manirul



  • 3.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-20-2016 06:24 AM

    Someone please help.

     

    Thanks in advance

    Manirul



  • 4.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-20-2016 07:14 AM

    We are using SDM 14.1, Oracle Database 11g, CA BI 4.1 Sp3

     

    Thanks

    Manirul



  • 5.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-20-2016 10:00 AM

    Can anyone please help me on this report?

     

    Thanks

    Manirul



  • 6.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-20-2016 10:34 AM

    Manirul,

     

    Checkout these two threads,  you may have to follow a similar approach for your report (Request Activity class)

     

    time_stamp

     

    Activity Log

     

     

    _R



  • 7.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-21-2016 04:38 AM

    Hi Raghu.Rudraraju

     

    The links you provided are really helpful.

    So I tried using the following custom query script in Query panel

     

    SELECT

    call_req.ref_num,act_log.action_desc,act_log.time_stamp

    FROM

    call_req,act_log

    WHERE

    act_log.call_req_id=call_req.persid

    AND act_log.description LIKE ('%Transfer Group from "Group A" to "Group B"%')

     

    But when I am validating the script, I am getting the following error:

     

    Database Error:[DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]AHD12005:Objet call_req in Majic  SQL query is unknown to dmsrvr at call_req, (IES 10901)

    abcd.jpg

     

     

    Please let me know if I am doing any mistake. We are using SDM 14.1, Oracle 11g and CA BI 4.1 sp3



  • 8.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-21-2016 08:53 AM

    Hi Raghu.Rudraraju

    One more thing client just clarified today:-

     

    Group Assignment could be multiple times like Group A->Group B  and Group B->Group C or  Group C-> Group D or Group D-> Group C anything  that is possible.Same is applicable for assignee as well. Assignee could be assigned multiple times. So they need to show the timestamp for latest group transfer  and latest assignee assignment.

     

    Is there any way we could do this?

     

     

    Thanks

    Manirul



  • 9.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-21-2016 08:56 AM

    The query in reference below is not a query that you can use in WEBI/Crystal reports against SDM Universe.  Because that's using object layer.

     

    SELECT

    call_req.ref_num,act_log.action_desc,act_log.time_stamp

     

     

    call_req e is a real table name in MDB

     

    cr a is a table name that report can use  (if you are using the CA SDM universe or a derived universe based on that)

     

     

    Checkout the  Request Activity  class in the universe,  you are looking for  Type Symbol = Transfer    and it should show all act_log entries which have a transfer activity.  Unless you have any extra filters, it should show all Transfers on a given ticket.

     

     

    _R



  • 10.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-21-2016 10:15 AM

    Hi Raghu.Rudraraju

    Thanks.

    So what I am planning right now is to fetch data from KPI as they want  timestamp  for more particularly three field  changes(Status,Group and Assignee) and I believe KPI ticket data covers these fields.

     

    So I tried to get the values using the following fields in query panel and the result was not satisfactory.

     

    Only I could see the group changes and the whole report was full of  group changes  and that is also for only one group change, I did not see anything related to assignee changes or anything else.

     

    I used the following fields in the report query panel:-

     

    Ref Num| Field Name|Field Value|Next Value|Previous Time|End Time|KPI Ticket Data Duration

     

    But I dont get the change details of three fields in any of the report data.

     

    I was expecting something like this:

     

    Report Output:-

    Ref Num| Field Name|Field Value|Next Value|Previous Time|End Time|KPI Ticket Data Duration

    100|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

    100|Assignee|Assignee A|Assignee B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

    101|Group| Group C|Group D|20/04/2016 hh:mm:ss|20/04/2016 hh:mm:ss|xxxx

    101|Assignee| Assignee P|Assignee Q|18/04/2016 hh:mm:ss|19/04/2016 hh:mm:ss|xxxx

    102|Group| Group E|Group F|20/04/2016 hh:mm:ss|20/04/2016 hh:mm:ss|xxxx

    102|Status| Open|Acknowledge|21/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

    103|Group| Group G|Group H|16/04/2016 hh:mm:ss|17/04/2016 hh:mm:ss|xxxx

    103|Assignee| Assignee M|Assignee N|18/04/2016 hh:mm:ss|19/04/2016 hh:mm:ss|xxxx

    103|Status| Open|In Progress|18/04/2016 hh:mm:ss|19/04/2016 hh:mm:ss|xxxx

     

    How can I achieve this?

     

     

    All I am getting in report right now is:-

     

    100|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

    101|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

    102|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

    103|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

    104|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

    105|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

    106|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

     

     

    Someone please help. I badly require this kind of report.

     

    Thanks

    Manirul



  • 11.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-21-2016 01:50 PM

    Hi All,

     

    Any help for the same? someone please help.

     

     

    Thanks

    Manirul



  • 12.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-21-2016 06:26 PM

    Hi Manirul_Hak,

     

    I'm using MS SQL, but I think this can be usefull, please check the output for this querys on your environment:

     

    • select * from audit_log where attr_name like 'assignee'
    • select * from audit_log where attr_name like 'group'

     

    I recomend you to use a custom universe pointing to some SQL view (in my case) this way you can easily get the data or try to make the joins at universe level with this object.

     

    Regards,



  • 13.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-25-2016 03:19 AM

    Thanks asalas Raghu.Rudraraju for valuable replies.

     

    Now I could see the following output:-

     

    Ref Num| Field Name|Field Value|Next Value|Previous Time|End Time|KPI Ticket Data Duration

     

    100|Status | Acknowledged | In Progress | 21/04/2016 8:05:00 pm| 22/04/2016 11:21:00 am |xxxx

    100|Status | Open| Acknowledged | 21/04/2016 07:21:00 pm | 21/04/2016 8:05:00 pm |xxxx

    100|Group | Group B | Group C | 19/04/2016 9:44:45 am | 21/04/2016 05:34:16 pm |xxxx

    100|Group | Group A |Group B | 18/04/2016 8:55:04 am | 19/04/2016 9:44:45 am |xxxx

     

    But I want to show like this(The latest changes in Status,Group or any other KPI fields):-

     

    100 |Status | Acknowledged | In Progress | 21/04/2016 8:05:00 pm| 22/04/2016 11:21:00 am |xxxx

    100 |Group | Group A |Group B | 18/04/2016 8:55:04 am | 19/04/2016 9:44:45 am |xxxx

     

    How can I show the latest changes on status,group,assignee or any other kpi fields? Please help.

     

    Thanks

    Manirul



  • 14.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-25-2016 08:11 AM

    Hi Raghu.Rudraraju asalas

     

    Any help for the same?

    I want to show only the latest changes of the three particular fields (status,assignee and group) not all the changes in these fields. How can I do that?

     

     

    Thanks

    Manirul



  • 15.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-25-2016 03:52 PM

    Anybody please share ideas how this can be done.

     

    Thanks

    Manirul



  • 16.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-26-2016 03:18 AM

    Any help for the same?

     

    Thanks

    Manirul



  • 17.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-28-2016 04:27 PM

    Hi,

    Try this way:

    Sem título1.png

    The result is:

    Sem título2.png

    I think that's what you want.



  • 18.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-30-2016 03:58 AM

    Hi Lrralves,

    Thanks for the reply. Actually we need to fetch the latest timestamp for these three changes(status,group,assignee).

    We are fetching this from ktd table using Max(end_time)  and ktd.field_name  IN  ( 'status') and same is for  'group' and 'assignee'.

    So we have created 3 derived tables for getting latest timestamp on 'status', 'group','assignee' changes(The scenario is ,these three fields can be changed multiple times and we need to show latest timestamp for that change) and we are getting correct result.

     

    But the only problem is  the report is showing those records only where all the three fields('assignee','group','status') been modified but its not showing the records where only 'status' is changed or 'group' is changed or 'assignee' is changed or any combination of these three fields change.

     

    We need to show all the records where any of these three fields is modified. So there is a need to join the derived tables as well so that we can show all the records where  any of the one, two or three fields been modified.

     

    Please help to join these derived tables so that we can achieve the scenario.

     

    Raghu.Rudraraju please look into this scenario.

     

    Thanks

    Manirul



  • 19.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 04-30-2016 10:20 AM

    Hi,

     

    Did you try making a SQL query and later using a new universe pointing to these results (use a SQL view)?

     

    On my experience this would be easier than try to get this data at default universe level,

     

    Regards,



  • 20.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 05-02-2016 03:57 AM

    Hi asalas,

     

    We have created these derived tables in custom universe not in default universe.

     

    The report is also showing the correct result but the  only problem is  the report is showing those records only where all the three fields('assignee','group','status') been modified but its not showing the records where only 'status' is only changed or 'group' is only changed or 'assignee' is only changed or any combination of these three fields change.

     

    We we need to  write the custom sql in report level in such a way that it  should show all the records  where only one field is changed (status/group/assignee), any of the two fields are changed (status,group/group,assignee/assignee,status) ,all the three fields are modified (status,group,assignee)

     

     

    How could we achieve that?

     

    Thanks

    Manirul



  • 21.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 05-02-2016 02:43 PM

    Hi All,

     

    Any help for the same?

     

    Thanks

    Manirul



  • 22.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 05-02-2016 09:51 PM

    Maybe the joins have to be left outer join or something similar to see the type of data you are expecting to see. 

     

    Unfortunately, I'm a bit lost on this whole requirement. Maybe others have some ideas to offer here.

     

    _R

     

     

     



  • 23.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 05-03-2016 01:00 AM

    Hi Raghu.Rudraraju,

    I can explain the scenario in brief.

    I need to show all the tickets where status,group or assignee gets changed or any combination of these three fields get changed and latest timestamp of those change(These three fields can be modified multiple times so need to pick the latest timestamp).

    Ticket number|Status Change Date| Assignee Change Date|Group Change Date

    I have created three derived table which is picking the latest timestamp for these change. Whenever I am using these in query panel I can see only those records where all the three fields get changed.but I need to show all other  records too where one or two fields got modified.

     

    How can we achieve that. If left outer join can help, how can I do this on derive table or do I have to join inside those derive table?

    If you please give some example ,it would be really helpful.

     

    Thanks

    Manirul



  • 24.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 05-06-2016 02:31 AM

    Hi Raghu.Rudraraju,

    Hope the requirement is understood.Please let me know one thing , is it possible to join these three derived table in universe level or in report level as we could only join two  tables(group and assignee). We excluded the status related derived table.Please suggest any other easier way to join these three tables.

     

    Meanwhile, Please share your ideas whether this can be achievable in any other way except  using derived table or any other easier way.

     

     

    Thanks

    Manirul



  • 25.  Re: How to show timestamp in CA Boxi when Group is escalated  and Assignee is assigned in SDM request ?

    Posted 05-06-2016 03:34 PM

    Manirul_Hak,

     

    Have you tried to make a view in SQL database and create a universe from it?

    I think it might be easier to manipulate the data in SQL rather than doing it in BOXI.