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
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.
Hi @johsc03 there, thanks for replying but the link takes me back to the top of this topic instead to another doc.
My apologies Esteban, the link below should be correct:
CA Xtraction::. Edit Data Model
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.
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.
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)
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.
Thanks once again for sharing your expertise for the benefit of the rest of the community members!
Keep it up!
glad to help
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
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.
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 :
I am not able to show you a screenshot since i did not mapped special handling in Xtraction.
Hope this helps.
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.
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.
You want a grid that shows what?
Users by special handling?
Ticket by affected end user special handlings?
The latter one, Tickets by affected end user special handling.
CEO Company 10
Assistant to CEO 5
Treasury Head 3
Simply check the "group" checkbox of the "sym" field of special handling and drag it from the group/grid component :
That checkbox is checked.
But when I dragged I see (as a Grid Example)
Add a filter : Special handling is not null
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
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.