Backing up LiveAgent Tickets
Contents
A PowerShell script to backup LiveAgent tickets and conversation history into SQLite database.
Intro
There is currently no way to create a backup/export your data from LiveAgent.com’s cloud service. This PowerShell script pulls tickets using LiveAgent API key and writes/updates them in a SQLite database. This method allows you to have raw backup of your ticket history.
Script Overview
This script does not backup attachments. Only messages!
The script itself isnt doing much other than running loops to go through each ticket and the conversation messages. This can take a while, days even if you’re collecting historical data in your initial setup. I didn’t have it run anything in parallel to keep it simple and PowerShell v5 compatible. Bonus, the “slowness” keeps it under the API rate limit.
When you run it every day, it will skip over most conversations as the lookup is done for tickets modified in last day so it pulls all existing and new conversations inside the ticket but only adds new conversations to the database, skipping old ones.
Prerequisites
PSSQLITE module must be installed, the script prompts the install if it’s not found.
You’ll need to modify the following items in the script before you execute it.
$basePath&$dbPath: SQL file name and where you want to store it$baseTicketsURL&$baseMessagesURL: Point to your own helpdesk URL.$startDate&$endDate: Dates to apply in the filtering when searching for conversations.
Script
<#
Backup LiveAgent tickets that were modified in the last day.
If you're running historical data pull then change the filtering from date_changed to date_created.
Tickets that are already in the database will not be updated to prevent duplicates.
#>
$basePath = "C:\backup\liveagent" # Database path
$dbPath = Join-Path $basePath "tickets.sqlite" # Database name
$apiKey = "Your API Key HERE"
$baseTicketsUrl = "https://yourURL.ladesk.com/api/v3/tickets"
$baseMessagesUrl = "https://yourURL.ladesk.com/api/v3/tickets"
$headers = @{
"apikey" = $apiKey
"Content-type" = "application/json"
}
# Filtering days
$perPage = 500
$startDate = (Get-Date).AddDays(-1).Date # Yesterday at midnight
$endDate = (Get-Date).Date # Today at midnight
# Download pssqlite PowerShell module if it isn't installed already
if (Get-Module -ListAvailable -Name pssqlite)
{
Write-Host "pssqlite Powershell module exists. Continuing..." -ForegroundColor Green
}
else
{
Write-Host "pssqlite Powershell module does not exist. Installing it now." -ForegroundColor Yellow
install-module pssqlite -Force -Confirm:$false
}
Import-Module pssqlite
# Ensure DB path exists
if (-not (Test-Path $basePath))
{
New-Item -ItemType Directory -Path $basePath | Out-Null
}
# Create database if running first time
if (-not (Test-Path $dbPath))
{
Invoke-SqliteQuery -DataSource $dbPath -Query "CREATE TABLE IF NOT EXISTS init (id INTEGER PRIMARY KEY);"
}
# Create tables if they don't exist
$createTicketsTable = @"
CREATE TABLE IF NOT EXISTS Tickets (
TicketID TEXT PRIMARY KEY,
Subject TEXT,
DateCreated TEXT,
Status TEXT,
OwnerEmail TEXT
);
"@
$createMessagesTable = @"
CREATE TABLE IF NOT EXISTS Messages (
MessageID INTEGER PRIMARY KEY AUTOINCREMENT,
TicketID TEXT,
DateCreated TEXT,
Author TEXT,
Body TEXT,
ID TEXT,
UserID TEXT,
Type TEXT,
Format TEXT,
FOREIGN KEY(TicketID) REFERENCES Tickets(TicketID)
);
"@
Invoke-SqliteQuery -DataSource $dbPath -Query $createTicketsTable
Invoke-SqliteQuery -DataSource $dbPath -Query $createMessagesTable
# Loop through the date(s)
for ($d = $startDate; $d -le $endDate; $d = $d.AddDays(1))
{
$nextDay = $d.AddDays(1)
Write-Host "=== Processing date $($d.ToString('yyyy-MM-dd')) ==="
$page = 1
do
{
# Filtering
# https://support.liveagent.com/870644-Filtering-API-v3-GET-tickets-results
# Conversation status (Possible values: A - Answered, P - Calling, T - Chatting, X - Deleted, B - Spam, I - Init, C - Open, R - Resolved, N - New, W - Postponed)
<# # THIS $filters isnt backwards compatible with Powershell 5, only works in 7!
$filters = @(
@("date_changed", "D>=", $d.ToString("yyyy-MM-dd 00:00:00")),
@("date_changed", "D<", $nextDay.ToString("yyyy-MM-dd 00:00:00"))
#,@("status", "E", "R") # Status resolved only
) | ConvertTo-Json -Compress
#>
# This filter is built "manually" but works with both Powershell version 5 and 7.
$filters = '[["date_changed","D>=","{0}"],["date_changed","D<","{1}"]]' -f `
$d.ToString("yyyy-MM-dd 00:00:00"),
$nextDay.ToString("yyyy-MM-dd 00:00:00")
$encodedFilters = [uri]::EscapeDataString($filters)
$url = "$baseTicketsUrl`?_page=$page&_perPage=$perPage&_sortDir=ASC&_sortField=date_changed&_filters=$encodedFilters"
# Get tickets
$tickets = Invoke-RestMethod -Headers $headers -Uri $url -Method Get
if ($tickets)
{
foreach ($t in $tickets)
{
$ticketId = $t.code
Write-Host " Ticket $ticketId ($($t.subject))"
# Get all messages for the ticket
$msgUrl = "$baseMessagesUrl/$ticketId/messages?includeQuotedMessages=false&_perPage=100&_sortDir=ASC&_from=0&_to=0"
$messages = Invoke-RestMethod -Headers $headers -Uri $msgUrl -Method Get
# Get the first message datecreated
$dateCreated = ($messages | Select-Object -First 1 -ExpandProperty datecreated)
# Insert ticket into DB (Tickets table)
$queryTicket = @"
INSERT INTO Tickets (TicketID, Subject, DateCreated, Status, OwnerEmail)
VALUES (@TicketID, @Subject , @DateCreated, @Status, @OwnerEmail);
"@
$paramsTicket = @{
TicketID = $ticketId
Subject = $(($t.subject -replace "'", "''"))
DateCreated = $t.date_created
Status = $t.status
OwnerEmail = $t.owner_email
}
try
{
Invoke-SqliteQuery -DataSource $dbPath -Query $queryTicket -SqlParameters $paramsTicket -ErrorAction Stop
}
catch
{
$errorOutput = $_.Exception.Message
if ($errorOutput -like "*UNIQUE constraint failed*")
{
Write-Warning "Duplicate TicketID. Skipping insert."
}
else
{
Write-Error "SQLite error: $errorOutput"
}
}
# Insert messages
foreach ($m in $messages)
{
# Dig deeper into the array if more message IDs are found within the message.
if (($m.id) -gt 1)
{
foreach ($item in $m)
{
# Set values to blank if empty
$id = if ($item.PSObject.Properties['id']) { $item.id } else { "" }
$user_full_name = if ($item.PSObject.Properties['user_full_name']) { $item.user_full_name }else { "" }
$type = if ($item.PSObject.Properties['type']) { $item.type } else { "" }
$datecreated = if ($item.PSObject.Properties['datecreated']) { $item.datecreated } else { "" }
$message = if ($item.PSObject.Properties['messages']) { $item.messages.message } else { "" }
# Flatten body so multiline bodies dont cause bad output. Also get rid of HTML formatting.
if ($message)
{
$message = $message -join "`n" # flatten
$plainText = [System.Net.WebUtility]::HtmlDecode($message) # html to plaintext
$message = [regex]::Replace($plainText, '<[^>]+>', '')
}
# Add entry to 'Messages' table in the database
# Check if the ID already exists (avoid duplicates)
$checkQuery = "SELECT COUNT(*) FROM Messages WHERE ID = @ID;"
$exists = Invoke-SqliteQuery -DataSource $dbPath -Query $checkQuery -SqlParameters @{ ID = $id }
if ($exists.'COUNT(*)' -eq 0)
{
# Only insert if ID not found
$query = @"
INSERT INTO Messages (TicketID, DateCreated, UserID, Body, ID, Author, Type)
VALUES (@TicketID, @DateCreated, @UserID, @Body, @ID, @Author, @Type);
"@
$params = @{
TicketID = $ticketId
DateCreated = $item.datecreated
UserID = $item.userid
Body = $message
ID = $id
Author = $user_full_name
Type = $type
}
Invoke-SqliteQuery -DataSource $dbPath -Query $query -SqlParameters $params
}
else
{
Write-Host " - Skipping Messages table insert - duplicate ID $id"
}
}
}
else
{
# Add entry to 'Messages' table in the database
# Check if the ID already exists (avoid duplicates)
$checkQuery = "SELECT COUNT(*) AS Count FROM Messages WHERE ID = @ID;"
$checkParams = @{ ID = $m.messages.id }
$exists = Invoke-SqliteQuery -DataSource $dbPath -Query $checkQuery -SqlParameters $checkParams
if ($exists.Count -eq 0)
{
# Only insert if ID not found
$insertMessage = @"
INSERT INTO Messages (
TicketID, DateCreated, Author, Body, ID, UserID, Type
)
VALUES (
'$ticketId',
'$($m.datecreated)',
'$(($m.userid -replace "'", "''"))',
'$(($m.messages -replace "'", "''"))',
'$($m.messages.id)',
'$($m.messages.userid)',
'$($m.messages.type)'
);
"@
Invoke-SqliteQuery -DataSource $dbPath -Query $insertMessage
}
else
{
Write-Host " -- Skipping Messages table insert - duplicate ID $($m.messages.id)"
}
}
}
}
}
$count = ($tickets | Measure-Object).Count
$page++
} while ($count -eq $perPage)
}
write-host "End of script."