Automate Freshdesk Integration Tasks from PowerShell

The CData Cmdlets for Freshdesk 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 Freshdesk.

PowerShell Cmdlets or ADO.NET Provider?

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

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

FreshDesk makes use of basic authentication. To connect to data, set the following connection properties:

• Domain: Set this to the domain associated with your FreshDesk account. For example, in your URL: https://my_domain.freshdesk.com.
• APIKey: Set this to the API key associated with your FreshDesk account. To retrieve your API key, Log into your support Portal -> Click on profile picture in the top right corner -> profile settings page. The API key will be available below the change password section to the right.

PowerShell

1. Install the module:

   Install-Module FreshDeskCmdlets

2. Connect:

   $freshdesk = Connect-FreshDesk -Domain "$Domain" -APIKey "$APIKey"
   

3. Search for and retrieve data:

   $status = "2"
   $tickets = Select-FreshDesk -Connection $freshdesk -Table "Tickets" -Where "Status = `'$Status`'"
   $tickets
   

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

   $tickets = Invoke-FreshDesk -Connection $freshdesk -Query 'SELECT * FROM Tickets WHERE Status = @Status' -Params @{'@Status'='2'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Freshdesk:

    
   $conn= New-Object System.Data.CData.FreshDesk.FreshDeskConnection("Domain=MyDomain;APIKey=myAPIKey;")
   $conn.Open()
   

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

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

Update Freshdesk Data

PowerShell

Update-FreshDesk -Connection $FreshDesk -Columns @('Id','Name') -Values @('MyId', 'MyName') -Table Tickets -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.FreshDesk.FreshDeskCommand("UPDATE Tickets SET Status='2' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.FreshDesk.FreshDeskParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Freshdesk Data

PowerShell

Add-FreshDesk -Connection $FreshDesk -Table Tickets -Columns @("Id", "Name") -Values @("MyId", "MyName") 

ADO.NET

$cmd = New-Object System.Data.CData.FreshDesk.FreshDeskCommand("INSERT INTO Tickets (Status) VALUES (@myStatus)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.FreshDesk.FreshDeskParameter("@myStatus","2")))
$cmd.ExecuteNonQuery()

Delete Freshdesk Data

PowerShell

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

ADO.NET

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