Blog Archives
Reading ServersCheck Status page from command line – Part 3.
In the final, part 3 of the article, I’ll show you how we’re pulling from ServersCheck sensor the current temperature and relative humidity in our data center with simple SQL statement, like this:
If you recall, in Part 1 of the article I published Go “script” that could be compiled on all supported Go platforms, including Linux.
We decided that we’ll be using one of our existing Oracle11g XE instance running on Oracle Linux 6.4 (x64) to host our “vpctemp” application written in go. On this Linux server we already had the latest Go compiler installed (go 1.1.2 at the time of this writing), but we could as easily compile the vpctemp.go on some other box and then simply copy executable to our production Oracle Linux server — not worrying about some potential missing library, thanks to “go” statically compiled nature.
We copied vpctemp binary to /oracle/dba/go directory and turning on execution bit to allow vpctemp to be executed by oracle user:
[alesk@oraxe go]$ ls -l /oracle/dba/go total 5056 -rwxr-xr-x 1 oracle dba 5177336 Jul 31 12:17 vpctemp
Then all that we did was to create “pseudo” external table. Pseudo in a sense that external table has no reference to external data file, instead all that it includes is a call to preprocessor directive. Preprocessor directive points to our golang program, which pulls data from ServersCheck sensor and prints the result to standard output, which is send to a client. Very simple and convenient way for ad hoc checks. (Oracle introduced preprocessor directive for external table in 11gR2 and later on backporting feature to 10.2.0.5 as well)
// SQL*Plus...connect alesk@oraxe create or replace directory goexec as '/oracle/dba/go'; grant execute on directory goexec to dba; create table vpctemp ( line varchar2(80) ) organization external ( type oracle_loader default directory goexec access parameters ( records delimited by newline preprocessor goexec:'vpctemp' ) location ( goexec:'vpctemp' ) ); create public synonym vpctemp for alesk.vpctemp; grant select on alesk.vpctemp to dbateam;
And that’s it. For a DBA team getting the current temperature and humidity in a data center becomes as easy as:
SQL> select * from vpctemp;
** UPDATE May 2019 **
During the migration of golang vpctemp application from 11g to 18c database we got an error when querying from vpctemp external table:
SQL> select * from vpctemp; select * from vpctemp * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-30653: reject limit reached
We found out that Oracle external table “preprocessor” in 18c doesn’t like the first record (blank line) and since original external table (see DDL above) was created without an explicit reject limit, the Oracle created external table wit the default valu, “reject limit 0”. That’s why query failed immediately.
The workaround was newly created external table:
CREATE TABLE KAVSEK.VPCTEMP ( LINE VARCHAR2(80 CHAR) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY GOEXEC ACCESS PARAMETERS ( records delimited by newline preprocessor goexec:'vpctemp' nologfile nobadfile ) LOCATION (GOEXEC:'vpctemp') ) REJECT LIMIT 10 NOPARALLEL NOMONITORING;
The result:
SQL> select * from vpctemp; LINE ----------------------------------------------- VPCtemp v0.10 Location: VPC1 *********************************** Timestamp : 2019-5-31 13:19:09 Temp. internal: 13.88 Temp. external: ? Humidity : ? 6 rows selected.
Reading ServersCheck Status page from command line – Part 1.
A year ago we installed ServersCheck Temperature & Sensor Gateway that measures temperature and relative humidity in our data center. The gateway itself is a convenient (approx. 10cm long) piece of HW that you can easily mount in some free spot in a rack.
It comes with a built-in web server where you can configure various settings and read current status of device, including internal temperature (inside sensors), external temperature and in our case humidity. After you login to ServerCheck web application you can check status in the data center:
Nice, but I would prefer if I could simply skip the user interaction with a GUI; why not run s simple command line tool/script that will display temperature and humidity in our data center. The bonus feature would be to allow us to simply select ServersCheck status from SQL*Plus.
In the first part of the article I’ll show you how I did it with a simple Google Go program.
Note that we’re currently using ServersCheck gateway with Hardware version: 4.0, Firmware version 2.11. This program might or might not work for you if you’re using something else.
Since ServersCheck gateway doesn’t ship with some documented API I had to do some research and finally found that sensor status data is served from simple xml page that is accessible from h t t p=//10.10.10.10/retcurvalue.xml (replace dummy IP with the IP that you assigned to your ServersCheck gateway).
<retcurvalue> <ssvalue0>16.06</ssvalue0> <ssvalue1>19.75</ssvalue1> <ssvalue2>78.65</ssvalue2> <ssvalue3>?</ssvalue3> <ssvalue4>?</ssvalue4> <ssvalue5>?</ssvalue5> <ssvalue6>?</ssvalue6> ... ... </retcurvalue>
So all that we need to do is to authenticate to ServersCheck internal webserver, parse xml and display temperature and humidity….something like this:
I thought it would be a good exercise to write this with a Google Go, so here it goes:
// // vpctemp.go by alesk (excercise in golang) // package main import ( "fmt" "io/ioutil" "log" "net/http" "regexp" "strings" "time" ) const ( VERSION = "0.10" LOCATION = "DATACENTER1" STATUSURL = "http://10.10.10.1/retcurvalue.xml" USER = "admin" PWD = "r049D;FMNgGHHLFKRRJEJFDD" TIMEFORMAT = "2006-1-2 15:04:05" ) type SensorStatus struct { temp1 string // <ssvalue0> internal temperature temp2 string // <ssvalue1> external sensor temperature hum1 string // <ssvalue2> relative humidity } var sensor SensorStatus func main() { request, err := http.NewRequest("GET", STATUSURL, nil) // I'm using simple obfuscation function getFoo to prevent casual password // leak - this is more than adequate approach in my case. You're free to // change the code to ask user for a password every time or do something more clever. request.SetBasicAuth(USER, getFoo(PWD)) client := &http.Client{} response, err := client.Do(request) if err != nil { log.Fatal(err) } defer response.Body.Close() body, err := ioutil.ReadAll(response.Body) // uncomment the following line if you wish to print the content of complete xml // fmt.Println(string(body)) // debugging // print values <ssvalue0,1,2> without tags // obviously I could use encoding/xml package in golang standard library but // rather than that took a shortcut with a simple regex string parsing. re := regexp.MustCompile("<[/]?ssvalue[0-9]>") i := 0 for _, line := range strings.Split(string(body), "\n") { if i < 4 { //skip first line <retcurvalue> if i != 0 { switch i { case 1: sensor.temp1 = re.ReplaceAllString(line, "") case 2: sensor.temp2 = re.ReplaceAllString(line, "") case 3: sensor.hum1 = re.ReplaceAllString(line, "") } } i += 1 } else { break } } timestamp := time.Now().Format(TIMEFORMAT) fmt.Printf("\nVPCtemp v%s Location: %s\n", VERSION, LOCATION) fmt.Println("***********************************") fmt.Printf("Timestamp : %s\n", timestamp) fmt.Printf("Temp. internal: %s\n", sensor.temp1) fmt.Printf("Temp. external: %s\n", sensor.temp2) fmt.Printf("Humidity : %s\n", sensor.hum1) } func getFoo(foo string) string { bar := foo[22:24] + foo[16:19] + foo[10:11] + foo[3:4] + foo[12:13] + foo[4:6] return strings.Replace(bar, "V", "v", 1) }
In the part 2 I’ll show you how little code was needed to script my “vpctemp” with PowerShell 3.0.
You must be logged in to post a comment.