Retrieve all incidents created on this month programmatically. Indeed, it’s very simple and straight forward approach when it comes to reportings right? No, we aren’t focusing reporting table (sys_report) for now. Instead I go with simple query!

Using PowerShell it’s very easy to find start and end of the month! Yes, try the below PowerShell snippets and let me know your thought!

Beginning of the month

# Tip 1
$Now = [datetime]::Now
$BeginningOfTheMonth = [datetime]::new($Now.Year,$Now.Month,1)
$BeginningOfTheMonth

# Tip 2
$FirstDay = [datetime]::Today.AddDays(1 - [datetime]::Today.Day)
$FirstDay

End of the Month

$Today = [datetime]::Today
$EndOfTheMonth = [datetime]::new($Today.Year,$Today.Month,1).AddMonths(1).AddDays(-1)
$EndOfTheMonth

Cool! But in service now we don’t need to use this for retrieving incidents created on ‘THIS MONTH’. Yes, servicenow makes job easier using inbuilt javascript functions which is used in the sysparm_query like illustrated  below.

sysparm_query=sys_created_onONThis month@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()

Here is the full code!

try {
    $Uri = "https://dev42835.service-now.com/api/now/table/incident?sysparm_query=sys_created_onONThis month@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()"
    $admin = "admin"
    $password = "admin" | ConvertTo-SecureString -AsPlainText -Force
    $Credential = New-Object pscredential -ArgumentList ($admin , $password)
    $Result = Invoke-RestMethod -Uri $Uri -Method Get -Credential $Credential
    $Result.result | Select-Object Number , sys_created_on
}
catch {
    $_.Exception
}

Output

number     sys_created_on     
------     --------------     
INC0010008 2018-08-09 12:07:45
INC0010009 2018-08-10 12:32:46

This works for weekly reports and all readily available options. If in case you need to make custom dates you can very well adopt the below option

sysparm_query=sys_created_on>’2018-08-08%3223:59::59

In my next blog I will show custom queries using PowerShell until then enjoy PowerShell!