{"id":298,"date":"2020-05-19T10:25:14","date_gmt":"2020-05-19T10:25:14","guid":{"rendered":"http:\/\/www.spktechfit.com\/?p=298"},"modified":"2020-05-19T10:39:53","modified_gmt":"2020-05-19T10:39:53","slug":"spiceworks-unassigned-tickets-report-with-powershell","status":"publish","type":"post","link":"https:\/\/www.spktechfit.com\/?p=298","title":{"rendered":"Spiceworks: Unassigned tickets report with PowerShell"},"content":{"rendered":"

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.<\/p>\n

Step 1: Create the SQL Query<\/h2>\n

This is a straightforward query that gets all unassigned tickets that are open:<\/p>\n

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

Step 2: Setup the PowerShell script<\/h2>\n

This does the following:<\/p>\n

    \n
  1. Import the PSSQLite module to query the database<\/li>\n
  2. Execute the query<\/li>\n
  3. Build the HTML email using the results found<\/li>\n
  4. Send the email with contents in an HTML table<\/li>\n<\/ol>\n

     <\/p>\n

    Powershell code:<\/strong><\/p>\n

    ==================================
    \n#Import the PSSQLite module
    \nImport-Module PSSQLite<\/p>\n

    #Set the source to your database
    \n$DataSource = PAth\\to\\DAtabase”<\/p>\n

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

    #Execute the query and export results to CSV
    \n$ReportResults = Invoke-SqliteQuery -DataSource $DataSource -Query $Query<\/p>\n

    #Check if the query returns anything (i.e. if there are unassigned tickets.) if not, exit
    \nIf ($ReportResults -eq $null)
    \n{
    \nWrite-Host “No unassiagned tickets. Well done”
    \nexit
    \n}<\/p>\n

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

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

    #Email variables
    \n$To = @(“name@yourdomain”)
    \n$From = “name@yourdomain”
    \n$SMTPServer = “mailserver”<\/p>\n

    $TodaysDate = (Get-Date).ToString(‘dd-MM-yyyy’)<\/p>\n

    #send the email
    \nSend-MailMessage -to $To -subject “Report: Unassigned Tickets $TodaysDate” -Body $HtmlTable1 -BodyAsHtml -SmtpServer $smtpserver -From $from<\/p>\n

    =======================<\/p>\n

     <\/p>\n

    Step 3: Create A Schedule task to run the report<\/h2>\n
      \n
    1. Create a “Basic Scheduled task”<\/li>\n
    2. Set your scheduled time<\/li>\n
    3. Set the Action as “Start a program”<\/li>\n
    4. set the “Program\/Script” as: Powershell.exe<\/li>\n
    5. Add the arguments: -file “\\\\Server\\Directory\\UnassignedTickets.ps1”<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

      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 […]<\/p>\n","protected":false},"author":1,"featured_media":304,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[8,9,3],"tags":[],"class_list":["post-298","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-powershell","category-spiceworks","category-it"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t