How can one find out what attachments are linked to which tickets in the backend database tables?

Products

• CA IT Asset Manager
• CA Software Asset Manager (CA SAM)
• ASSET PORTFOLIO MGMT- SERVER
• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

WARNING: This document discusses the relationship between backend SD tables. It is provided for informational purposes only. Please do not directly modify any information in the backend DB tables.

Depending on the ticket, for requests/incidents/problems, there is a table called usp_lrel_attachments_requests. For Change Orders, it is usp_lrel_attachments_changes.

Using Call Requests as an example:

 Call_Req.persid field is linked to usp_lrel_attachments_requests.cr field
 Attachment.id field is linked to usp_lrel_attachments_requests.attmnt field

 

 

Environment

CA Service Management 17.X

Resolution

To find out for a given call request, what attachments are linked:

Find out the call req's persid, based on whichever parameters you know of the case. For instance, if you know the ticket number, or ref_num, say it was "T-1234", you can run:

pdm_extract -f "select persid, ref_num from Call_Req where ref_num = 'T-1234'"

You would get an entry such as this:

TABLE Call_Req
       persid ref_num
       { "cr:663422" ,"T-1234" }

In this case, "cr:663422" is the call_req persid, which will be needed to obtain the list of attachments on the given request ticket.

One can then run:

pdm_extract -f "select attmnt, cr from usp_lrel_attachments_requests where cr = 'cr:663422'"

This command will obtain the list of attachments on the given request ticket, which in this case, are associated with persid "cr:663422" or Ticket T-1234

An example output:

TABLE usp_lrel_attachments_requests
       attmnt cr
       { "400001" ,"cr:663422" }
       { "400007" ,"cr:663422" }
       { "400016" ,"cr:663422" }
usp_lrel_attachments_requests
       rows:3

For each value on the attmnt column of the above extract, one can then run:

pdm_extract -f "Select attmnt_name, id from Attachment where id = 400001"
pdm_extract -f "Select attmnt_name, id from Attachment where id = 400007"
pdm_extract -f "Select attmnt_name, id from Attachment where id = 400016"

You would get a result such as this:

TABLE Attachment
       attmnt_name id
       { "test.txt" ,"400001" }
Attachment
       rows:1
 
TABLE Attachment
       attmnt_name id
       { "abcdefghij" ,"400007" }
Attachment
       rows:1
 
TABLE Attachment
       attmnt_name id
       { "test.msg" ,"400016" }
Attachment
       rows:1

 

To find out for a given attachment, what call_requests are associated:

Locate the attachment you are interested in obtaining information on. An easy way to find on a specific attachment is to search by its uploaded file name. The objective is to find out the corresponding id number on the attachment table. In this case, let's assume the file name is "test.txt"

pdm_extract -f "Select attmnt_name, id from Attachment where attmnt_name = 'test.txt'"

A more brute force method is to do a straight output of all attachments, displaying id number and file name:

pdm_extract -f "Select id, attmnt_name from Attachment"

In this example, you would get:

 

TABLE Attachment
       id attmnt_name
       { "400001" ,"test.txt" }
       { "400002" ,"test2.txt" }
       { "400003" ,"test3.txt" }
       { "400006" ,"test4.txt" }
       { "400007" ,"test5.txt" }
       { "400009" ,"test6.txt" }
       { "400011" ,"sample-cont.txt" }
       { "400013" ,"test.msg" }
       { "400016" ,"test.msg" }
Attachment
       rows:9

 

Take the attachment id number (for example, I'll choose '400002') and run this query on the usp_lrel_attachments_requests table with the given id:

pdm_extract -f "select attmnt, cr from usp_lrel_attachments_requests where attmnt = 400002"

You will get something like this in response

 

TABLE usp_lrel_attachments_requests
       attmnt cr
       { "400002" ,"cr:663422" }
usp_lrel_attachments_requests
       rows:1

 

Note: If you get no results back then the given attachment is an orphan and not linked to a given ticket.

Finally, obtain the call_req persid, in this case, cr:663422, and run:

 

pdm_extract -f "select persid, ref_num from Call_Req where persid = 'cr:663422'"
 
 
TABLE Call_Req
       persid ref_num
       { "cr:663422" ,"T-1234" }
Call_Req
       rows:1

 

The associated ticket would be the ref_num value, in this case, T-1234.