Filter, Sort and Paging Expressions

When multiple records are returned by a GET request, Odata expressions can be appended to the end of the URI. The three types of expressions determine whether the multiple records are filtered, sorted, or paged. Documentation identifies whether a field is filterable or sortable. Paging expressions are always available for any GET method that returns multiple records.

Multiple Odata expressions can be combined in the same URI. In the following example, the first Odata expression is delimited by a question (?) character. Subsequent Odata expressions are delimited with an ampersand (&) character. 

GET /automation/servicedesk/{serviceDeskId}/tickets?$skip=30&$top=10&$orderby=Severity

Supported Filter Expressions

Filter expressions limit the set of multiple records returned by a GET request. 

GET <yourVSA>/api/v1.0/system/OrgNames?$filter=startswith(OrgName,'SapphireIT')
GET <yourVSA>/api/v1.0/assetmgmt/assets?$filter=AssetName eq 'Win8'

Filtering Using Decimal Data Types

When filtering using decimal values—such as filtering agents using an AgentId—an M must be added to the end of the number to denote a decimal data type instead of the default double data type. For example:

http://vsa.com/assetmgmt/agents?$filter=AgentId eq 121747357971679M 


                                            

                                                

                                                    
                                                        
substringof
                                                        

                                                    		
                                                    
                                                        
$filter=substringof('cab', OrgName)
                                                        

                                                    		
                                                    
                                                        
returns all organizations with names containing 'cab'

                                                    		
                                                

                                                

                                                    
                                                        
startswith 
                                                        

                                                    		
                                                    
                                                        
$filter=startswith(OrgName, 'pro')
                                                        

                                                    		
                                                    
                                                        
returns all organizations with names beginning with 'pro'

                                                    		
                                                

                                                

                                                    
                                                        
endswith
                                                        

                                                    		
                                                    
                                                        
$filter=endswith(OrgName, 'ing')
                                                        

                                                    		
                                                    
                                                        
returns all organizations with names ending with 'ing'

                                                    		
                                                

                                                

                                                    
                                                        
and
                                                        

                                                    		
                                                    
                                                        
$filter=orgtype eq 'Sales' and orgRevenue gt '1000000'
                                                        

                                                    		
                                                    
                                                        
returns all organizations classified as sales organizations with revenue greater than one million

                                                    		
                                                

                                                

                                                    
                                                        
equal
                                                        

                                                    		
                                                    
                                                        
$filter=orgName eq 'Sapphire Products' 
                                                        

                                                        
$filter=AgentId eq 123456789M
                                                        

                                                    		
                                                    
                                                        
returns the organization with the name 'Sapphire Products'

                                                        
returns an agent with an agentguid equal to 123456789. Notice the M at the end of the agentguid number in the filter, indicating a decimal data type.

                                                    		
                                                

                                                

                                                    
                                                        
greaterthan
                                                        

                                                    		
                                                    
                                                        
$filter=orgRevenue gt '1000000'
                                                        

                                                    		
                                                    
                                                        
returns all organizations with revenue greater than one million

                                                    		
                                                

                                                

                                                    
                                                        
greaterthanorequal
                                                        

                                                    		
                                                    
                                                        
$filter=orgRevenue ge '1000000'
                                                        

                                                    		
                                                    
                                                        
returns all organizations with revenue greater than or equal to one million

                                                    		
                                                

                                                

                                                    
                                                        
lessthan
                                                        

                                                    		
                                                    
                                                        
$filter=orgRevenue lt '1000000'
                                                        

                                                    		
                                                    
                                                        
returns all organizations with revenue less than one million

                                                    		
                                                

                                                

                                                    
                                                        
lessthanorequal
                                                        

                                                    		
                                                    
                                                        
$filter=orgRevenue le '1000000'
                                                        

                                                    		
                                                    
                                                        
returns all organizations with revenue less than or equal to one million

                                                    		
                                                

                                            

                                            
Filtering by Date

                                            

                                                

                                                    
                                                        
greaterthan
                                                        

                                                    		
                                                    
                                                        
$filter=LastCheckInTime gt DATETIME'2019-06-17T07:14:20'
                                                        

                                                    		
                                                    
                                                        
returns all dates that are later than the defined one

                                                    		
                                                

                                                

                                                    
                                                        
greaterthanorequal
                                                        

                                                    		
                                                    
                                                        
$filter=LastCheckInTime ge DATETIME'2019-06-17T07:14:20'
                                                        

                                                    		
                                                    
                                                        
returns dates that are later than or equal to the defined one

                                                    		
                                                

                                                

                                                    
                                                        
lessthan
                                                        

                                                    		
                                                    
                                                        
$filter=LastCheckInTime lt DATETIME'2019-06-17T07:14:20'
                                                        

                                                    		
                                                    
                                                        
returns dates that are older than the defined one

                                                    		
                                                

                                                

                                                    
                                                        
lessthanorequal
                                                        

                                                    		
                                                    
                                                        
$filter=LastCheckInTime le DATETIME'2019-06-17T07:14:20'
                                                        

                                                    		
                                                    
                                                        
returns dates that are older or equal to the defined one

                                                    		
                                                

                                            

                                            
NOTE    DATETIME is case sensitive and should be in capital letters as sampled above.

                                            
Supported Sort Expressions

                                            
Sort expressions sort the multiple records returned by a GET request into ascending or descending order. 
GET /automation/servicedesk/{serviceDeskId}/ticketstatus$orderby=Priority

                                            

                                                

                                                    
                                                        
sort ascending
                                                        

                                                    		
                                                    
                                                        
$orderby=Priority
                                                        

                                                    		
                                                    
                                                        
sorts by selected column in ascending order.

                                                    		
                                                

                                                

                                                    
                                                        
sort descending
                                                        

                                                    		
                                                    
                                                        
$orderby=Priority desc
                                                        

                                                    		
                                                    
                                                        
sorts by selected column in descending order.

                                                    		
                                                

                                            

                                            
Supported Paging Expressions

                                            
When multiple records are returned by a GET request, paging expressions set the number of records to skip and the number of records to return. 
GET /automation/servicedesk/{serviceDeskId}/tickets?$skip=30&$top=10

                                            
NOTE   The maximum number of records that can be returned by a GET request is 100, even if no paging expression is used. 

                                            

                                                

                                                    
                                                        
records skipped
                                                        

                                                    		
                                                    
                                                        
$skip=5
                                                        

                                                    		
                                                    
                                                        
skips the first five records

                                                    		
                                                

                                                

                                                    
                                                        
records returned
                                                        

                                                    		
                                                    
                                                        
$top=10
                                                        

                                                    		
                                                    
                                                        
the number of records to be returned