Generate-SpiceworksTicketReports.ps1 - A PowerShell to generate reports about tickets in Spiceworks via SQLite

Generate-SpiceworksTicketReports.ps1

#requires -version 3
<#
.SYNOPSIS
Generate-SpiceworksTicketReports.ps1 - Spiceworks open ticket reporting

.DESCRIPTION 
Reads the SpiceWorks database to generate emails about open tickets. Sends an email to each
operator with their open tickets.

Requires the SQLite PowerShell provider: https://psqlite.codeplex.com/

Directions on how to set it all up are on my blog. See the link below.

.OUTPUTS
A HTML email is sent to each operator

.LINK
http://daniel.streefkerkonline.com/reporting-on-spiceworks-tickets-via-sqlite-and-powershell/

.NOTES
Written By: Daniel Streefkerk
Website:	http://daniel.streefkerkonline.com
Twitter:	http://twitter.com/dstreefkerk
Todo:
    1. Make the "last updated" column's "x days ago" more readable with "yesterday", "today", etc
    2. Add Try/Catch error handling, especially at the Send-MailMessage part

Change Log
v2.0, 27/02/2014 - Rewrite to use PSObjects, added basic SLA support, split the email into different tables for high/medium/low priority tickets
v1.0, 12/02/2014 - Initial version
#>

Import-Module sqlite

#...................................
# Variables
#...................................

$spiceworksServerName = "helpdesk.example.com" # Used to build ticket URLs
$spiceworksServerPort = 9675 # Also used to build ticket URLs
$spiceworksServerProtocol = "http" # http or https - also used to build ticket URLs
$spiceworksDatabaseFilePath = "C:\Program Files (x86)\Spiceworks\db\spiceworks_prod.db" # Path to the SQLite database used by SpiceWorks

$settingWarningThresholdDays = 7 # User will be warned about tickets older than this number of days
$settingUseSMTPAuthentication = $false # Specifies whether or not SMTP auth is used. If this is set to $true, you need to specify values for $emailServerUsername and $emailServerPassword

#SLA Stuff
$settingsSLALowPriorityPickUpTime = 60 # In minutes, how soon a low-priority ticket must be picked up by an operator
$settingsSLALowPriorityFirstResponseTime = 2880 # In minutes, how soon a low-priority ticket must be responded to after it's assigned
$settingsSLAMediumPriorityPickUpTime = 60 # In minutes, how soon a medium-priority ticket must be picked up by an operator
$settingsSLAMediumPriorityFirstResponseTime = 1440 # In minutes, how soon a medium-priority ticket must be responded to after it's assigned
$settingsSLAHighPriorityPickUpTime = 60 # In minutes, how soon a high-priority ticket must be picked up by an operator
$settingsSLAHighPriorityFirstResponseTime = 120 # In minutes, how soon a high-priority ticket must be responded to after it's assigned

$emailSender = "[email protected]" # Email address that the report emails will originate from
$emailSenderName = "IT Helpdesk"
$emailServer = "smtp.example.com" # SMTP Server
$emailServerPort = 25 # SMTP Port
$emailServerUsername = $null # SMTP Auth username, if applicable
$emailServerPassword = $null # SMTP Auth password, if applicable

$now = Get-Date # Get the current date, for use in the emails
$time = $now.ToLongTimeString() # Long time string, for the footer of the email
$date = $now.ToLongDateString() # Long date string, for the footer of the email

#Build up some URL strings to access the tickets
$UrlBase = $spiceworksServerProtocol + "://" + $spiceworksServerName + ":" + $spiceworksServerPort
$ticketUrlBase = $UrlBase + "/tickets/list/single_ticket/"
$myTicketsURL = $UrlBase + "/tickets/list/my_tickets"

# Check if the root folder is a valid folder. If not, try again.
if ((Test-Path $spiceworksDatabaseFilePath -PathType Leaf) -eq $false) {
    Write-Host "'$spiceworksDatabaseFilePath' doesn't seem to exist. Please adjust the script variable spiceworksDatabaseFilePath to point to the correct location" -ForegroundColor Red
    break
}

#################################################################### HELPER FUNCTIONS ################################################################
# Return an array of ticket objects, given a table PSObject of tickets from the DB
function TicketsToObject ($tickets) {
    $returnValue = @()

    foreach ($ticket in $tickets) {
        # Figure out some time-based stats. NB: Ticks are a measure of time that make it easy to calculate the difference between two points in time
        $ticksOpen = $now.Subtract($ticket.created_at).Ticks
        $ticksSinceLastUpdate = $now.Subtract($ticket.updated_at).Ticks
        $hasBeenUpdatedSinceOpening = if ($ticksSinceLastUpdate -lt $ticksOpen) { $true } else { $false}

        # Start out true, and set to false if any broken SLA conditions are found
        $meetsSLA = $true
        $NotesForSLA = ""

        # Is this ticket assigned to anyone?
        if ([string]::IsNullOrEmpty($ticket.assigned_to)) {
            $isAssigned = "No"
        } else {
            $isAssigned = "Yes"
        }

        # Convert Spiceworks numeric priority to a string
        switch ($ticket.priority) {
            3 {
                $priority = "Low"
                $requiredSLAPickupTime = $settingsSLALowPriorityPickUpTime
                $requiredSLAResponseTime = $settingsSLALowPriorityFirstResponseTime
              }
            2 {
                $priority = "Medium"
                $requiredSLAPickupTime = $settingsSLAMediumPriorityPickUpTime
                $requiredSLAResponseTime = $settingsSLAMediumPriorityFirstResponseTime
              }
            1 {
                $priority = "High"
                $requiredSLAPickupTime = $settingsSLAHighPriorityPickUpTime
                $requiredSLAResponseTime = $settingsSLAHighPriorityFirstResponseTime
              }
        }

        # SLA Rules Here
        ####################

        # If the ticket hasn't been updated since opening, but it is assigned, check if the required response time has already expired
        if (($hasBeenUpdatedSinceOpening -eq $false) -and ($isAssigned -eq "Yes")) {
            $meetsSLA = if ([TimeSpan]::FromMinutes($requiredSLAResponseTime).Ticks > $ticksSinceLastUpdate) { $false } else { $true }
            $NotesForSLA += "Ticket hasn't had a first update within the required SLA response time of $requiredSLAResponseTime minutes. "
        }

        # If the ticket hasn't been assigned since it was opened, check if the required response time has already expired
        if ($isAssigned -eq "No") {
            $meetsSLA = if ([TimeSpan]::FromMinutes($requiredSLAPickupTime).Ticks -lt $ticksOpen) { $false } else { $true }
            $NotesForSLA += "Ticket hasn't been assigned within the required SLA response time of $requiredSLAPickupTime minutes. "
        }

        ####################

        # Build up our ticket PSObject
        $thisTicket = New-Object psobject

        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Ticket_Number" -Value $ticket.id
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Ticket_Summary" -Value ($ticket.summary -replace "\[.*\]","")
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Priority" -Value $priority
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Created" -Value $ticket.created_at
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Created_By" -Value ($users | Where-Object {$_.id -eq $ticket.created_by}).email
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Assigned_To" -Value ($users | Where-Object {$_.id -eq $ticket.assigned_to}).email
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Last_Updated" -Value "$($now.Subtract($ticket.updated_at).Days) days ago"
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Days_Open" -Value $now.Subtract($ticket.created_at).Days
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Due_At" -Value $ticket.due_at
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "URL" -Value ($ticketUrlBase + $ticket.id)
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Is_Assigned" -Value $isAssigned

        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Ticks_Open" -Value $ticksOpen
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Ticks_Since_Last_Update" -Value $ticksSinceLastUpdate
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Has_Been_Updated" -Value $hasBeenUpdatedSinceOpening

        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "Meets_SLA" -Value $meetsSLA
        $thisTicket | Add-Member -MemberType "NoteProperty" -Name "SLA_Notes" -Value $NotesForSLA

        $returnValue += $thisTicket
    }

    return $returnValue
}

# Convert a ticket PSObject array into a HTML table
function TicketObjectArrayToTable($ticketObject) {
    if ($ticketObject -eq $null) { return $null }

    $html = "<table><thead>"
    $html += "<tr>"
    $html += "<th>Ticket Number</th>"
    $html += "<th>Summary</th>"
    $html += "<th>Priority</th>"
    $html += "<th>Creator</th>"
    #$html += "<th>Assignee</th>"
    $html += "<th>Created</th>"
    $html += "<th>Days Open</th>"
    $html += "<th>Last Updated</th>"
    $html += "<th>Meets SLA?</th>"
    $html += "<th>Notes</th>"
    $html += "</tr></thead>"
    $html += "<tbody>"

    foreach ($ticket in $ticketObject) {

        # Add a CSS class to the row if the ticket on this row doesn't meet the SLA conditions
        switch ($ticket.Meets_SLA) {
            $true { $rowStartHTML = "<tr>" }
            $false { $rowStartHTML = "<tr class=""NotSLA"">" }
        }

        $html += $rowStartHTML
        $html += "<td>$($ticket.Ticket_Number)</td>"
        $html += "<td><a href=""$($ticket.URL)"">$($ticket.Ticket_Summary)</a></td>"
        $html += "<td>$($ticket.Priority)</td>"
        $html += "<td>$($ticket.Created_By)</td>"
        #$html += "<td>$($ticket.Assigned_To)</td>"
        $html += "<td>$($ticket.Created)</td>"
        $html += "<td>$($ticket.Days_Open)</td>"
        $html += "<td>$($ticket.Last_Updated)</td>"
        $html += "<td>$($ticket.Meets_SLA)</td>"
        $html += "<td>$($ticket.SLA_Notes)</td>"

        $html += "</tr>"

    }

    $html += "</tbody>"
    $html += "</table>"

    return $html
}

# Wrap whatever's passed into this function in HTML and HEAD tags with some CSS included
function WrapInHtmlPage($html) {
    # Set up some CSS
    $style = "<style>BODY{font-family: Arial; font-size: 10pt;}"
    $style += "TABLE{border: 1px solid black; border-collapse: collapse;}"
    $style += "TH{border: 1px solid black; background: #dddddd; padding: 5px; }"
    $style += "TD{border: 1px solid black; padding: 5px; }"
    $style += ".NotSLA { color: white; background: red;}"
    $style += "</style>"

    $newHTML = "<html>"
    $newHTML += $style

    $newHTML += $html

    $newHTML += "</html>"

    return $newHTML
}
# Mount the SpiceWorks database
mount-sqlite -name SpiceWorks -dataSource $spiceworksDatabaseFilePath

# Get all of the helpdesk operators from the database
$adminUsers = Invoke-Item spiceworks: -sql 'select * from users where role="admin" or role="helpdesk_tech" or role="helpdesk_admin"'

# Test in case there are no admin users in the database. Not likely, if even at all possible.
if ($adminUsers.Count -lt 1) { 
    Write-Host -ForegroundColor Red "No admin users found in the database"
    break 
}

$users = Invoke-Item spiceworks: -sql "select id,first_name,last_name,email,role from users"
$tickets = Invoke-Item spiceworks:  -sql "select * from tickets where status == 'open'"
$tickets = TicketsToObject($tickets)

# Get any unassigned tickets
[array]$unassignedTickets = $tickets | Where-Object {[string]::IsNullOrEmpty($_.Assigned_To)}

# Loop through each user and create the email report
foreach ($adminUser in $adminUsers) {
    # Grab a list of tickets that are assigned to this user
    [array]$thisUserTickets = $tickets | where-object {$_.Assigned_To -eq $adminUser.email}

    # Get tickets that are assigned to other users. Commented out as it clutters up the email report too much
    #[array]$otherUserTickets = $tickets | where-object {$_.Assigned_To -ne $adminUser.email}

    # Build up the user's full name for use later. Easier to do it once here.
    $userFullName = "$($adminUser.first_name) $($adminUser.last_name)"
    
    # If this user has no open tickets, skip and go to the next user
    if ($thisUserTickets.Count -eq 0) {
        Write-Host -ForegroundColor Magenta "No tickets were found for user: $userFullName. Skipping this user."
        continue
    }

    # Build a list of current user's high-priority tickets
    [array]$highPriorityTickets = $thisUserTickets | Where-Object {$_.Priority -eq "High"}

    # Build a list of current user's medium-priority tickets
    [array]$mediumPriorityTickets = $thisUserTickets | Where-Object {$_.Priority -eq "Medium"}

    # Build a list of current user's low-priority tickets
    [array]$lowPriorityTickets = $thisUserTickets | Where-Object {$_.Priority -eq "Low"}

    ########### Start building up the email body HTML ################
    $emailBody = "<h1>Daily Ticket Report for $userFullName - $($now.ToLongDateString())</h1>"

    # Add a table of unassigned tickets at the top if there are any
    if ($unassignedTickets -ne $null) {
        $emailBody += "<h2 color=""red"">Unassigned Tickets</h2>"
        $emailBody += TicketObjectArrayToTable($unassignedTickets)
    }

    # Add a table of high priority tickets if there are any
    if ($highPriorityTickets.Count -gt 0) {
        $emailBody += "<h2>High Priority</h2>"
        $emailBody += TicketObjectArrayToTable($highPriorityTickets)
    }

    # Add a table of medium priority tickets if there are any
    if ($mediumPriorityTickets.Count -gt 0) {
        $emailBody += "<h2>Medium Priority</h2>"
        $emailBody += TicketObjectArrayToTable($mediumPriorityTickets)
    }

    # Add a table of low priority tickets if there are any
    if ($lowPriorityTickets.Count -gt 0) {
        $emailBody += "<h2>Low Priority</h2>"
        $emailBody += TicketObjectArrayToTable($lowPriorityTickets)
    }

    # Add a table of tickets that are assigned to other users
    # ## uncomment line 99 if you want to also uncomment the lines below
    #if ($otherUserTickets.Count -gt 0) {
    #    $emailBody += "<h2>Tickets assigned to other users</h2>"
    #    $emailBody += TicketObjectArrayToTable($otherUserTickets)
    #}

    # Add a footer
    $emailBody += "<p>Report generated at $time on $date by $($MyInvocation.MyCommand.Name) on $($env:COMPUTERNAME)</p>"
    $emailBody += "<a href=""$myTicketsURL"">View tickets in Spiceworks</a>"

    # Wrap the email content that we just built up in HTML tags and add CSS style to the HEAD
    $emailContent = WrapInHtmlPage($emailBody)

    # Send the email
    Send-MailMessage -From "$emailSenderName <$emailSender>" -To $adminUser.email -Subject "Daily Helpdesk Ticket Report" -Body $emailContent -BodyAsHtml:$true -SmtpServer $emailServer -Port $emailServerPort
}

Remove-PSDrive SpiceWorks
Remove-Variable -Name tickets
Remove-Variable -Name adminUsers