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
This is a straightforward query that gets all unassigned tickets that are open:<\/p>\n
select <\/em> This does the following:<\/p>\n <\/p>\n Powershell code:<\/strong><\/p>\n ================================== #Set the source to your database #define your query #Execute the query and export results to CSV #Check if the query returns anything (i.e. if there are unassigned tickets.) if not, exit $HtmlTable1 =”” # Insert data into Table1 #Email variables $TodaysDate = (Get-Date).ToString(‘dd-MM-yyyy’)<\/p>\n #send the email =======================<\/p>\n <\/p>\n 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,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,9,3],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/298"}],"collection":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=298"}],"version-history":[{"count":4,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/298\/revisions"}],"predecessor-version":[{"id":305,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/298\/revisions\/305"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/media\/304"}],"wp:attachment":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=298"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\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>\nStep 2: Setup the PowerShell script<\/h2>\n
\n
\n#Import the PSSQLite module
\nImport-Module PSSQLite<\/p>\n
\n$DataSource = PAth\\to\\DAtabase”<\/p>\n
\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
\n$ReportResults = Invoke-SqliteQuery -DataSource $DataSource -Query $Query<\/p>\n
\nIf ($ReportResults -eq $null)
\n{
\nWrite-Host “No unassiagned tickets. Well done”
\nexit
\n}<\/p>\n
\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
\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
\n$To = @(“name@yourdomain”)
\n$From = “name@yourdomain”
\n$SMTPServer = “mailserver”<\/p>\n
\nSend-MailMessage -to $To -subject “Report: Unassigned Tickets $TodaysDate” -Body $HtmlTable1 -BodyAsHtml -SmtpServer $smtpserver -From $from<\/p>\nStep 3: Create A Schedule task to run the report<\/h2>\n
\n