Archive for February, 2012

24 February, 2012

Postgres logging… on Windows.

by gorthx

Yep, you read that right. My regular readers (hi, dad!) are probably wondering “Who are you, and what have you done with gabrielle?” so let me explain. We’re currently demoing netbrain where I work; it’s a dynamic network mapping and troubleshooting tool. It runs on Windows, and uses Postgres as its backend database. Naturally, I wanted to have a look-see [1]. And since the general rule is “When in doubt, go with what you know”, I figured I’d set up Pg logging.

The first order of business was to find postgresql.conf and pg_hba.conf. Now, I’ve been out of the Windows world for a while and had to feel my way around, and it was a lot like trying to type while wearing a pair of fur mitts. Everything took me three times as long. By the time I had clicky-clicked my way to Computer/OS (C:)/Program Files (x86)/NetBrain/Postgresql 8.4/database, I clung to the conf files like flotsam in the tide. Then I got nervous about editing them with notepad and decided I needed to install Vim first.

Step 1, as always: Make a backup! I called mine postgresql.conf.distro. Normally I’d set up a git repo, but we have a (time limited!) demo license, and I didn’t want to spend time installing git on Windows [2].

So, logging. I wanted to see who’s connecting, and any “long” queries, so I changed postgresql.conf like so:
log_destination = eventlog
log_min_duration_statement = 1s # for now, will increase once I figure out what's "normal" for this database
log_statement = ddl # same
log_connections = on
log_disconnections = on
log_line_prefix = '<%r %d %u>'

You can log to another location (which I haven’t tried yet), but database shutdown and startup messages are going to go to the Windows eventlog, regardless, so you’ll have to watch two things at once [3].

Now I needed to restart the database [4]. Restarting is pretty straightforward: head over to Server Manager -> Configuration -> Services, find Pg in the list, right-click, restart.

Then I needed to figure out how to watch my log messages without ‘tail’. Hm.
Server Manager -> Diagnostics -> Event Viewer -> Windows Logs -> Application…and I see Pg messages!

I created a custom view so I wouldn’t have to look at the rest of the windows stuff; I just checked the “By source” radio button, and selected PostgreSQL from the drop-down. Voila! It doesn’t automatically update; you have to click the ‘refresh’ button on the lower right. Which is kind of a bother, but it is better than nothing.

This is good enough for now; I have other things to investigate while we still have the demo license. The ultimate plan is to get this logging to something that fouine can read. I got some good tips from the PDXPUGers at our meeting last week & am looking forward to checking them out.



1 – “What’s the worst that could happen?”
2 – Which is one of the first things I’ll do when we deploy to production. git for Windows: http://code.google.com/p/msysgit/downloads/list?can=3&q=official+Git
3 – Tim of PDXPUG fame tells me there are ways around this.
4 – Pg on windows is a “service”, so my choices are start, stop, and restart. There’s no reloading to e.g. re-read your pg_hba.conf.

Advertisements
10 February, 2012

Get Cisco Serial Numbers with SNMP

by gorthx

A friend asked me how to go about doing this, and I figured I’d post it here so he can find it again if he needs it.

You’ve already discovered that snmp-server chassis-id is a user-maintained field and therefore not reliable [1], so you can skip trying to use chassisId (1.3.6.1.4.1.9.3.6.3) from OLD-CISCO-CHASSIS-MIB. It’s supposed to be depracated anyway.

Newer equipment supports the ENTITY-MIB. (For certain definitions of “support” … it’s not pretty.)

There are a number of ways to do this. The most straightforward, if you’re starting from scratch, is to walk entPhysicalClass and look for items of type 3, chassis.

It’ll look like this:
:::-->snmpwalk -v 2c -M ~/.snmp/mibs -m ENTITY-MIB -c public -O s myswitch entPhysicalClass | grep chassis
entPhysicalClass.1001 = INTEGER: chassis(3)

Then, use entPhysicalSerialNum and the iid you just found (the 1001 in the previous example) to find the serial number:
:::-->snmpget -v 2c -M ~/.snmp/mibs -m ENTITY-MIB -c public -O s myswitch entPhysicalSerialNum.1001
entPhysicalSerialNum.1001 = STRING: FOC14475A35

In case you don’t have those mibs installed & don’t want to bother with it, here are the numerical equivalents:
entPhysicalClass .1.3.6.1.2.1.47.1.1.1.1.5
entPhysicalSerialNum .1.3.6.1.2.1.47.1.1.1.1.11

That method would look like this:
:::-->snmpwalk -v 2c -c public -O s myswitch .1.3.6.1.2.1.47.1.1.1.1.5 | grep "INTEGER: 3"
mib-2.47.1.1.1.1.5.1001 = INTEGER: 3
:::-->snmpget -v 2c -c public -O s myswitch .1.3.6.1.2.1.47.1.1.1.1.11.1001
mib-2.47.1.1.1.1.11.1001 = STRING: "FOC14475A35"

Now, if you want to get fancy and maybe find out the model number as well, you can then check any of the following (in order of how useful they’ve been to me personally):
entPhysicalModelName .1.3.6.1.2.1.47.1.1.1.1.11.13
entPhysicalDescr .1.3.6.1.2.1.47.1.1.1.1.11.2
entPhysicalName .1.3.6.1.2.1.47.1.1.1.1.11.7

…which would look like this:
:::-->snmpget -v 2c -M ~/.snmp/mibs -m ENTITY-MIB -c public -O s myswitch \
entPhysicalModelName.1001 \
entPhysicalDescr.1001 \
entPhysicalName.1001
entPhysicalModelName.1001 = STRING: SM-ES3G-16-P
entPhysicalDescr.1001 = STRING: SM-ES3G-16-P
entPhysicalName.1001 = STRING: 1



1 – These things tend to migrate off the equipment they were originally configured on, on to other machines, via a copy & paste vector. Pretty soon you have five or six different boxes that supposedly have the same serial number.