CA Service Management

Buenas tardes me gustaria que me colaboran, en saber como hago la consulta al la base de datos y que me llame todos los datos , por que la que hago me llama los id , y no con los datos del export

https://communities.ca.com/thread/241774404
Buenas tardes me gustaria que me colaboran, en saber como hago la consulta al la base de datos y que me llame todos los datos , por que la que hago me llama los id , y no con los datos del export

Good Morning John.
 
Please explain in more detail on what you are asking in this thread?
Which product is involved? Ca Service Desk or CA Service Catalog?
And which release are you running then?
 
You are asking for a query on the mdb, am i correct?
And what would you like to retrieve from the mdb? What should the result of the query then be?
 
Thanks in advance for your help in clarifying this and kind regards, Louis van Amelsfort.

Good morning Louis.  Please explain in more detail what you are asking in this thread? I want to make a query to the database to extract the information of all the surveys received What product is involved? Ca Service Desk CA Service Catalog?  Service Desk  And what release are you running then? Oracle  You are asking for a query in the mdb, am I correct?  If it's an mdb query And what would you like to recover from mdb? All the information about the survey received and have a detail  What should be the result of the consultation then?  This query should bring all the data of the finder and id of tikect,  But what happens with the original id  example  Case 1236-17 shows me cr 4023656  Thank you ,
 John.

English is helping

the table storing survey data are starting with survey in the mdb

The technical reference guide containing the dictionary must be your starting point for such  information :

https://docops.ca.com/ca-service-management/14-1/en

note that the cr:1223456 is the persid of your ticket that you can join with the call_req table to retrieve the ref_num

below a simple TSQL example to retrieve some particular survey info:

DECLARE @StartDate DATETIME, @EndDate DATETIME   
SET @StartDate = DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0)   
SET @EndDate = dateadd(dd, -1, DATEADD(mm, 1, @StartDate))

SELECT     call_req.ref_num, DATEADD(ss, survey.last_mod_dt, '1970/01/01 00:00:00') AS [Survey Date], call_req.type AS [Call Type], prob_ctg.sym AS category, [Group].last_name AS Group_name,
                      requester.first_name AS [Requester first name], requester.last_name AS [Requester last name], customer.first_name AS [Customer first name],
                      customer.last_name AS [Customer last name], survey.nx_comment AS [Survey comment], survey_question.sequence AS [Question Sequence],
                      survey_question.txt AS [Question text], survey_answer.sequence AS [Answer Sequence], survey_answer.txt AS [Answer Text],
                      survey_answer.selected AS [Answer Selected]
FROM         ca_contact AS customer RIGHT OUTER JOIN
                      survey LEFT OUTER JOIN
                      call_req LEFT OUTER JOIN
                      ca_contact AS [Group] ON call_req.group_id = [Group].contact_uuid LEFT OUTER JOIN
                      prob_ctg ON call_req.category = prob_ctg.persid LEFT OUTER JOIN
                      usp_owned_resource AS CI ON call_req.affected_rc = CI.owned_resource_uuid ON survey.object_id = call_req.id ON
                      customer.contact_uuid = call_req.customer LEFT OUTER JOIN
                      ca_contact AS requester ON call_req.requested_by = requester.contact_uuid FULL OUTER JOIN
                      survey_question LEFT OUTER JOIN
                      survey_answer ON survey_question.id = survey_answer.own_srvy_question ON survey.id = survey_question.owning_survey
WHERE     (survey.object_type = N'cr') AND (DATEADD(ss, survey.last_mod_dt, '1970/01/01 00:00:00') BETWEEN @StartDate AND @EndDate)
ORDER BY call_req.ref_num

Hope this help

/J

John,

It should be noted that CABI reporting includes out of the box survey reports that may gather what you want.

I threw together a query that provides you in the results the case number, the survey name, the survey question, the answers provided from the customer, as well as comments. The query may be crude, and I'm sure it can be written cleaner. I also excluded a bunch of the data to only include the minimum, so of course adjust it to something that makes sense for you:

select call_req.ref_num, survey.sym, survey_question.txt, survey_answer.selected,survey_answer.txt,survey_question.qcomment_label,survey_question.qcomment from survey
LEFT JOIN call_req
ON survey.object_id = call_req.id
FULL JOIN survey_question
ON survey_question.owning_survey=survey.id
FULL JOIN survey_answer
ON survey_answer.own_srvy_question=survey_question.id
where survey_answer.selected='1' OR qcomment IS NOT NULL

Thank you very much ALEXANDER, this is what I need but with all the data of the survey.

John,

Like I said, you can modify it to include whatever information you'd like. If you want all the survey question and answer information you can adjust it to be:

select call_req.ref_num, survey.sym, survey_question.*, survey_answer.* from survey
LEFT JOIN call_req
ON survey.object_id = call_req.id
FULL JOIN survey_question
ON survey_question.owning_survey=survey.id
FULL JOIN survey_answer
ON survey_answer.own_srvy_question=survey_question.id
where survey_answer.selected='1' OR qcomment IS NOT NULL

John 777, what are you missing in your query?

The queries examples provided by myself and Alex both return you with the re_num  of the corresponding ticket.

Regards,

Jerome

Yes, but I need all the data of the survey and the asignataruo of the ticket

Alex, thanks for the example.

small correction of you query as it's important to filter the object type too otherwise you may report a wrong ticket as id may cross from a change or another object.

In your case for the cr object the where clause will look like:

WHERE (survey_answer.selected='1' OR qcomment IS NOT NULL) AND (survey.object_type = N'cr')

my 2 cents

/J

I need to perform this query with all this data in excel but the crystal display distorts the data.

I need to perform this query with all this data in excel but the crystal display distorts the data.

I need to perform this query with all this data in excel but the crystal display distorts the data.

Good Afternoon John.

Thanks for your explanation.

Expecting then for some one of the CA Service Desk users, to further respond to this.

Kind regards, Louis.