I’ve found myself at a new job, recreating many of the processes that I spent the last few years putting together, tweaking, modifying, building a new managed services provider with an exciting new company. One of those challenges lead me to Email parsing and ConnectWise (CW). Previously I had the opportunity to use Autotask and email2ticket, however with the modifications to the API that CW did a few years ago, email2ticket is no longer supported for CW. With my found love of Azure Functions and looking at existing mail based parsing tools (https://mailparser.io/, https://www.thinkautomation.com/) which have amazing feature sets, they didn’t do quite what I was looking for and to replace the functionality that I once had with email2ticket.

That is leading me to this PowerShell series for how to utilize the CW REST API and the things that took some understanding and digging in a little to determine how to do simple queries. The CW developer portal has some great resources, and I stumbled upon the forums that ultimately made it possible to finally build a wildcard query via PowerShell to identify whether a contact exists in CW.

First things first, you have to authenticate to the CW REST API. This requires you to generate a CW API Access Account and you need access to the Admin Setup tables to do so.

CW Members Tab – API Members

Once you have an integration setup you can proceed with creating a PowerShell script to handle the automation. I build in Azure Functions mostly, so there will be some pieces in here that relate to that, I’ll breakdown each section (and eventually move some of these pieces to linked articles).

Authentication

Authentication to the CW rest API is fairly simple. It requires your pubilc and private keys and your company identifier.

First set your variables for your credentials. 

$global:CWcompany    = "company"
$global:CWprivate    = "privatekey"
$global:CWpublic     = "publickey"
$global:CWserver     = "https://api-na.myconnectwise.net"

Second configure the authentication string and setup the standard headers for your GET request. 

[string]$Accept      = "Accept: application/vnd.connectwise.com+json; version=3.0"
[string]$ContentType = 'application/json'
[string]$Authstring  = $CWcompany + '+' + $CWpublic + ':' + $CWprivate
$encodedAuth         = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(($Authstring)));

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Authorization", "Basic $encodedAuth")
$headers.Add("Content-Type", 'application/json')
$headers.Add("Accept", $Accept)

Then for the URI, this consists of a few parts, your base URL, your query parameters, and your target for your request.

Query String – this is the conditions you’re going to pass to do the lookup. In this case I’m looking for the email address of a contact. This requires the use of CW’s childconditions parameters. Initially the communicationItems is used to determine the value of the email address, then forcing it to only look for the type of email address to speed the query result. The “%” is used as the wildcard for the “like” operator. I spent some time trying to determine what would work best, contains and in both resulted in invalid syntax so using the like operator was the eventual conclusion. 

[string]$query       = '?childconditions=communicationItems/value like "%' + $email + '%" AND communicationItems/communicationType="Email"'

Putting it all together, you have a target of /company/contacts and use the query string. $email is the full or partial email address you’re searching for. Benefits of using a partial, such as @xyz.corp, would yeild all contacts for that domain and you can use some logic there to determine what company they belong to (more to come on that subject). 

[string]$TargetUri   = '/company/contacts'
[string]$query       = '?childconditions=communicationItems/value like "%' + $email + '%" AND communicationItems/communicationType="Email"'
[string]$BaseUri     = "$CWserver" + "/v4_6_release/apis/3.0" + $TargetUri + $query

Finally, send the Invoke-RestMethod command to get the results. This returns a JSON table that Invoke-RestMethod converts to a PS Object. 

$JSONResponse = Invoke-RestMethod -URI $BaseURI -Headers $Headers -ContentType $ContentType -Method Get

Here’s the full code that I’m using to query for a specific contact’s email address. 

# GET method: each querystring parameter is its own variable
if ($req_query_email) 
{
    $email = $req_query_email
}

###INITIALIZATIONS###
$global:CWcompany    = "company"
$global:CWprivate    = "privatekey"
$global:CWpublic     = "publickey"
$global:CWserver     = "https://api-na.myconnectwise.net"

###CW AUTH STRING###
[string]$Accept      = "Accept: application/vnd.connectwise.com+json; version=3.0"
[string]$Authstring  = $CWcompany + '+' + $CWpublic + ':' + $CWprivate
[string]$ContentType = 'application/json'
$encodedAuth         = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(($Authstring)));

###CW HEADERS###
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Authorization", "Basic $encodedAuth")
$headers.Add("Content-Type", 'application/json')
$headers.Add("Accept", $Accept)

###CW QUERY###
[string]$TargetUri   = '/company/contacts'
[string]$query       = '?childconditions=communicationItems/value like "%' + $email + '%" AND communicationItems/communicationType="Email"'
[string]$BaseUri     = "$CWserver" + "/v4_6_release/apis/3.0" + $TargetUri + $query

###GET RESPONSE###
$JSONResponse = Invoke-RestMethod -URI $BaseURI -Headers $Headers -ContentType $ContentType -Method Get

###PARSE CONTACT INFO TO USABLE SHORT TABLE###
$contactInfo = @()
foreach($contact in $JSONResponse){
    $email = $null; $emails = $null
	$obj = New-Object PSObject
	$obj | Add-Member -MemberType NoteProperty -Name "id" -Value $contact.id
	$obj | Add-Member -MemberType NoteProperty -Name "firstName" -Value $contact.firstName
	$obj | Add-Member -MemberType NoteProperty -Name "lastName" -Value $contact.lastName
    foreach ($commtype in $contact.communicationItems) {
        $email = $($commtype | Where-Object {$_.communicationType -eq "Email"}).value
        if ($email.length -gt 2) {$emails += $email + ";"}
    }
    $obj | Add-Member -MemberType NoteProperty -Name "emails" -Value $emails
    $obj | Add-Member -MemberType NoteProperty -Name "company" -Value $contact.company.name
    $obj | Add-Member -MemberType NoteProperty -Name "companyid" -Value $contact.company.id
    $obj | Add-Member -MemberType NoteProperty -Name "companyidentifier" -Value $contact.company.identifier
	$contactInfo += $obj
}

If($contactInfo)
{
    Out-File -Encoding Ascii -FilePath $res -inputObject $($contactInfo | ConvertTo-Json)
}

Else
{
    Return $False
}