Spiceworks: Unassigned tickets report with PowerShell

An “Unassigned Tickets” report is very useful for making sure everyday that all tickets are being picked up and not left sitting unassigned. Spiceworks has the built in report functionality which are great, but the only issue I have with them is that you cant put report content directly in an email, it sends it as an attachment. So I created my own report using PowerShell.

Step 1: Create the SQL Query

This is a straightforward query that gets all unassigned tickets that are open:

select
tickets.id as ‘Ticketid’
,’http://helpdesk.stbedes.catholic.edu.au/tickets/v2#single_ticket/’||tickets.id AS ‘TicketLink’
, (select email as ‘Created By’ from users where tickets.created_by=users.id) AS ‘CreatedBy’
, summary AS Summary
, round(julianday(‘now’) – julianday(tickets.created_at),2) AS ‘DaysOpen’
from tickets
WHERE assigned_to is null AND Status = ‘open’

Step 2: Setup the PowerShell script

This does the following:

1. Import the PSSQLite module to query the database
2. Execute the query
3. Build the HTML email using the results found
4. Send the email with contents in an HTML table

Powershell code:

==================================
#Import the PSSQLite module
Import-Module PSSQLite

#Set the source to your database
$DataSource = PAth\to\DAtabase”

#define your query
$Query = “select
tickets.id as ‘Ticketid’
,’http://SpiceworksServer/tickets/v2#single_ticket/’||tickets.id AS ‘TicketLink’
, (select email as ‘Created By’ from users where tickets.created_by=users.id) AS ‘CreatedBy’
, summary AS Summary
, round(julianday(‘now’) – julianday(tickets.created_at),2) AS ‘DaysOpen’
from tickets
WHERE assigned_to is null AND Status = ‘open'”

#Execute the query and export results to CSV
$ReportResults = Invoke-SqliteQuery -DataSource $DataSource -Query $Query

#Check if the query returns anything (i.e. if there are unassigned tickets.) if not, exit
If ($ReportResults -eq $null)
{
Write-Host “No unassiagned tickets. Well done”
exit
}

$HtmlTable1 =””
# Create column headers of Table1
$HtmlTable1 = “<table border=’1′ align=’Left’ cellpadding=’2′ cellspacing=’0′ style=’color:black;font-family:arial,helvetica,sans-serif;text-align:left;’>
<tr style =’font-size:13px;font-weight: normal;background: #FFFFFF’>
<th align=left><b>Ticket #</b></th>
<th align=left><b>Summary</b></th>
<th align=left><b>Created By</b></th>
<th align=left><b>Days Open</b></th>
</tr>”

# Insert data into Table1
foreach ($row in $ReportResults)
{
$HtmlTable1 += “<tr style=’font-size:13px;background-color:#FFFFFF’>
<td><a href='” + $row.’ticketlink’ + “‘>” + $row.’ticketid’ + “</a></td>
<td>” + $row.’summary’ + “</td>
<td>” + $row.’CreatedBy’ + “</td>
<td>” + $row.’DaysOpen’ + “</td>
</tr>”
}
$HtmlTable1 += “</table>”

#Email variables
$To = @(“name@yourdomain”)
$From = “name@yourdomain”
$SMTPServer = “mailserver”

$TodaysDate = (Get-Date).ToString(‘dd-MM-yyyy’)

#send the email
Send-MailMessage -to $To -subject “Report: Unassigned Tickets $TodaysDate” -Body $HtmlTable1 -BodyAsHtml -SmtpServer $smtpserver -From $from

=======================

Step 3: Create A Schedule task to run the report

1. Create a “Basic Scheduled task”
2. Set your scheduled time
3. Set the Action as “Start a program”
4. set the “Program/Script” as: Powershell.exe
5. Add the arguments: -file “\\Server\Directory\UnassignedTickets.ps1”