Automate Zendesk Integration Tasks from PowerShell

The CData Cmdlets for Zendesk are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Zendesk.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Zendesk, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Zendesk data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Zendesk. To access Zendesk data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Zendesk.

Once you have acquired the necessary connection properties, accessing Zendesk data in PowerShell can be enabled in three steps.

Connecting to Zendesk

To connect, set the URL and provide authentication. The URL is your Zendesk Support URL: https://{subdomain}.zendesk.com.

Authenticating to Zendesk

You can authenticate using the Basic or OAuth methods.

Using Basic Authentication

To use Basic authentication, specify your email address and password or your email address and an API token. Set User to your email address and follow the steps below to provide the Password or ApiToken.

• Enable password access in the Zendesk Support admin interface at Admin > Channels > API.
• Manage API tokens in the Zendesk Support Admin interface at Admin > Channels > API. More than one token can be active at the same time. Deleting a token deactivates it permanently.

Using OAuth Authentication

See the Getting Started guide in the CData driver documentation for an authentication guide.

PowerShell

1. Install the module:

   Install-Module ZendeskCmdlets

2. Connect:

   $zendesk = Connect-Zendesk -URL "$URL" -User "$User" -Password "$Password" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $industry = "Floppy Disks"
   $tickets = Select-Zendesk -Connection $zendesk -Table "Tickets" -Where "Industry = `'$Industry`'"
   $tickets
   

   You can also use the Invoke-Zendesk cmdlet to execute SQL commands:

   $tickets = Invoke-Zendesk -Connection $zendesk -Query 'SELECT * FROM Tickets WHERE Industry = @Industry' -Params @{'@Industry'='Floppy Disks'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Zendesk\lib\System.Data.CData.Zendesk.dll")
    

2. Connect to Zendesk:

    
   $conn= New-Object System.Data.CData.Zendesk.ZendeskConnection("URL=https://subdomain.zendesk.com;[email protected];Password=test123;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

3. Instantiate the ZendeskDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Id, Subject from Tickets"
   
   $da= New-Object System.Data.CData.Zendesk.ZendeskDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.subject
   }
    

Update Zendesk Data

PowerShell

Update-Zendesk -Connection $Zendesk -Columns @('Id','Subject') -Values @('MyId', 'MySubject') -Table Tickets -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Zendesk.ZendeskCommand("UPDATE Tickets SET Industry='Floppy Disks' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Zendesk.ZendeskParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Zendesk Data

PowerShell

Add-Zendesk -Connection $Zendesk -Table Tickets -Columns @("Id", "Subject") -Values @("MyId", "MySubject") 

ADO.NET

$cmd = New-Object System.Data.CData.Zendesk.ZendeskCommand("INSERT INTO Tickets (Industry) VALUES (@myIndustry)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Zendesk.ZendeskParameter("@myIndustry","Floppy Disks")))
$cmd.ExecuteNonQuery()

Delete Zendesk Data

PowerShell

Remove-Zendesk -Connection $Zendesk -Table "Tickets" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Zendesk.ZendeskCommand("DELETE FROM Tickets WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Zendesk.ZendeskParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject