Using Microsoft LogParser

Running a Windows server can be tricky. When things go wrong, finding trends or patterns in a busy Windows Event Log with the native Event Viewer isn’t the easiest thing in the universe. Windows servers on the Internet are particularly vulnerable to Dictionary-based attacks, and one server I look after had a multitude of these messages:

Security Event Log

Security Event Log

I wanted to run a query in Event Viewer to show me all the failed logons, and see which usernames are commonly used in dictionary attacks.  I also wanted to see which IP addresses these came from so that I can perhaps tweak firewall settings to block any rogue ISPs.

Windows Log Parser comes to the rescue.  It’s a free command-line tool from Microsoft that can parse a multitude of log types using an SQL-like query language.  It’s particularly good at parsing IIS logs, but we can save an .evtx event log file and begin to query items that we are interested in.  We can use SQL commands like DISTINCT to only show unique rows.

Let’s dive in with an example:

This shows us all the columns available in our event log file called SecurityLog.evtx.  I’ve specified that the input file is EVT with the -i:EVT switch.

We’re shown a lot of columns, such as EventID, EventType, SourceName.  The details I want to dig into are account name and IP address.  These are held in a long pipe delimited column called “Strings”. Let’s modify our query:

This gives us a single column containing the data we need:

Column 'Strings'

We can see that the username is the 6th pipe delimited section along.  We can modify the query to grab the 6th delimited text using:

Note we’re also using the DISTINCT keyword to get unique values only, and given the column a name of UserNames that we can use on a ORDER BY later.  We now want to output to a file rather than to screen, and also only select items that have “winlogon” in the 19th column.  This is so we only see failed windows logons.  Our final commandline looks like this:

We’ve specified that the output file is a CSV file (-o:CSV) and we’ve told it to save to a file using the “into” command.  Here are just a few usernames that have been tried:

Yet another reminder why you should change your Administrator username to something else!

We can see the IPs where these attacks came from (in column 20) using:

Hopefully this gives you an idea of how powerful Log Parser can be, and how you can use SQL syntax to search your log files.


AQL Messaging APIs

It’s been a little while since I posted here.  It has been a busy Summer with a number of projects going on at work.  At home, I had an idea for a product that needs some SMS text and text-to-voice call capability.  I had a quick look around the market, and signed up to the messaging services at AQL.  I’ve used them before for email-to-SMS services in the past, and wanted to explore a few more of their APIs.  I ended up writing this small class in C# to get this off the ground, and I thought I would share the code here.  As of the time of writing, AQL have a free trial service of 50 credits (which gets you 50 SMS or voice calls).  Feel free to use this code as you wish, standard use-at-your-own-risk terms apply.  You can download it here.  Full details of all their APIs are available here. Enjoy!


Fun with JeeNodes and temperature sensors

Today (29th March) is Arduino Day 2014. I recently bought a JeeNode, a low-power Arduino compatible board. I chose a JeeNode USB module that has a built-in USB interface.

JeeNode USB

The USB interface can operate as a power supply, a programming port (using the open source Arduino IDE software). It also acts as a serial port, which is automatically installed on a windows machine and appears as a regular COM port. It’s also automatically detected on a Raspberry Pi running Raspbian, and normally appears as /dev/ttyUSB0.

The JeeLabs website is full of example code, and has downloadable zip that can be used in the Arduino IDE in minutes. One neat feature of the JeeNodes is that is has a wireless module that can easily send and receive data to another JeeNode. They are addressable, so you could place JeeNode sensors in different rooms, all reporting back to one central node. The radio modules are licence-free, with UK models running at 868MHz and 433Mhz, with a 915Mhz option if you’re in the US. I wanted to experiment with a few different temperature sensors, and see how they behaved.

I got hold of two modules. The first was a budget DS18B20 one-wire temperature sensor (I got this one on EBay as a module that has a pullup resistor and power LED built in), but you can buy the three-pin transistor-style device or a probe-on-a-wire for about £2. The data sheet describes an accuracy of ±0.5°C.

The second module was a JeeLabs Pressure Plug which has a BMP085 barometic pressure and temperature sensor. This is much more expensive at £15, but also gives us air pressure readings. The data sheet describes an accuracy of ±2.5 hPa pressure, and ±2°C.

The unexpected third sensor comes from the ATmega328 microcontroller at the heart of the JeeNode. It can measure its own internal temperature, and whilst not calibrated, if an offset is used it should be reasonably accurate assuming the processor does not overheat.

I pulled it all together, with the two temperature sensors plugged into the JeeNode USB. Samples are taken every second, averaged over 30 samples, and then a delimited string of average values is written to the serial port every 30 seconds. The JeeNode is plugged into a Raspberry Pi that is running a basic shell script. This reads the serial port for data, and once complete, runs curl to do a http post of the values to a web server.

On the web server, I store the different sensor values in an SQL database. I present the details using the Google Chart API, and a bit of jQuery to refresh things automatically. The results from the temperature sensors were interesting, here are 200 data points over 90 minutes:

Comparison of temperature sensors

Comparison of temperature sensors

Here we can see that the sensors follow the same temperature pattern, and have a reasonably constant offset. Given we know that the DS18B20 has the highest level of accuracy, and my lack of other temperature calibration tools, I decided to calibrate the other two sensors against the DS18B20. I took those readings and created an average offset of -1.4°C for the BMP085, and +1.7°C for the internal measurement.

Adding the offsets has helped bring them back into line. My temperature website now shows the sensors with almost identical values:

Sensor readings after offset values

Sensor readings after offset values

Once I have a few more days of data, I’ll re-average these and see if there is a more reliable offset value.

The final question comes from barometic pressure. My readings for today come from the device as 1008hPa. I live roughly half way between Heathrow and Farnborough airports. The QNH values for these airfields (the barometric pressure adjusted to sea level) has been a pretty constant 1013hPa. I live at 24m above sea level (again, based on the nearby airfield at Fairoaks) which means that my local air pressure (QFE in aviation terms) should be 1010hPa. This is within the accuracy of the sensor. I’ve therefore added an offset on my sensor of +2hPa.

I’ve added this to my final display:

Final temperature and pressure dashboard

Final temperature and pressure dashboard

I’ll see how this behaves over the next few weeks, and will use this to log how our heating usage varies over the next few weeks when our central heating controller is swapped for a British Gas Hive controller.