Add support for date values and operators in query URLs

[replicaJunction]

I'd like to be able to add a date to a query - for example, query for items in a table created between two dates, or updated since a given date. PowerShell can do this after the fact using Where-Object, but that means I have to query for ALL the data first and filter it locally. It's the ServiceNow equivalent of running Get-ADComputer -Filter * | Where-Object { $_.ComputerName -like '*Test*' } instead of running Get-ADComputer -Filter "Name -like '*Test*'".

It looks like adding dates to a ServiceNow query URL is ta little hairy, but a forum post describes one way to do it:

/api/now/v1/table/task_ci?sys_parrn_query=active=true^sys_created_onBETWEENjavascript:gs.dateGenerate('2015-04-16','00:10:00')@javascript:gs.dateGenerate('2015-04-22','12:59:59')

Adding support for that isn't too difficult, but the bigger issue is how the parameter should look. How should the user provide the date, the field to filter on, and the operator (between, greater than, etc.)? This doesn't really fit in the existing definition for New-ServiceNowQuery, Get-ServiceNowTableEntry, or any of the convenience methods - since the operator in the query isn't "equals," a simple key = value syntax won't fly for this one using -MatchContains or -MatchExact. The only way to do this in the current state of the module is to manually create a query and pass it directly to the -Query parameter of Get-ServiceNowTable.

I'm not sure what the best way is to implement this feature. I'll create a second post with a few of my own ideas, but I want to be clear that I'm not suggesting a specific implementation yet - just trying to figure out what would work best.

[replicaJunction]

Here are a few possibilities I can think of:

Add parameters for each individual property

This first option isn't my favorite, but I'm including it for sake of completion. Add parameters like -CreatedSince, -CreatedBetween, -UpdatedSince, -UpdatedBetween, and so on, and manually define the date parameters for each one. This isn't very flexible, and it would add a lot of parameters.

Add a -MatchDate Parameter

Add a -MatchDate parameter to functions that currently have -MatchContains and -MatchExact. The value of this parameter would probably have to be a hashtable to support multiple operators. Here are a couple examples of how that could look (user experience):

@{
    Property = 'sys_created_on'
    Operator = 'Between'
    Values = @(
        (Get-Date '01/01/2019')
        (Get-Date '01/02/2019')
    )
}
# translates to sys_created_onBETWEENjavascript:gs.dateGenerate('2019-01-01','00:00:00')@javascript:gs.dateGenerate('2019-02-01','00:00:00')


@{
    Property = 'sys_updated_on'
    Operator = 'ge' # -ge (Greater than or equal to)
    Values = @( (Get-Date '01/01/2019') )
}
# translates to sys_updated_on>=javascript.gs.dateGenerate('2019-01-01','00:00:00')

This would add a single parameter to a lot of functions, but it wouldn't be nearly as bad as adding a bunch of parameters like the first suggestion.

Add a new -Match parameter

Instead of adding a new -MatchDate parameter, we could just add a -Match parameter that handles MatchContains, MatchExact, and the new date behavior like this:

# To replicate existing MatchExact behavior
@{
    Property = 'number'
    Operator = 'equals' # or something shorter like "eq"
    Value    = 'RITM01234567'
}

# To replicate existing MatchContains behavior
@{
    Property = 'propName'
    Operator = 'contains'
    Value    = 'Foo'
}

# To represent new date behavior
@{
    Property = 'sys_created_on'
    Operator = 'between'
    Value    = @(
        (Get-Date '01/01/2019')
        (Get-Date '01/02/2019')
    )
}

The -MatchContains and -MatchExact parameters could be preserved both for compatibility and convenience.

The New-ServiceNowQuery function could be updated to handle all of this behavior so it's not recreated in each function (or it could be moved to a new helper - something like Merge-MatchParameter).

Require the user to construct date parameters themselves

Create a brand new function that constructs a single date parameter value. Something like this:

PS> Get-ServiceNowQueryDateParameter -Property 'sys_created_on' -Operator 'between' -Value @( (Get-Date '01/01/2019'), (Get-Date '01/02/2019') )

This could either output the raw URL string, or it could output a hashtable. Either way, existing functions would need to be modified with a new parameter that accepts its output (something like -AdditionalParameters).

Again, all of these are just possibilities, and I'm not sure what the best way is for this to look yet.

[replicaJunction]

After thinking about this a bit more, I think New-ServiceNowQuery might be a reasonable place to put this for the time being (and this would be an argument against making it private, as in #60 ). A new parameter could be added to that function - something like MatchAdvanced or just Match - which could accept the new hashtable syntax:

$dateMatch = @{
    Property = 'sys_updated_on'
    Operator = 'gt'
    Values = @( (Get-Date '01/01/2019') )
}

$query = New-ServiceNowQuery -Match $dateMatch

This could be passed back into Get-ServiceNowTable.

It's not exactly intuitive, since it would require the user to use Get-ServiceNowTable instead of the nicer helper functions, but it would be a quick way to add the functionality without changing existing functionality or adding new parameters to everything.

[Rick-2CA]

Since December 2016 I've had an email in my work inbox from our SNow dev at the time with the string for this. Have never taken the time to solve for it. Have always wanted to.

I'm favoring the idea of a hash parameter that expects a field, operator, and two potential date fields with the ability for one to be null. Pass that to a private function that has validation that'll kick back the string to add to the rest of the call later. I'd like to see something added to each existing function rather than having to introduce a new one or get people to use what probably should be private functions.

[replicaJunction]

I took a first pass at implementing this in a new branch on my fork of the repo. The first commit creates two helper functions and some Pester tests for them, and the second adds a new Match parameter to New-ServiceNowQuery which leverages them. The next step would be to go through and add that parameter to public-facing functions, but I wanted to pause for a bit and see what you thought of the direction I was going first.

A couple basic examples of stuff supported by the Match parameter:

@{
    Property = 'sys_id'
    Operator = 'eq'
    Value = '12345'
}

@{
    prop = 'sys_created_on'
    op = 'gt'
    val = (Get-Date).AddDays(-3)
}

@{
    p = 'sys_updated_on'
    o = 'between'
    v = @(
        (Get-Date).AddDays(-7)
        (Get-Date).AddDays(-3)
    )
}

I also added a fallback option - if the hashtable provided doesn't have distinct property, operator, and value parameters, assume it's actually a series of key=value pairs that should be matched exactly:

@{
    sys_id='12345'
    sys_created_by='myUsername'
}

The Pester test for the helper function Get-MatchQueryParam documents just about everything that I've added support for so far.

What are your thoughts? Is this moving in a good direction, or would you rather see a different approach?

[replicaJunction]

For convenience, here are a couple of fully-realized examples of calling New-ServiceNowQuery. I expect usage would be similar once the parameter is added to public-facing functions.

# Formal syntax
New-ServiceNowQuery -Match @{Property = 'sys_id'; Operator = 'eq'; Value = '12345'}
ORDERBYDESCopened_at^sys_id=12345



# Example of using a date and the abbreviated hashtable syntax
New-ServiceNowQuery -Match @{prop = 'sys_created_on'; op = 'gt'; val = (Get-Date).AddDays(-3) }
ORDERBYDESCopened_at^sys_created_on>javascript:gs.dateGenerate('2019-03-09','14:10:18')



# Example of the "between" operator and very abbreviated hashtable syntax
$hash = @{
    p = 'sys_updated_on'
    o = 'between'
    v = @(
        (Get-Date).AddDays(-7)
        (Get-Date).AddDays(-3)
    )
}
New-ServiceNowQuery -Match $hash
ORDERBYDESCopened_at^sys_updated_onBETWEENjavascript:gs.dateGenerate('2019-03-05','14:11:46')@javascript:gs.dateGenerate('2019-03-09','14:11:46')



# If the hashtable cannot be identified with distinct property, operator,
# and value items, fall back on legacy behavior. This should be equivalent
# to using the MatchExact parameter

New-ServiceNowQuery -Match @{'sys_id'='12345';'sys_created_by'='myUsername'}
ORDERBYDESCopened_at^sys_created_by=myUsername^sys_id=12345

New-ServiceNowQuery -MatchExact @{'sys_id'='12345';'sys_created_by'='myUsername'}
ORDERBYDESCopened_at^sys_created_by=myUsername^sys_id=12345

[Rick-2CA]

Just a note to let you know I've seen your update, but haven't been able to spend time with it. Looking like no earlier than next week before I will right now. Sorry and thanks for your patience.

[lansalot]

Afternoon all

Did you manage to get a look at merging this yet, Rick? Great module by the way, it's really handy.

[Rick-2CA]

Brett Miller and I had some discussions around it in March or April. As stated by replicaJunction it's not that it's hard, it's how to integrate it into the module. We floated some ideas, but were working on another challenge and the time and we both got caught up in other life events so this has fallen down the list a ways.

[gdbarron]

Wow, I hadn't seen this until now and realized I implemented something similar to what @replicaJunction was suggesting. It's been 2 years since this thread was updated, any chance you guys have seen the updates to New-ServiceNowQuery and Get-ServiceNowRecord? I haven't added any smarts for dates yet, but that's on my list. Anything else you'd like to see?