Ticket List Widget update code, to be used with a List Widget: https://developer.geckoboard.com/#list

Spiceworks_Geckoboard-TicketList.ps1

$query = @"
select t.id as id,t.summary as summary,t.description as description,u.first_name || ' ' || u.last_name as name, round((julianday(date('now','localtime')) - julianday(t.created_at))) + 1 as days, t.category as category from tickets as t
inner join users as u on t.created_by = u.id
where status == 'open'
order by t.created_at desc
"@

$apikey = "paste API key here"
$geckoPushUrl = "paste push URL here"

# Get the list of tickets
$ticketList = Invoke-Item spiceworks: -sql $query

# Set up the holder for our JSON data that we're going to create
$dataJSON = ""

for ($i=0; $i -lt $ticketList.Count; $i++) {

$dayText = if ($ticketlist[$i].days -eq 1) { "Day" } else { "Days" }
$ticketTitle = $ticketlist[$i].summary
$ticketNumber = $ticketlist[$i].id
$ticketName = $ticketlist[$i].name
$ticketDaysOpen = $ticketlist[$i].days
$ticketCategory = $ticketlist[$i].category
    
$ticketSummary = ""

if ($ticketName -ne [DBNull]::Value) {
    $ticketSummary += "$ticketName | "
    }

$ticketSummary += "$ticketDaysOpen $dayText | $ticketCategory"

$tempJSON = @"
    {"title": {"text": "TITLEHERE"},"label": {"name": "LABELHERE","color": "#418CF0"},"description" : "SUMMARYHERE"},
"@

$tempJSON = $tempJSON.Replace("LABELHERE",$ticketNumber)
$tempJSON = $tempJSON.Replace("TITLEHERE",$ticketTitle)
$tempJSON = $tempJSON.Replace("SUMMARYHERE",$ticketSummary)

$dataJSON += $tempJSON
}

#Construct a here-string with the required json format
$jsonstream = @"
{
  "api_key":"APIKEY",
  "data": {
     "item" : 
      [ 
        DATAHERE
      ]
  }
}
"@

$jsonstream = $jsonstream.Replace("DATAHERE",$($dataJSON.TrimEnd(",")))
$jsonstream = $jsonstream.Replace("APIKEY",$apikey)
 
#The line of code will only work in PowerShell V3
#Invoke-WebRequest -Method Post -Body $jsonstream -Uri $geckoPushUrl
 
#This should work in V2 also
$client = new-object system.net.webclient
$client.UploadString($geckoPushUrl,$jsonstream)