Thursday, June 11, 2015

Sunrise Alarm Clock

I know it's been a while since I've posted anything. Much longer since I've posted anything about the RaspberryPi. However, things worked out today and over the last few weeks that I was able to finish up a couple projects involving different Pi for different purposes. Today's is the Unicorn Sunrise.

I have a RaspberryPi powering my NAS. I should say, a RaspberryPi is my NAS since it's just an RPi with a USB HDD attached and Samba running. My NAS, along with most of my equipment is on a high shelf in my walk in closet, right next to an air conditioning vent. It stays cool and it's out of the way. Since I get up for work very early in the morning, I take on the role of ninja, trying to make my way out of the house without waking anyone up. I often use the light in the closet, but I'd rather not. It's bright and if I forget to close the closet door, it lights up the whole room.

A couple months ago, I bought a Unicorn Hat. They're very fun and I bought it because it was cheap, not because I had anything in mind. I had fun playing with the various demo scripts, then got to thinking about how I could actually use this thing. I thought about building something that could replace the LED based alarm clock for the kids, but the brightness of thing would make it like turning on flood lights in their room. That wouldn't do. That's when I had the idea of simulating a sunrise in my closet for when I'm getting ready for work. I could gradually increase the brightness by turning on one LED at a time. Then I had the idea of actually cycling through each of the colors between 0 and the color I picked for sunrise (Let the sunshine in!). That made for a longer cycle: 8 rows of 8 LEDs, for 100 different brightness percentage levels. That didn't end up being a problem though.

I ended up putting a static bag over the top to tone down the brightness even further. Anyway, after following the normal instructions for installing the Unicorn Hat, I wrote this script and set a cronjob to kick it off at 5am.

I just got it put up there, so we'll see how it goes tomorrow morning. I already had the idea of actually just switching between midnight blue and sunrise. That'll be the next version.

Tuesday, June 9, 2015


UPDATE 6/9/2015: Version 1.7 now released. This update adds standalone support. Since CA is including newer versions of MySQL in their products, DBToolv3 is no longer going to work. This change allows you to specify to use MySQLDump instead of DBToolv3. Essentially, you unremark line 15 and remove/remark line 14. If I get enthusiastic about it, I may update the script to allow a switch from the command line to specify which method to use. I'm just not there yet.
UPDATE 2/10/15: Version 1.6 now released. This update changes the way harvesters and DSAs are backed up, by only backing up the ReaperArchive, ReaperArchive15, and HarvesterArchive directories to a single directory (no redundant rolling backups). It only backs up files that have the archive bit set, so before running it the first time, set the archive bit for all the files in those directories. I also fixed the date naming method so it's YYYYMMDD instead of YYYYDDMM. I also added timestamping to the log so you know how long it takes to perform the file backups vs. the database backups.
UPDATE 2/27/14: Version 1.5 now released. This version doesn't have too many changes. I just added the lines below that allow the NFA mess of data files to be backed up along with everything else. This one script can still be used on any product. However, when running on a Harvester or DSA, extra commands backup the data files.
The syntax for running the tool hasn't changed since 1.4 (but 1.4 introduces some major changes), so you should be able to drop the script in place without changing any scheduled tasks.

nqbackup.bat <dbname> <num_backups_to_keep>

Remember, if you need a reminder how to run the tool, just run it without any arguments (or just double click it from Windows Explorer).

Tuesday, March 17, 2015

Custom Formula: IPFromDEC (IP address from decimal)

See part 1 of this thread.
See part 2 of this thread.
Install Instructions

UPDATE: I cleaned up the code. It may be a little less intuitive, but the code itself is simpler. I added a new boolean checker: IPIsValidIP(), which returns true if the IP address is a valid dotted decimal IP address.

UPDATE: I added a new formula: IPGetCIDRList(). This list takes two IP addresses and defines all the address blocks between them, inclusively. For example:


This is handy if you want to get all the address blocks in a range. The more simple the range, the shorter the list of summarizations. The more complex the range, the longer the list. For example:


Download using the same link as below. If you're upgrading, close Excel and copy the downloaded XLAM file to the same location as your existing IPConversion.xlam file. If you don't know where that is, look in your add ins list (File>>Options>>Add Ins, look in the location column).

UPDATE: I compiled and have now published my IPConversion.xlam.  How to install this add-in.
How to use:
Function Argument 1 Argument 2 335.20.30.40
IP2DEC Converts from dotted decimal to decimal 3232239394 169090600 Invalid IP Address 2383683112
IPFROMDEC Converts from decimal to dotted decimal #VALUE!
IPNetwork Returns the network number given an IP address and mask 24 Invalid IP Address
IPIsInSubnet Determines if the given IP address is within the given subnet 16 TRUE FALSE FALSE FALSE
IPGetOctet Returns the octet specified (1-4) 3 15 30 Invalid IP Address 30
IPGetCIDRList Returns a comma separated list of CIDR address blocks between the two provided IP addresses,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
IPIsValidIP Returns true if the specified IP address is a valid IP address TRUE TRUE FALSE TRUE

In a previous post, I showed how to convert an IP address from dotted decimal notation to a decimal number. Well, i found myself in a situation where i needed to do the reverse.  So, there are two ways of doing this.

The first involves using a big formula to chop the decimal value into its equivalent dotted decimal counter parts. The formula goes like this (this formula references cell B2 where the decimal format of an IP address should be):

While this is nice, it would be even nicer if i could just do something like this:

This is the second method.  If you've already created your IP2DEC.xlam file and have it enabled as an add in, you're ready to go, you can add a custom formula to break the IP address back out into the same add-in (if you haven't, click here to see how).

Open a blank workbook in Excel.  Press Alt+F11 or click 'Visual Basic' on the Developer tab in the ribbon bar.  If the project explorer isn't visible, show it by pressing Ctrl+R or by choosing View>>Project Explorer.  You should see two projects in there, one for the new blank workbook that opened and one for the IP2DEC add-in.  You should see Module1 under the IP2DEC add-in (if you don't see this, you didn't do the steps on the previous post).  Double click it.  You should now see the IP2DEC public function code.  Now all you need to do append some code to the bottom of the module that will define the function for converting back to dotted decimal format.
Public Function IPFROMDEC(ipaddress) As String
If ipaddress + 1 - 1 > 4294967295# Then GoTo toobig
Dim firstoctet As String, secondoctet As String, thirdoctet As String, fourthoctet As String
firstoctet = Int(ipaddress / (2 ^ 24))
secondoctet = ipaddress - (firstoctet * 2 ^ 24)
secondoctet = Int(secondoctet / (2 ^ 16))
thirdoctet = ipaddress - (firstoctet * 2 ^ 24) - (secondoctet * 2 ^ 16)
thirdoctet = Int(thirdoctet / (2 ^ 8))
fourthoctet = ipaddress - (firstoctet * 2 ^ 24) - (secondoctet * 2 ^ 16) - (thirdoctet * 2 ^ 8)
fourthoctet = Int(fourthoctet)
Select Case 255
Case Is < firstoctet
GoTo toobig
Case Is < secondoctet
GoTo toobig
Case Is < thirdoctet
GoTo toobig
Case Is < fourthoctet
GoTo toobig
End Select
IPFROMDEC = firstoctet & "." & secondoctet & "." & thirdoctet & "." & fourthoctet
Exit Function
IPFROMDEC = "Invalid IP Address"
End Function
Hit the save button and go try it out.  Put an IP address in one cell and use IP2DEC() to convert it to decimal.  Then use IPFROMDEC() to convert it back.

You might think this an exercise in futility, however, this can come in handy when trying to parse out IP address blocks given CIDR notation.  For example, if you wanted to calculate the starting and ending IP address for the block of IP addresses, I'd have the following:

 The result of the formula in A2 is shown in B2.  This can also be very handy when trying to determine whether or not a given IP address is within a given subnet.

Thursday, February 12, 2015

Extending Windows URL handling for SSH, RDP, and SCP

Sometimes, I've thought how cool it would be if I could design a web page with links like rdp://someserver and have it open an RDP session to someserver. It seems like it would be a simple thing. Just like http:// and ftp://, rdp:// specifies what I want to do, and someserver specifies the server I want to do it to. Turns out it is.

I always thought it was a little dumb that this wasn't built into Windows, but I couldn't figure out how to make it work. I was excited when I found this article with the associated scripts to configure it. Immediately, I thought about putting all three on all my computers (scp, rdp, ssh). Running two batch files on all my computers seemed too bulky, so I combined them all into one:
Save this as a .bat file and get a copy of WinSCP and Putty in the same directory. Run the batch file with elevated privileges (Right Click >> Run As Administrator) and you should see three successful messages.
You should then be able to design pages where the following links actually do something on your local system:

I've been thinking for a while now that I need to use XAMPP portable and just build my own administrative GUI for the NetQoS systems. Theoretically, it would allow you to view the health check reports that I've built (which I still need to sanitize and post) and perhaps a page that automatically renders an architecture diagram, complete with rdp:// links to all the Windows servers and ssh:// links to all the Linux servers. I would also include all my browser view based tools for NPC. It's just an unhatched idea in my brain right now. Every time I think about it, I think how awesome it could be and how much work it would end up being.

Wednesday, January 28, 2015

SNMPGet for Windows and Community String Discovery

I recently needed to test SNMP connectivity from a Windows server to a device to prove that there was a problem outside my system causing SNMP polling to fail. Linux has NetSNMP, which comes with a command line snmpget utility. Windows has no such utility. A quick search on the internet helped with that. I found a utility from SNMPSoft, but of course I had to build a wrapper for it.

The wrapper is pretty simple since my objective is to do a quick check of SNMP connectivity. The version and the OID polled are hard coded to v2c and sysObjectID.

This could be used to discovery which community strings work on a system by using for loops in the command line. For example, if you wanted to test a bunch of community strings:

for %A in (public string1 string2 string3 string4 etc) do @snmpdiscover hostname %A

This will output something like this:

Host:localhost Community:public

Host:localhost Community:string1
%Failed to get value of SNMP variable. Timeout.

Host:localhost Community:string2
%Failed to get value of SNMP variable. Timeout.

Host:localhost Community:string3
%Failed to get value of SNMP variable. Timeout.

Host:localhost Community:string4
%Failed to get value of SNMP variable. Timeout.

Host:localhost Community:etc
%Failed to get value of SNMP variable. Timeout.

In this case, the first community string (public) worked, while the others didn't.