Created
December 5, 2014 05:54
-
-
Save dstreefkerk/d8ebafbabf735bc9002f to your computer and use it in GitHub Desktop.
Ticket List Widget update code, to be used with a List Widget: https://developer.geckoboard.com/#list
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment