Yusuf Ozturk » Windows Powershell

Blogroll

Badges

Community

Kayako Classic to Freshdesk Migration via PowerShell

Posted in Windows Powershell | No Comment | 1,020 views | 26/10/2017 13:40

If you need to migrate from Kayako Classic to Freshdesk, you may use following script.
This is just an example script that i wrote in a few minutes.

First create connection to Kayako Classic via API information:

Add-Type -AssemblyName System.Web
$apiKey = "e5b3990a-5c6f-6634-2db3-6ff3f5d5036d";
$secretKey = "ZTBmNTIyNTItZTRhZC00ZDQ0LTY1MTktN2Q5MTFlYTUyNDdmNWZiOTRiNDQtZTNhZC0zOTE0LTBkZmQtMGZkMDY4YTRmMmY1";
$salt = [guid]::NewGuid().ToString();
$hmacsha = New-Object System.Security.Cryptography.HMACSHA256
$hmacsha.key = [Text.Encoding]::ASCII.GetBytes($secretKey)
$signature = $hmacsha.ComputeHash([Text.Encoding]::ASCII.GetBytes($salt))
$signature = [Convert]::ToBase64String($signature)
$encodedSignature = [System.Web.HttpUtility]::UrlEncode($signature)

Then get prepare for Freshdesk connection. Type your Freshdesk username and password:

$AllProtocols = [System.Net.SecurityProtocolType]'Ssl3,Tls,Tls11,Tls12'
[System.Net.ServicePointManager]::SecurityProtocol = $AllProtocols
$userpassB64 = [System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("test@domain.com:myPassword!"))

User Migration:

$userLink = "http://domain.kayako.com/api/Base/User/Filter&apikey=$apiKey&salt=$salt&signature=$signature"
[xml]$xml = (Invoke-WebRequest $userLink).Content
foreach ($topic in $xml.users.user) {
$contactJson = @{
	name = $topic.fullname.InnerText
	email = $topic.email.InnerText
}| ConvertTo-Json -Compress
Invoke-WebRequest -Headers @{Authorization = "Basic $userpassB64"} `
-Uri https://domain.freshdesk.com/api/v2/contacts `
-ContentType "application/json" -Method Post -Body $contactJson
}

Getting departments from Kayako:

$departmentLink = "http://domain.kayako.com/api/  `
Base/Department&apikey=$apiKey&salt=$salt&signature=$signature"
[xml]$xml = (Invoke-WebRequest $departmentLink).Content
foreach ($topic in $xml.departments.department) {
    write-host ID: $topic.id
    write-host Title: $topic.title.InnerText
    write-Host Type: $topic.type.InnerText
}

Getting tickets from Kayako:

$departmentId = 1
$ticketLink = "http://domain.kayako.com/api/Tickets/Ticket/ListAll/$departmentId/  `
1,2,3,4,5,6,7,8,9&apikey=$apiKey&salt=$salt&signature=$encodedSignature"
[xml]$xml = (Invoke-WebRequest $ticketLink).Content
foreach ($topic in $xml.tickets.ticket) {
    write-host ID: $topic.id
    write-host Subject: $topic.subject.InnerText
    write-host FullName: $topic.fullname.InnerText
    write-host Email: $topic.email.InnerText
    write-host Replies: $topic.replies.InnerText
}

Getting ticket posts from Kayako:

$ticketId = 518
$ticketPostLink = "http://domain.kayako.com/api/Tickets/TicketPost/ListAll/$ticketId `
&apikey=$apiKey&salt=$salt&signature=$encodedSignature"
[xml]$xml = (Invoke-WebRequest $ticketPostLink).Content
foreach ($topic in $xml.posts.post) {
    write-host ID: $topic.id.InnerText
    write-host FullName: $topic.fullname.InnerText
    write-host Contents: $topic.contents.InnerText
    write-host Email: $topic.email.InnerText
}

Getting ticket notes from Kayako:

$ticketId = 518
$noteLink = "http://domain.kayako.com/api/Tickets/TicketNote/ListAll/$ticketId `
&apikey=$apiKey&salt=$salt&signature=$encodedSignature"
[xml]$xml = (Invoke-WebRequest $noteLink).Content
foreach ($topic in $xml.notes.note) {
    write-host ID: $topic.id
}

Migrating tickets from Kayako to Freshdesk:

$departmentId = 1
$ticketLink = "http://domain.kayako.com/api/Tickets/Ticket/ListAll/$departmentId/ `
1,2,3,4,5,6,7,8,9&apikey=$apiKey&salt=$salt&signature=$encodedSignature"
[xml]$xml = (Invoke-WebRequest $ticketLink).Content
 
foreach ($topic in $xml.tickets.ticket) {
 
	$topicId = $topic.id
	$replyCount = 1;
	$ticketId = 0;
 
	$ticketPostLink = "http://domain.freshdesk.com/api/Tickets/TicketPost/ListAll/$topicId `
        &apikey=$apiKey&salt=$salt&signature=$encodedSignature"	
	[xml]$postXml = (Invoke-WebRequest $ticketPostLink).Content	
	$postIds = $postXml.posts.post.id.InnerText | Sort-Object
 
	foreach ($postId in $postIds)
	{
		$post = $postXml.posts.post | where {$_.id.InnerText -eq $postId}
		if ($replyCount -eq 1)
		{
			$ticketJson = @{
				description = $post.contents.InnerText
				subject = $topic.subject.InnerText
				email = $post.email.InnerText
				priority = 1
				status = 5
			}| ConvertTo-Json -Compress
			$ticketPost = Invoke-WebRequest -Headers @{Authorization = "Basic $userpassB64"} `
                        -Uri https://domain.freshdesk.com/api/v2/tickets  `
                        -ContentType "application/json" -Method Post -Body $ticketJson
			$ticketPostJson = $ticketPost.Content | ConvertFrom-Json
			$ticketId = $ticketPostJson.id
 
			$replyCount++
		}
		else
		{
			$ticketNoteJson = @{
				body = $post.contents.InnerText
				private = $false
			}| ConvertTo-Json -Compress
			Invoke-WebRequest -Headers @{Authorization = "Basic $userpassB64"} `
                        -Uri https://domain.freshdesk.com/api/v2/tickets/$ticketId/notes `
                        -ContentType "application/json" -Method Post -Body $ticketNoteJson
 
			$replyCount++
		}
	}
}

First of all, be aware of “status = 5”. I migrated all tickets in “closed” state. But if you have many open tickets in Kayako, you may set it as parameter so you can set them open/closed etc.

You might see that I added the old ticket replies as “public note” into Freshdesk. Reason of this, because Freshdesk API supports “TicketReply” method (this is a new method that they made it in 5 years i guess, according to their forum) but simply you can not say that, “do not send notification to customer”. So only way to do this is using “public note” method. In that case you can disable customer notifications.

To be honest, there are many lack of features in Freshdesk and i mean the core features, in API and in the software itself. Or these guys are really terrible at API side, or they just don’t give any effort to backend API. This was one of the most premature API i was dealing with after years, and this is in almost 2018 :)

If you need to see “TicketReply” method, here it is:

$ticketId = 25
$ticketReplyJson = @{
	body = "Details about the issue"
}| ConvertTo-Json -Compress
Invoke-WebRequest -Headers @{Authorization = "Basic $userpassB64"} `
-Uri https://domain.freshdesk.com/api/v2/tickets/$ticketId/reply  `
-ContentType "application/json" -Method Post -Body $ticketReplyJson

But be aware, this will send notification to customer because there is no option to disable notifications.

I hope this would a good guidance for you. This is very simple script meet with my requirements. Please test it before migrating real data.

Getting MSSQL Server Availability Replicas via PowerShell

Posted in Windows Powershell | No Comment | 2,124 views | 25/06/2016 17:17

You can get detailed info about SQL Server Availability Replicas via following code:

$MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
 
# Get SQL Availability Replicas
$HostSQLAvailabilityReplica = @($MSSQLServerManager.AvailabilityReplicas)[0]
 
# Get SQL Availability Group Replicas Properties
$HostSQLName = $HostSQLAvailabilityReplica.Name;
$HostSQLAvailabilityReplicaGuid = $HostSQLAvailabilityReplica.UniqueId;
$HostSQLRole = $HostSQLAvailabilityReplica.Role;
$HostSQLAvailabilityMode = $HostSQLAvailabilityReplica.AvailabilityMode;
$HostSQLConnectionModeInPrimaryRole = $HostSQLAvailabilityReplica.ConnectionModeInPrimaryRole;
$HostSQLConnectionModeInSecondaryRole = $HostSQLAvailabilityReplica.ConnectionModeInSecondaryRole;
$HostSQLEndpointUrl = $HostSQLAvailabilityReplica.EndpointUrl;
$HostSQLFailoverMode = $HostSQLAvailabilityReplica.FailoverMode;
$HostSQLJoinState = $HostSQLAvailabilityReplica.JoinState;
$HostSQLQuorumVoteCount = $HostSQLAvailabilityReplica.QuorumVoteCount;
$HostSQLSessionTimeout = $HostSQLAvailabilityReplica.SessionTimeout;
$HostSQLBackupPriority = $HostSQLAvailabilityReplica.BackupPriority;
$HostSQLCreateDate = $HostSQLAvailabilityReplica.CreateDate;
$HostSQLDateLastModified = $HostSQLAvailabilityReplica.DateLastModified;

You can also check other properties by listing all properties of $HostSQLAvailabilityReplica.

Getting MSSQL Server Database Replica States via PowerShell

Posted in Windows Powershell | No Comment | 2,349 views | 07/06/2016 00:12

You can get detailed info about SQL Server Database Replica States via following code:

$MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
 
# Get SQL Database Replica States
$HostSQLDatabaseReplicaState = @($MSSQLServerManager.DatabaseReplicaStates)[0]
 
# Get SQL Database Replica Properties
$HostSQLReplicaGuid = $HostSQLDatabaseReplicaState .AvailabilityReplicaId;
$HostSQLDatabaseID = $HostSQLDatabaseReplicaState .DatabaseID;
$HostSQLAvailabilityReplicaServerName = $HostSQLDatabaseReplicaState .AvailabilityReplicaServerName;
$HostSQLAvailabilityDatabaseName = $HostSQLDatabaseReplicaState .AvailabilityDatabaseName;
$HostSQLReplicaAvailabilityMode = $HostSQLDatabaseReplicaState .ReplicaAvailabilityMode;
$HostSQLReplicaRole = $HostSQLDatabaseReplicaState .ReplicaRole;

You can also check other properties by listing all properties of $HostSQLDatabaseReplicaState.

Getting MSSQL Server Availability Group Listeners via PowerShell

Posted in Windows Powershell | 1 Comment | 5,066 views | 11/05/2016 15:42

You can get detailed info about SQL Server Availability Group Listeners via following code:

$MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
 
# Get SQL Availability Group Listener
$HostSQLAvailabilityGroupListener = @($MSSQLServerManager.AvailabilityGroupListeners)[0]
 
# Get SQL Availability Group Listener Properties
$HostSQLName = $HostSQLAvailabilityGroupListener.Name;
$HostSQLAvailabilityGroupListenerGuid = $HostSQLAvailabilityGroupListener.UniqueId;
$HostSQLIPAddresses = $HostSQLAvailabilityGroupListener.AvailabilityGroupListenerIPAddresses;
$HostSQLPortNumber = $HostSQLAvailabilityGroupListener.PortNumber;

You can also check other properties by listing all properties of $HostSQLAvailabilityGroupListener.

Getting MSSQL Server Availability Groups via PowerShell

Posted in Windows Powershell | No Comment | 1,958 views | 05/05/2016 16:39

You can get detailed info about SQL Server Availability Groups via following code:

$MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
 
# Get SQL Availability Groups
$HostSQLAvailabilityGroup = @($MSSQLServerManager.AvailabilityGroups)[0]
 
# Get SQL Availability Groups Properties
$HostSQLAvailabilityGroupName = $HostSQLAvailabilityGroup.Name;
$HostSQLAvailabilityGroupID = $HostSQLAvailabilityGroup.Id;
$HostSQLAvailabilityGroupGuid = $HostSQLAvailabilityGroup.UniqueId;
$HostSQLLocalReplicaRole = $HostSQLAvailabilityGroup.LocalReplicaRole;
$HostSQLAutomatedBackupPreference = $HostSQLAvailabilityGroup.AutomatedBackupPreference;
$HostSQLFailureConditionLevel = $HostSQLAvailabilityGroup.FailureConditionLevel;
$HostSQLPrimaryReplicaServerName = $HostSQLAvailabilityGroup.PrimaryReplicaServerName;
$HostSQLHealthCheckTimeout = $HostSQLAvailabilityGroup.HealthCheckTimeout;

You can also check other properties by listing all properties of $HostSQLAvailabilityGroup.

Getting MSSQL Server Resource Pools via PowerShell

Posted in Windows Powershell | No Comment | 2,126 views | 24/04/2016 23:25

You can get detailed info about SQL Server Resource Pools via following code:

$MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
 
# Get SQL Resource Pools
$MSSQLResourcePool = @($MSSQLServerManager.ResourceGovernor.ResourcePools)[0]
 
# Get SQL Resource Pools Properties
$HostSQLResourcePoolName = $MSSQLResourcePool.Name;
$HostSQLMaximumCpuPercentage = $MSSQLResourcePool.MaximumCpuPercentage;
$HostSQLMaximumIopsPerVolume = $MSSQLResourcePool.MaximumIopsPerVolume;
$HostSQLMaximumMemoryPercentage = $MSSQLResourcePool.MaximumMemoryPercentage;
$HostSQLMinimumCpuPercentage = $MSSQLResourcePool.MinimumCpuPercentage;
$HostSQLMinimumIopsPerVolume = $MSSQLResourcePool.MinimumIopsPerVolume;
$HostSQLMinimumMemoryPercentage = $MSSQLResourcePool.MinimumMemoryPercentage;

You can also check other properties by listing all properties of $MSSQLResourcePool.

How to use jscript on PoSHServer? (PowerShell Web Server)

Posted in Windows Powershell | No Comment | 2,359 views | 13/04/2016 13:22

There is no jscript limitation on PoSHServer.

However you need to use escape characters to make it work.

If you need to use something like this:

$(function() { $.ajax({

Then you should change your codes like this:

`$(function() {`$.ajax({

` is a escape character in PowerShell.