WebService REST - Options for Fields


How to search and filter in the database

By field

In the HTTP requests, you can do a search/filter on one or several fields of the Service Manager database by using the names of corresponding objects. However, you cannot use only once the same object in the same request (the union is not possible).

The syntax to be used is the following one.

search={(prefix_object_name_1.)object_name_1}:{object_name_value_1}(,{(prefix_object_name_2.)object_name_2}:{object_name_value_2})
  • (prefix_object_name_x.): Prefix to be only indicated when the wished object is contained in another object.
  • object_name_x: Name of the used object. Mandatory parameter.
  • object_name_value_x: Value of the used object. Mandatory parameter.

 

example 

Check icon.png   Correct requests
/employees?search=last_name:"Morley, Gaby"

/assets?search=asset_tag:"1012D"
/assets?search=employee.last_name:"Taverner, David"
/assets?search=department.department_en:"Finance"

/requests?search=department.department_en:"Industry"
/requests?search=status.status_en:"Closed"
/requests?search=status.status_en:"In Progress",submit_date_ut:this_week&sort=recipient.last_name+asc
/requests?search=recipient.last_name:"Morley, Gaby"
/requests?search=requestor.last_name:"Morley, Gaby"
/requests?search=recipient.last_name:"Morley, frédérique",status.status_en:"In Progress"
/requests?search=recipient.e_mail:fpachelbel@evtry.com,status.status_en:"Escalated"
/requests?search=recipient.e_mail:adam@evtry.com,status.status_en:"In Progress",submit_date_ut:today
/requests?search=recipient.last_name:"Adam, George",status.status_en:"Closed",submit_date_ut:this_year
/requests?search=recipient.last_name:"Adam, George",status.status_en:"In Progress",submit_date_ut:this_week&sort=submit_date_ut+asc
Not check icon.png   Incorrect requests
/requests?search=recipient.e_mail:adam@evtry.com,morley@evtry.com
/requests?search=recipient.e_mail:adam@evtry.com,recipient.e_mail:morley@evtry.com
/requests?search=recipient.e_mail:adam@evtry.com&morley@evtry.com
/requests?search=recipient.e_mail:adam@evtry.com&recipient.e_mail:morley@evtry.com

By date

Note: The expected date format is that employed by the logged-in Service Manager REST API user.

  • Filtering by date can be carried out in all Date fields.
  • Date intervals are inclusive.
  • You can use predefined intervals.

example 

search=SUBMIT_DATE_UT:last_week
search=SUBMIT_DATE_UT:3daysbefore

List of predefined intervals

    Note : The values are not case sensitives.

  • this_year
  • this_month   
  • this_week
  • this_day / today
  • last_year
  • last_month
  • last_week
  • first_quarter / Q1
  • second_quarter / Q2
  • third_quarter / Q3
  • fourth_quarter / last_quarter/ Q4
  • next_day / tomorrow
  • next_week
  • next_month
  • next_year
  • daysbefore
  • daysafter
  • You can use specific intervals.

By date

example   Requests created from 07/09/2020 to 07/10/2020

Search=SUBMIT_DATE_UT:(2020-07-09;2020-07-10)

     Result:

{
"RFC_NUMBER": "I200709_000001",
"SUBMIT_DATE_UT": "2020-07-09T04:34:59.593-04:00"
}

By date and hour

Note: The use of this option is available since version Autumn 2020 of Service Manager.

example   Requests created from 07/09/2020 12:00 am to 07/10/2020 11:59 pm

search=SUBMIT_DATE_UT:(2020-07-09T00:00:00;2020-07-09T23:59:00)

    Result: 04:34:59.593 is corresponding to 08:34 am in UT hour

{
"RFC_NUMBER": "I200709_000001",
"SUBMIT_DATE_UT": "2020-07-09T04:34:59.593-04:00"
}

example   Requests created from 07/09/2020 12:30 pm to 07/09/2020 12:35 pm in the -4 hours time zone

search=SUBMIT_DATE_UT:(2020-07-09T12:00:00.000-04:00;2020-07-09T12:35:00.000-04:00)&fields=SUBMIT_DATE_UT 

    Result : 04:34:59.593 is corresponding to 08:34 am in UT hour in the -4 hours time zone

{
"RFC_NUMBER": "I200709_000001",
"SUBMIT_DATE_UT": "2020-07-09T04:34:59.593-04:00"
}

Formats for the date

Note: The use of formatDate parameter in the HTTP request string is available since version Oxygen 1.7 - Build 2018.1.131.0 of Service Manager.

Many options are available to format all the dates / hours and timezone information by using the formatDate parameter in the HTTP request string.

When you use this parameter, new fields suffixed by _FORMAT will be added in the string HTTP result to display the date in the format you want.

Notes

  • All Date fields can be formated.

    example  LAST_UPDATE, MAX_RESOLUTION_DATE_UT

  • The same date formatting applies to all fields specified in the HTTP request string.

Caution

  • This parameter is only used to display the date in the format you want. It does not allow you to filter information on a date. To do this, use the search parameter when the method allows it.

Examples

Formats for the date

Format Result

formatDate = l/M/Y

Friday/Nov/2018

formatDate = l jS \of F Y h:i:s A

Friday 16th of November 2018 12:00:00 AM

formatDate=Y-m-d H:i:s

2018-03-10 17:16:18

Using the formatDate parameter

Without Date fields formatting

https://{your_server}/api/v1/{your_account}/requests?max_rows=1&fields=LAST_UPDATE,MAX_RESOLUTION_DATE_UT

         Result:

{
   "HREF": "https://{your_server}/api/v1/{your_account}/requests?max_rows=1&fields=LAST_UPDATE,MAX_RESOLUTION_DATE_UT"
   "record_count": "1",
   "total_record_count": "13818",
   "records": [
        {
           "HREF": "https://{your_server}/api/v1/{your_account}/requests/000002",
           "RFC_NUMBER": "000002",
           "LAST_UPDATE": "2018-11-01T06:17:46.297-05:00",
           "MAX_RESOLUTION_DATE_UT": "2018-11-04T20:00:00.000-04:00"
        }
    ]
}

With Date fields formatting

https://{your_server}/api/v1/{your_account}/requests?max_rows=1&fields=LAST_UPDATE,MAX_RESOLUTION_DATE_UT&formatDate=l/M/Y

         Result: LAST_UPDATE_FORMAT and MAX_RESOLUTION_DATE_UT_FORMAT will be added with the desired date formatting.

{
   "HREF": "https:// {your_server}/api/v1/{your_account}/requests?max_rows=1&fields=MAX_RESOLUTION_DATE_UT,SUBMIT_DATE_UT&formatDate=l/M/Y"
   "record_count": "1",
   "total_record_count": "13818",
   "records": [
        {
           "HREF": "https:// {your_server}/api/v1/{your_account}/requests/000002",
           "RFC_NUMBER": "000002",
           "LAST_UPDATE": "2018-11-01T06:17:46.297-05:00",
           "MAX_RESOLUTION_DATE_UT": "2018-11-04T20:00:00.000-04:00",
           "LAST_UPDATE_FORMAT": "Thursday/Nov/2018",
           "MAX_RESOLUTION_DATE_UT_FORMAT": "Sunday/Nov/2018"
        }
    ]
}

Formatting options

Day

Format Description Example
d Day of the month as a number
  • With leading zero
01 to 31
D Short name of the day
  • Three letters
  • In the local language of the server - English as default
Sun (Sunday) to Sat (Saturday)
j Day of the month as a number
  • Without leading zero
1 to 31
l

(lowercase L)

Full name of the day Sunday to Saturday
N ISO-8601 day of the month as a number
  • The week begins on Monday
1 (Monday) to 7 (Sunday)
S Ordinal suffix for the number of the day: st, nd, rd ou th
  • Two letters
  • Note: You can use it with j
1st, 2nd, 3rd, 4th
w Day of the week as a number
  • The week begins on Sunday
0 (Sunday) to 6 (Saturday)
z Day of the year as a number
  • Without leading zero
0 to 365

Week

Format Description Example
W ISO-8601 week of the year as a number
  • The week begins on Monday
42 = 42th week of the year

Month

Format Description Example
F Full name of the month January to December
m Month of the year as a number
  • With leading zero
01 (January) to 12 (December)
M Short name of the month
  • Three letters
  • In the local language of the server - English as default
Jan (January) to Dec (December)
n Month of the year as a number
  • Without leading zero
1 (January) to 12 (December)
t Number of days in the month 28 to 31

Year

Format Description Example
L Leap year 0 = Non-leap year ; 1 = Leap year
o ISO-8601 year 1999 ; 2003
Y Year as a 4-digit number 1999 ; 2003
y Year as a 2-digit number 99 ; 03

Hour

Format Description Example
a Lowercase am (ante meridiem = before noon) or pm (post meridiem = after noon) after the hours 15am ; 15pm
A Uppercase AM (ante meridiem = before noon) or PM (post meridiem = after noon) after the hours 15AM ; 15PM
B Swatch Internet Time (SIT) 000 to 999
g 12-hour format of an hour
  • Without leading zero
1 to 12
G 24-hour format of an hour
  • Without leading zero
0 to 23
h 12-hour format of an hour
  • With leading zero
01 to 12
H 24-hour format of an hour
  • With leading zero
01 to 23
i Minute
  • With leading zero
00 to 59
s Second
  • With leading zero
00 to 59
u Microsecond 654321
v Millisecond 654

Full Date / Hour

Format Description Example
c ISO-8601 date 2004-02-12T15 :19 :21+00 :00
r RFC 2822 formatted date Thu, 21 Dec 2000 16 :01 :07+0200
U Number of seconds since the Unix Epoch
  • Reference date: January 1 1970 00:00:00 GMT
3/5/2019 at 15:44:18 = 1551800658 seconds

Timezone

Format Description Example
e Timezone identifier UTC ; GMT ; Atlantic/Azores
I

(capital i)

Standard time or daylights savings time 0 = Standard time ; 1 = Daylights savings time
O Timezone difference to Greenwich time (GMT) in hours +0200
P Timezone difference to Greenwich time (GMT) in hours:minutes +02:00
T Timezone abbreviation EST ; MDT
Z Timezone offset from UTC (Universal Coordinated Time) in seconds
  • The offset for timezones west of UTC is negative
  • The offset for timezones east of UTC is positive
-43200 to 50400
Tags:
Last modified by Christine Daussac on 2021/04/20 15:32
Created by Administrator XWiki on 2019/03/19 15:32

Shortcuts

Recent changes
•  EV Service Manager
•  Service Apps
•  EV Self Help
•  Service Bots

Powered by XWiki ©, EasyVista 2021