Extract data by date range using pdm_extract

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

This is a guide to setting a date range in the pdm_extract utility of Service Desk. It also covers different syntax options, typical output, errors and trouble-shooting.

The pdm_extract utility can be used to extra data from Service Desk according to a date range.

This process is useful for generating a text file which can be used for reporting purposes or for transferring a subset of data to another system.

It only requires access to a command prompt on the Service Desk server and does not require access to the database server. As the commands are native to Service Desk, they may be run on any Service Desk Version running on any operating system and database combination. This has advantages when working on an unfamiliar platform.

Environment

Release: 14.1 or higher
Component:  CA Service Desk Manager

Resolution

The usual ' pdm_extract -f "Select . . . " ' statement is used with the addition of the " DATE " keyword.

One way of doing the extract is to use the format:

 DATE 'YYYY-MM-DD'

e.g.: DATE '2016-03-01'

A full command may be:

 pdm_extract -v -f "select * from Call_Req where open_date >= DATE '2016-03-01'"

Note:

• There are single quotes around the date.
• Dates are expressed in YYYY-MM-DD format.
• Leading zeroes are included eg " 03 " and not " 3 ".
• The word DATE must be in upper case.
• There must be a date defined field in the table being queried eg close_date
• Both Incidents and Call Requests are stored in the Call_Req table.

Example

For extracting after or before a date:

The following example extracts all Incidents and Call Requests; they are stored in the same Call_Req table, that were opened between 01 and 30 March.

The command in this instance only extracts three fields, namely id, reference number and date opened.

Syntax:

 pdm_extract -v -f "select id, ref_num, open_date from Call_Req where open_date >= DATE '2016-03-01' and open_date < DATE '2016-03-30' and type = 'I'"

Additional Information

Dates in Service Desk:

Dates are stored in the database in UNIX time. This is the number of seconds since January 1st 1970. The above output is a conversion of this time. The large number "1456790400" in the output is the UNIX time equivalent of the date entered.