Category Archives: Scripting
Notes related to scripting/programming techniques and code snippets (python, awk, sed, go, powershell …) will be published here.
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 2.
In the Part 1. of the article I showed command line tool written with Google Go that reads current values from ServersCheck temperature and humidity sensor. In part 2 of the article I’ll show you how I did the same with PowerShell 3.0 script. In a way this script is more sophisticated than the golang variant. First because of the way how password for ServersCheck is handled, instead of security by obscurity I’m asking user once for a password, then storing password encrypted with a combination of his/her computer private key + user session key in a local file vpctemp-pwd.txt. Not bullet proof but nevertheless a big improvement from golang version of the script.
Second, the way I parse xml in powershell is simplified, thanks to select-xml cmdlet built in powershell 3.0.
A prerequisite for running the script is:
– PowerShell 3.0 (hence .NET 4 framework is also needed)
– execution policy must be set to RemoteSigned
You can check PowerShell version with:
PS> get-host | select version
and execution policy with (make sure that you run PowerShell in Admin mode!):
PS> Get-ExecutionPolicy and if it's not RemoteSigned change it with: PS> Set-ExecutionPolicy RemoteSigned
Final result of the script is output similar to golang version:
And here is the powershell script source:
#
# vpctemp.ps1 - PowerShell 3.0 script
# by AlesK
#
$version = "v0.10"
$uri = "http://10.10.10.1/retcurvalue.xml"
$username = "admin"
$pwdfile = ".\vpctemp-pwd.txt"
if (Test-Path $pwdfile) {
$password = Get-Content $pwdfile | ConvertTo-SecureString
}
else {
$password = Read-Host "Password for Admin" -AsSecureString
$password | ConvertFrom-SecureString | Out-File $pwdfile
}
$credential = New-Object System.Management.Automation.PSCredential $username,$password
$result = Invoke-Webrequest -URI $uri -Credential $credential -UseBasicParsing
[xml]$xml = $result | select -expand Content
$temp1 = $xml | select-xml -xpath '/retcurvalue/ssvalue0' | select -expand Node
$temp2 = $xml | select-xml -xpath '/retcurvalue/ssvalue1' | select -expand Node
$hum1 = $xml | select-xml -xpath '/retcurvalue/ssvalue2' | select -expand Node
$timestamp = Get-Date
Write-Host "VPCtemp $version Location: VPC1"
Write-Host "***********************************"
Write-Host "Timestamp :", $timestamp
Write-Host "Temp. internal:", $temp1."#text"
Write-Host "Temp. external:", $temp2."#text" -foregroundcolor "yellow" -backgroundcolor "red"
Write-Host "Humidity :", $hum1."#text"
In the final, part 3 of the article I’ll show you how we’re using Oracle external table “preprocessor” feature to display data center temperature and humidity with simple SQL statement.
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.
How to compile pycrypto 2.4.1 (python 3.2.2 for Windows 7 x64)
This note is a variation of note that talks about compiling cx_Oracle module for python 3.2 (64-bit), but this time around I wanted to try out pycrypto module v2.4.1 that supports python 3. Let me say that I first tried to build pycrypto with MinGW using TLDR experimental installation package for Windows x64. Making long story short, it was a bummer (I hit several errors that I didn’t know how to resolve), I didn’t even try if 32-bit module could be compiled with MinGW. Anyway, I believe sticking with VC 2008 and bulky SDK (over 5GB of installed SW!) is inevitable, at least for me. Here’re the steps that I followed….
My target platform for the pycrypto build was Windows 7 Professional x64 (SP1) with 64-bit python 3.2.2.
1) Download and install Visual Studio Express 2008 SP1 from MS site
It’s mandatory to build python modules with VC 2008, because this is the compiler that was used
to build python 3.2. Note that I installed default components of Visual Studio 2008 in default location.
2) Because Visual Studio Express 2008 SP1 doesn’t ship with 64-bit compiler, download
and install Windows SDK for Windows 7 and .NET 3.5 SP1.
Again, don’t be tempted to install newer Windows SDK for Windows 7 and .NET 4, it must be older SDK (released in 2009). I left all installation options at default.
3) Download and install 64-bit python 3.2.2
Ok, this is obvious step. :-)
4) Download pycrypto 2.4.1
After you download unzip pycrypto-2.4.1.tar.gz in some temporary directory, such as C:\Temp
5) Prepare command shell for 64-bit compiler:
Start -> Run
%comspec% /k ""C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\bin\vcvars64.bat""
Then enter the following on the command line:
SET PATH=C:\PYTHON32;%PATH%
SET PYTHONHOME=C:\Python32\
cd C:\Temp\pycrypto-2.4.1
python setup.py build
python setup.py bdist_wininst
In dist directory you’ll find installation file:
pycrypto-2.4.1.win-amd64-py3.2.exe
After you install pycrypto, check that it works:
cmd> python
>>> import Crypto
>>> print(Crypto.__version__)
2.4.1
IronPython – working with Registry
Here is a code snippet that I wrote recently for IronPython, correcting some registry information that I put by mistake in our image that is used for cloning client PC’s. I’m sure PowerShell script would be even shorter and cleaner, but I had to come with some working code in a hurry.
# -*- coding: cp1250 -*-
# odbc_patch.py -- patching my shallowness
# AlesK.
__about__= "IronPython script // odbc_patch2.py by AlesK"
from Microsoft.Win32 import Registry
# -----------------------------------------------
# How to enumerate a node from ODBC.INI.
# Print System DSN names with SERVER key value if present
# (SERVER key is mandatory for MS ODBC for Oracle, MySQL ODBC,
# etc., but not for Oracle supplied driver!
#
# For complete reference to Regirstry class refer to:
# msdn.microsoft.com/en-us/library/microsoft.win32.registry.aspx
# You can use:
# Registry.ClassesRoot|CurrentConfig|CurrentUser|
# LocalMachine|PerformanceData|Users
# ------------------------------------------------
system_dsn = Registry.LocalMachine.OpenSubKey('Software\\ODBC\\ODBC.INI')
for dsn in system_dsn.GetSubKeyNames():
dsn_key = system_dsn.OpenSubKey(dsn)
server = dsn_key.GetValue('SERVER')
# print only those DSN's that were created with MS ODBC)
# and thus have SERVER defined
if server is not None:
print("DSN=" + dsn + " SERVER=" + str(server))
# ------------------------------------------------------
# How to read and change value in registry.
# Registry.GetValue
# Registry.SetValue
#
# The plot: by mistake I added MS at the end of SERVER variable
# for DSN's that are using MS ODBC driver. Instead of
# SERVER = ORAXMS I really want SERVER = ORAX, striping off MS.
# Candidates in my case are: ORAXMS, ORAYMS, ORAZMS.
# ------------------------------------------------------
odbc_dsn = ['ORAXMS','ORAYMS','ORAZMS']
odbc_node = "HKEY_LOCAL_MACHINE\\Software\\ODBC\\ODBC.INI"
odbc_key = "SERVER"
for dsn in odbc_dsn:
value = ''
value = Registry.GetValue(odbc_node + '\\' + dsn, odbc_key, value)
if value in odbc_dsn:
Registry.SetValue(odbc_node + '\\' + dsn, odbc_key, dsn.strip('MS'))
print("ODBC.INI: patched " + dsn + " SERVER=" + dsn +
" to SERVER=" + dsn.strip('MS'))
else:
print("ODBC.INI: " + dsn + " - nothing to patch.")
Sample output:
C:\Scripts\IronPython\Registry>ipy odbc_patch2.py
DSN=ORAXMS SERVER=ORAXMS
DSN=ORAYMS SERVER=ORAYMS
DSN=ORAZMS SERVER=ORAZMS
DSN=ORA6MS SERVER=ORAT
ODBC.INI: patched ORAXMS SERVER=ORAXMS to SERVER=ORAX
ODBC.INI: patched ORAYMS SERVER=ORAYMS to SERVER=ORAY
ODBC.INI: patched ORAZMS SERVER=ORAZMS to SERVER=ORAZ
dbaportal.eu 
You must be logged in to post a comment.