Dashing widget that shows KACE K1000 service desk open ticket count

README.md

##Preview

##Description Shows open ticket count from the KACE K1000 service desk. You can set up teams based on KACE appliance usernames and display a widget per team, or you can display a global count. Or both!

##Dependencies

• libmysqlclient package
• mysql2 gem

#on Debian/Ubuntu

sudo apt-get install libmysqlclient
gem install mysql2

##Usage To install

dashing install 9633826

To add to dashboard

<ul>
  <!-- When using teams, set data-id to "tickets_{my team name}" -->
  <li data-row="1" data-col="1" data-sizex="1" data-sizey="1">
    <div data-id="tickets_linux" data-view="Ticketcount" data-title="Linux Tickets"></div>
  </li>
  <li data-row="1" data-col="1" data-sizex="1" data-sizey="1">
    <div data-id="tickets_windows" data-view="Ticketcount" data-title="Windows Tickets"></div>
  </li>
  <li data-row="1" data-col="1" data-sizex="1" data-sizey="1">
    <div data-id="tickets_dba" data-view="Ticketcount" data-title="DBA Tickets"></div>
  </li>
  
  <!-- The default team is "All" -->
  <li data-row="1" data-col="1" data-sizex="1" data-sizey="1">
    <div data-id="tickets_all" data-view="Ticketcount" data-title="All Tickets"></div>
  </li>
</ul>

##Settings Edit jobs/ticketcount.rb to define DB connection params like hostname, password etc. See here for K1000 connection details.

Also you define teams in ticketcount.rb, see the comments in that file for more details.

ticketcount.coffee

class Dashing.Ticketcount extends Dashing.Widget
  onData: (data) ->
    if data.status == "failed"
      console.log(data)

ticketcount.html

<h1 class="title" data-bind="data.name | append ' Tickets'"></h1>

<div class="upper">
    <h2 class="value" data-bind="data.count"></h2>
    <p class="subtitle">open</p>
</div>

<p class="updated-at" data-bind="updatedAtMessage"></p>

ticketcount.rb

require 'mysql2'

# DB Connection
# -------------
# See http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL114992 for more details.

user = 'R1'
password = 'box747'
host = 'myK1000.mydomain.local'
database = 'ORG1'

# Team Definitions
# ----------------
# Define teams based on Kace usernames. Then you can create individual widgets per team by
# setting data-id="tickets_myTeamName" in the dashboard. See the gist for a dashboard example.
#
# After you define a team, make sure to add it to team_list. The default list
# always has to be defined.
# 
# E.g.:
#
# dba = {
#     "name" => "DBA", 
#     "count" => 0, 
#     "members" => ['moose', 'midge', 'ethel'], 
# }
# windows = {
#     "name" => "Windows", 
#     "count" => 0, 
#     "members" => ['reggie', 'veronica', 'weatherbee'], 
# }
# linux = {
#     "name" => "Linux", 
#     "count" => 0, 
#     "members" => ['archie', 'jughead', 'betty'], 
# }
# default = {
#    "name" => "All", 
#    "count" => 0, 
# }
# team_list = [dba, windows, linux, default]

default = {
   "name" => "All", 
   "count" => 0, 
}
team_list = [default]

# SQL query
# ---------
# You can modify this to get more information if you want fancier widgets.

query="
    select HD_TICKET.ID, 
    CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
        TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
        TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ', 
        DATE_FORMAT(ADDTIME('2000-01-01 00:00:00',
        SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
        '%kh %im')) AS TIME_OPEN,
    HD_CATEGORY.NAME as CATEGORY, 
    HD_STATUS.NAME as STATUS,
    CREATED,
    ifnull((select USER_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME
    from HD_TICKET
    left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
    left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID 
        where HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened'
    order by OWNER_NAME, CREATED, HD_TICKET.ID
"

# Engage
# ------

SCHEDULER.every '1m', :first_in => 0 do |job|
    # Reset counters
    team_list.each do |team|
        team["count"] = 0
    end 

    # fetch data
    begin
        conn = Mysql2::Client.new(
            :host=> host, 
            :database => database, 
            :username => user, 
            :password => password
        )
        results = conn.query(query)
        if results
            results.each do |row|
                team_list.each do |team|
                    if team["name"] == "All"
                        team["count"] += 1
                    else
                        if team["members"].include?(row["OWNER_NAME"].downcase)
                            team["count"] += 1
                        end
                    end
                end
            end
        end
    rescue Exception => ex
    ensure
        conn.close if conn
    end

    #send data
    if ex.nil?
        team_list.each do |team|
            send_event(
                "tickets_#{team["name"].downcase}", 
                { status: "success", data: team }
            )
        end
    else
        team_list.each do |team|
            send_event(
                "tickets_#{team["name"].downcase}", 
                { status: "failed", errorclass: ex.class, errormsg: ex.message }
            )
        end
    end
end

ticketcount.scss

// ----------------------------------------------------------------------------
// Sass declarations
// ----------------------------------------------------------------------------
$background-color:  #47bbb3;
$value-color:       #fff;

$title-color:       rgba(255, 255, 255, 0.7);
$updated-color:     rgba(0, 0, 0, 0.3);

// ----------------------------------------------------------------------------
// Widget-number styles
// ----------------------------------------------------------------------------
.widget-ticketcount {

  background-color: $background-color;

  .title {
    color: $title-color;
  }

  .value {
    color: $value-color;
  }

  .subtitle {
    font-size: 25px;
    color: $value-color;
  }

  .updated-at {
    color: $updated-color;
  }

}

Could you make this as a repo so that I can clone and modify for own use?