CA Service Management

 View Only
Expand all | Collapse all

Xtraction query to show VIP Users (Special Handle)

Pier Olivier Tremblay

Pier Olivier TremblayMar 02, 2018 11:21 AM

  • 1.  Xtraction query to show VIP Users (Special Handle)

    Posted Feb 26, 2018 10:26 AM

    Hi there,

     

    I would like if somebody can give me a hand with this scenario.

     

    I will like to show in a Xtraction Dashboard, VIP users from CA ServiceDesk that they have an open Incident or Request. (As far as I know, this is a "Special Handle" condition that is added to the user in CA Service Desk).

     

    I requested a chat with Support and they provided me the following query

     

    SELECT contact_handling.contact_combo_name, cnt_handling.delete_flag_symbol FROM contact_handling INNER JOIN cnt cnt_handling ON (cnt_handling.id=contact_handling.contact) WHERE contact_handling.special_handling = 400001

     

    What I would like to know if how to edit the data model to add this info.

     

    Support Case reply to ask on the community...

     

    thanks in advance



  • 2.  Re: Xtraction query to show VIP Users (Special Handle)

    Broadcom Employee
    Posted Feb 28, 2018 12:23 PM

    Hi Esteban,

     

    Please reference the link below:

    Xtraction query to show VIP Users (Special Handle) 

     

    However on step 4B, instead of adding a new field you need to add a join between the contact table and the usp_contact_handling table.

     

    Thanks,

    Scott



  • 3.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Feb 28, 2018 02:01 PM

    Hi @johsc03 there, thanks for replying but the link takes me back to the top of this topic instead to another doc.



  • 4.  Re: Xtraction query to show VIP Users (Special Handle)

    Broadcom Employee
    Posted Feb 28, 2018 02:38 PM

    My apologies Esteban, the link below should be correct:

     

    CA Xtraction::. Edit Data Model 



  • 5.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 02, 2018 09:53 AM

    Great!

     But the thing is that, following the document (altough changing the step you remarked above). I do not have anywhere a "Contact" table, the closest thing is an "Affected End User" which does not have a usp_contact_handling table anywhere close. This happening in both Incidents and Requests.



  • 6.  Re: Xtraction query to show VIP Users (Special Handle)

    Broadcom Employee
    Posted Mar 02, 2018 10:01 AM

    My apologies once again Esteban, the table is: "ca_contact"

     

    The contact column from the usp_contact_handling will match the contact_uuid column from the ca_contact table.



  • 7.  Re: Xtraction query to show VIP Users (Special Handle)
    Best Answer

    Posted Mar 02, 2018 10:32 AM

    Earamendi, what you need to understand here is that the table name in Xtraction are nowhere to match the one in ServiceDesk's database.

     

    johsc03 is talking about SDM table and you about Xtraction table. You guys are not even close to understand each other.

     

     

    What you need to do is decide wheter you want to the affected end user's special handlings, the customer's special handlings or both.

     

    Xtraction works exactly like a SQL Query. You need to understand SQL first if you want to map fields easily in Xtraction.

     

     

    The logic is as below : (SDM database table or column name will be in paranthesis)

     

    Table

     

    REQUEST (call_Req) is linked to table AFFECTED_END_USER (ca_contact) using the contact_uuid. You should be able to find this join under the REQUEST(call_req) table details, tab Joins.

    You need to link table AFFECTED_END_USER(ca_contact) to usp_contact_handling using the contact_uuid.

    Create a new table in Xtraction, name it AFF_END_USER_HANDLING_REL (usp_contact_handling).

    Add the contact and special_handling column.

    Create a join on the table AFFECTED_END_USER(ca_contact) and left join the Affected end user PK (contact_uuid) to the contact column in the AFF_END_USER_HANDLING_REL(usp_contact_handling) you just created.

    You now need to map the relation table to the usp_special_handling table.

    Create a new table in Xtraction, name it AFF_END_USER_SPEC_HANDLING (usp_special_hanling).

    Add the id and sym columns.

    Create a join on the table AFF_END_USER_HANDLING_REL you created few steps ago and left join the special_handling column to the id column in the AFF_END_USER_SPEC_HANDLING table you just created.

     

     

     

    You can repeat those steps to map the special handling for the customer, just change the name of every table you create. Like a SQL query, you have several join on the same table with different aliases. Wich is exactly what you will be doing in Xtraction

     

     

    And again, try to type the query in SQL first. This will be much easier after to replicate it in Xtraction.

     



  • 8.  Re: Xtraction query to show VIP Users (Special Handle)

    Broadcom Employee
    Posted Mar 02, 2018 11:15 AM

    Pier-Oliver..........

     

    Thanks once again for sharing your expertise for the benefit of the rest of the community members!

     

    Keep it up!



  • 9.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 02, 2018 11:21 AM

    glad to help



  • 10.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 07:57 AM

    Pier, how are you?

     

    Hey, many thanks in advance for your answer.

     

    Yeah, I'm a noob on the SQL area so for sure that's the main reason for why I'm struggling with this.

     

    With your reply I could have fields where when they're dragged into a Xtraction dashboard, I can see how many tickets are VIP and how many are not. But when I go to "see records" I do not have a field saying which VIP users are, I just see the following

     

    Number|OpenDate|Reportedby|Assignee|Group|Priority|Status|Summary

     

    For this, I am using the "Sym" Field created inside "AFF_END_USER_SPEC_HANDLING" table.

     

    Should I add another table or another join to show that value?

     

    Thanks once again.



  • 11.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 08:36 AM

    The columns displayed when you click on "Show results" are configurable.

     

    In Xtraction GUI, click on Administration -> Settings -> Default columns -> Right click incident -> edit and add your special handling sym column here.

     

    Be aware that this will apply to everyone using the "show results" option.

     

     

    Otherwise you may want to consider a Pivot Dahsboards.

     

    IT will give you something like :

     

    ticket numberVIP (CEO)VIP (Administration council)Blind
    12341
    12541
    15671
    16781

     

     

    I am not able to show you a screenshot since i did not mapped special handling in Xtraction.

     

    Hope this helps.



  • 12.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 08:39 AM

    I forgot to tell :

     

    Your new field "sym" in the special handling table needs to be checked as Groupable (checkbox "group" in the datamodel) for you to use it in a pivot dashboard.



  • 13.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 11:16 AM

    Sorry Pier, what I meant is to drag it directly and show it as Grid on Xtraction.

     

    Reckon that it might have something to do in the "Expression" configured on the "Sym" Field but I can't get it.

     

    Regards.



  • 14.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 11:20 AM

    You want a grid that shows what?

     

    Users by special handling?

    or

    Ticket by affected end user special handlings?



  • 15.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 11:46 AM

    The latter one, Tickets by affected end user special handling.

     

    Example

    Username                           Count

    CEO Company                     10

    Assistant to CEO                    5

    Treasury Head                        3



  • 16.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 12:25 PM

    Simply check the "group" checkbox of the "sym" field of special handling and drag it from the group/grid component :



  • 17.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 01:32 PM

    That checkbox is checked.

    But when I dragged I see (as a Grid Example)

     

    (blank)            679

    VIP                 10



  • 18.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 01:38 PM

    Add a filter : Special handling is not null



  • 19.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 02:21 PM

    In fact, in your case it's gonna be Sym is not null.

     

    But let me suggest you to add a meaningfull alias to the Sym field. Otherwise you will get lost rapidly



  • 20.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 02:58 PM

    pier-olivier.tremblay

    After adding the filter on the Data Model, I went back to the Xtraction GUI, dragged "Affected end user" to the layout, and then filter them with the "VIP field" after this I'd manage to show VIP users with open tickets.

     

    So, many thanks for your time and patience, not only because you helped me with the answer but also because I understood every step.

     

    Regards.



  • 21.  Re: Xtraction query to show VIP Users (Special Handle)

    Posted Mar 05, 2018 03:01 PM

    My pleasure!