Ich sollte eine SQL Query in ein Ruby script packen

Hallo zusammen,

wir haben diese SQL Query. Weiter oben wird noch TOP 100 abgefragt.
Es geht um den Where Befehl

FROM [gugus_transaction].[gugus_transactions].[T_gugus_NOTIFIER]
WHERE (
        (gugusnotifierstatus = 'NEW')  
        AND (created < DATEADD(hh, - 1, GETDATE()))
        )
    OR (
        (gugusnotifierstatus = 'PROCESSING')  
        AND (created < DATEADD(hh, - 1, GETDATE()))
        )
    OR (
        (gugusnotifierstatus = 'ERROR')  
        AND (created < DATEADD(hh, - 1, GETDATE()))

Nun habe ich ein Rubyscript, das es für SQL für was anderes gab mal angepasst- Weiss jemand wie ich es richtig anpasse. Auch so, das die Zeiten drinnen sind. Alles älter als 1 Stunde wird abgefragt. Wie im SQL...

#!/usr/bin/env ruby
require 'awesome_print'  
require 'optparse'  
def banner
  puts 'USAGE: check_error_docs -h HOST -w WARN -c CRIT -u USER -p PASS -d DB'  
end
def add_switches_get_options!(parser)
  options = {}
  parser.on('-h', '--help', 'Display usage info') { options[:help] = true }  
  parser.on('-h HOST', '--host=HOST', 'Perform check at db server HOST, default is ') { |h| options[:host] = h }  
  parser.on('-u USER', '--username=USER', 'Username used to auth with database, ') { |u| options[:user] = u }  
  parser.on('-p PASS', '--password=PASS', 'Password used to auth with databse, default password for ') { |p| options[:pass] = p }  
  parser.on('-d DB', '--database=DB', 'Database to query, default is ') { |d| options[:db] = d }  
  parser.on('-l legal timeout', '--legaltimeout=LEGALTIMEOUT', 'How long it is legal that the tasks is locked, default is 1 (hour)') { |l| options[:leagaltimeout] = l }  
  options
end
def switch_please
  optparser = OptionParser.new
  options = add_switches_get_options!(optparser)
  optparser.parse!
rescue OptionParser::InvalidOption
  puts optparser
  exit 1
else
  [optparser, options]
end
(_parser, options) = switch_please
options[:host] ||= '10.99.99.99'  
options[:user] ||= 'gugus_Report'  
options[:pass] ||= 'UnsercoolesPW'  
options[:db]   ||= 'gugustransaction'  
options[:leagaltimeout]   ||= $1
failed_1our = `/opt/mssql-tools/bin/sqlcmd -s '|' -h -1 -W -w 999 -S #{options[:host]} -U #{options[:user]} -P #{options[:pass]} -H #{options[:host]} -d #{options[:db]} -Q "set nocount on;SELECT ID FROM T_DELIVERY_NOTIFIER WHERE deliverynotifierstatus  = 'NEW' OR `PROCESSING" OR `ERROR`LASTUPDATE < DATEADD(hour, -#{options[:leagaltimeout]}, GETDATE())" `.split  
if failed_1our .length < 1
  puts "OK: failed_1our =#{failed_1our .length}"  
  exit 0

Abgefragt werden soll eine Tabelle gugus_transaction mit table gugus_transaction. Eine Spalte hat da die 3 Staten.

Gruss
Jonas

Please also mark the comments that contributed to the solution of the article

Content-Key: 667514

Url: https://administrator.de/contentid/667514

Printed on: April 19, 2024 at 08:04 o'clock

6 Comments

Latest comment

Moin,

Zeile 31 wäre dein Query einzusetzen.
Vorher vielleicht als Variable erstellen und diese dann in Zeile 31 einfügen.

Gruß
em-pie

Hallo,

vielen Dank.

Gruss
Joans

wo könnte man so ein Script in Auftrag geben ?

Hallo,

es sieht nun so auf. Geht aber nicht

#!/usr/bin/env ruby
require 'awesome_print'  
require 'optparse'  
def banner
  puts 'USAGE: check_error_docs -h HOST -w WARN -c CRIT -u USER -p PASS -d DB'  
end
def add_switches_get_options!(parser)
  options = {}
  parser.on('-h', '--help', 'Display usage info') { options[:help] = true }  
  parser.on('-h HOST', '--host=HOST', 'Perform check at db server HOST, default is epr-int-db') { |h| options[:host] = h }  
  parser.on('-u USER', '--username=USER', 'Username used to auth with database, default cxtREL_wm_tn') { |u| options[:user] = u }  
  parser.on('-p PASS', '--password=PASS', 'Password used to auth with databse, default password for cxtREL_wm_tn') { |p| options[:pass] = p }  
  parser.on('-d DB', '--database=DB', 'Database to query, default is cxtREL_wm_tn') { |d| options[:db] = d }  
  parser.on('-l legal timeout', '--legaltimeout=LEGALTIMEOUT', 'How long it is legal that the tasks is locked, default is 1 (hour)') { |l| options[:leagaltimeout] = l }  
  options
end
def switch_please
  optparser = OptionParser.new
  options = add_switches_get_options!(optparser)
  optparser.parse!
rescue OptionParser::InvalidOption
  puts optparser
  exit 1
else
  [optparser, options]
end
(_parser, options) = switch_please
options[:host] ||= '10.99.99.99'  
options[:user] ||= 'gugus_Report'  
options[:pass] ||= 'UnsercoolesPW'  
options[:db]   ||= 'gugustransaction'  
options[:leagaltimeout]   ||= $1
failed_1our = `/opt/mssql-tools/bin/sqlcmd -s '|' -h -1 -W -w 999 -S #{options[:host]} -U #{options[:user]} -P #{options[:pass]} -H #{options[:host]} -d #{options[:db]} -Q "set nocount on;SELECT ID FROM T_DELIVERY_NOTIFIER WHERE deliverynotifierstatus  = 'NEW' OR `PROCESSING" OR `ERROR`LASTUPDATE < DATEADD(hour, -#{options[:leagaltimeout]}, GETDATE())" `.split  
if locked_tasks.length < 1
  puts "OK: failed_1our =#{failed_1our .length}"  
  exit 0

Gruss
Jonas

Wie oft noch: du musst dein Statement in die Zeile 33 einbinden.
Da steht doch genau drin, welches Statement ausgeführt werden soll!

SELECT ID FROM T_DELIVERY_NOTIFIER WHERE deliverynotifierstatus  = 'NEW' OR `PROCESSING" OR `ERROR`LASTUPDATE < DATEADD(hour, -#{options[:leagaltimeout]}, GETDATE())" `.split  

Das einfach mit einem obigen ersetzen.

DAnke

Gruss
Jonas

German Question Development

Hotly discussed

How to set up and configure a Linux GRE tunnelAlexWisha - 3 Comments

WIREGUARD VPN ON UDM PRO BEHIND FRITZBOX - HANDSHAKE DID NOT COMPLETEjstricker - 2 Comments

Check of ZFW Firewallgleixnerd - 1 Comment

End of Support dates for Office 2016, 2019 Apps und Productivity ServersDani - 1 Comment