gistfile1.txt

require_relative 'my_db.rb'

SCHEDULER.every '30s', :first_in => 0 do |job|

  db = MyDb.conn

  # Mysql query
  sql = "SELECT REPLACE(HotFixData.Severity,\' \', \'Optional\') AS `name`, COUNT(computers.computerid) AS `value` FROM ((Hotfix JOIN Hotfixdata ON HotFixData.HotFixID=HotFix.HotFixID) LEFT JOIN Computers ON Computers.ComputerID=HotFix.ComputerID) WHERE (Hotfix.Installed=0 AND HotFix.Approved=1) AND HotFixData.Severity IN (\'Critical\', \'Important\', \'low\', \' \',\'moderate\') AND Computers.ClientID=(1 OR 1=0) AND Computers.OS LIKE \'%server%\' AND computers.computerid NOT IN (SELECT computerid FROM v_extradatacomputers WHERE `exclude from dashboard` LIKE \'1\')GROUP BY Severity"

  # Execute the query
  results = db.query(sql)

  # Sending to List widget, so map to :label and :value
  acctitems = results.each do |row|
    row =  { label: row['name'], value: row['value'].to_i }

  end

  # Update the List widget
  send_event('missingpatches', { value: acctitems } )

db.close
end