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: SDMU0M99000-14.1-Service Desk Manager-Full License
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'"

<Please see attached file for image>

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.

Other Syntax

Extracting all fields from the table:

To extract all fields, the wildcard of "*" would be used e.g.:

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

Writing data to a text file:

Data can be written to a file with the usual ">" (create file) or ">>" (append) redirects e.g. the following will create or overwrite a file called Call_Req_2016-03-01.TXT:

 pdm_extract -v -f "select id, ref_num, open_date from Call_Req where open_date >= DATE '2016-03-01'" > Call_Req_2016-03-01.TXT

For a date range:

It can be useful to extract files according to a date range eg All Incidents and Call Requests logged in the month of March 2016.

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

For only Incidents or only Call Requests:

Both Incidents and Call Requests are stored in the same table, Call_Req. They are distinguished in the table by the "type" field set to either "R" (Requests) or "I" (Incidents).

The following will extract all Incidents only for March 1st 2016 and output the results to a file.

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'" > Inc_2016-Marc.txt

Select clause:SELECT #ref_num , #open_date , #id FROM Call_Req

Where clause:where open_date >= 1456790400 and open_date < 1459296000 and type = 'I'

<Please see attached file for image>

Possible Errors

Normal run and overview of logging:

A normal run of the pdm_extract command using a "statement" will generate output as above and write only a stop and start line to the Service Desk stdlog. For example.

<Please see attached file for image>

Some common problems will generate both errors on screen and in the Service Desk stdlogs at normal logging levels, as below, where normal log level means that pdm_logstat -vL returns no logging set eg:

 C:\>pdm_logstat -vL

 INDEX TYPE LEVEL PATTERN

 C:\>

Data should always be checked to make sure that it is at least consistent with expectations. For example, 500 Incidents may be normal for a month at one site, so an output of 5 would warrant double-checking for typos. Just because a query returns data does not mean that the query is "correct."

The most important line to review in all of these error messages is the "where clause." This is usually the line that the extract had problem with and may need close observation.

The error codes are also useful, although they have a tendency to be generic in the case of syntax errors.

It is rare that additional investigation is required, but the "-v" verbose extract option can be added to the pdm_extract command if additional logging is required. Entries will then be written to the stdlog. And there is the option of increasing the pdm_logstat level.

"DATE" must be in uppercase:

If the "DATE " command is issued in lowercase, this error results:

 pdm_extract -v -f "select id, ref_num from Call_Req where open_date >= date  '2016-03-01'"

<Please see attached file for image>

These lines are written to the Service Desk stdlog at normal logging levels:

<Please see attached file for image>

Syntax incomplete:

If copying and pasting these commands, note that the syntax of the command is entered on one line. If a partial input of the line is done, many errors could result, with this one being typical of a syntax error:

 pdm_extract -v -f "select id, ref_num from Call_Req where open_date >=

 Select clause:SELECT #ref_num , #id FROM Call_Req

<Please see attached file for image>

These lines are written to the Service Desk stdlog at normal logging levels:

<Please see attached file for image>

Leadings zeroes must be included in the date format:

There are many possible ways to go wrong with the syntax. This is another - leaving out the leading zeroes on the dates. Observe the YYYY-MM-DD format. In this example, "3" is specified for March instead of "03".

 pdm_extract -v -f "select id, ref_num, open_date from Call_Req where open_date >= DATE '2016-3-01'"

<Please see attached file for image>

 Select clause:SELECT #ref_num , #open_date , #id FROM Call_Req

These lines are written to the Service Desk stdlog at normal logging levels:

<Please see attached file for image>

Note: The pdm_extract errors can appear similar at normal logging levels. The output in both this example and the example where the word " DATE " was in lowercase rather than upper-case appear similar, with only the change of case in the error to indicate a difference.

All output shown above is from a Service Desk r14.1 system running on Windows 2012, SQL 2012 on one box.