Monitoring APC UPS via TheDude and SNMP

We have had some issues with our UPS systems recently and found that our monitoring and alerting systems were not working too well. After some tinkering I found that you can remotely monitor the UPS via The Dude. This method uses SNMP to monitor the UPS variables.

Examples of live UPS data including a UPS with a faulty battery.

Adding Probes for alerts:

Example of adding a specific probe.

I used the following Oid's:

iso.org.dod.internet.private.enterprises.318.1.1.1.2.2.1.0  - Battery % >= 90

iso.org.dod.internet.private.enterprises.318.1.1.1.7.2.3.0 - Battery Self Test != 0

iso.org.dod.internet.private.enterprises.318.1.1.1.2.2.2.0 - Battery Temp < 40

iso.org.dod.internet.private.enterprises.318.1.1.1.4.2.3.0 - Output Load < 90

iso.org.dod.internet.private.enterprises.318.1.1.1.2.2.4.0 - Replace Battery != 2

iso.org.dod.internet.private.enterprises.318.1.1.1.2.2.3.0 - Time Remaining > 10:00

You can use the code below to view the UPS status by adding it to the Label.

[Device.Name]

Serial: [oid("iso.org.dod.internet.private.enterprises.318.1.1.1.1.2.3.0")]

Remaining: [oid("iso.org.dod.internet.private.enterprises.318.1.1.1.2.2.3.0")]

Battery Cap: [oid("iso.org.dod.internet.private.enterprises.318.1.1.1.2.2.1.0")]%

Battery Temp: [oid("iso.org.dod.internet.private.enterprises.318.1.1.1.2.2.2.0")] C

Output Load: [oid("iso.org.dod.internet.private.enterprises.318.1.1.1.4.2.3.0")]% 

Output V: [oid("iso.org.dod.internet.private.enterprises.318.1.1.1.4.2.1.0")]V

Output A: [oid("iso.org.dod.internet.private.enterprises.318.1.1.1.4.2.4.0")]A

Last Test: [oid("iso.org.dod.internet.private.enterprises.318.1.1.1.7.2.4.0")]

Result: [if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.7.2.3.0")=1,"Success","Failed")]

Reason Switch: [if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.3.2.5.0")=1,"No Events","")][if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.3.2.5.0")=2,"High line Voltage","")][if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.3.2.5.0")=3,"Brownout","")][if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.3.2.5.0")=4,"Loss of mains power","")][if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.3.2.5.0")=5,"Small temp power drop","")][if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.3.2.5.0")=6,"Large temp power drop","")][if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.3.2.5.0")=7,"Small spike","")][if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.3.2.5.0")=8,"Large spike","")][if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.3.2.5.0")=9,"UPS Selft Test","")][if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.3.2.5.0")=10,"Excessive input voltage fluctuation","")]

Batt Fault: [if(oid("iso.org.dod.internet.private.enterprises.318.1.1.1.2.2.4.0")=2,"Yes","No")]

How to run an application as a different user

I often have the need to run applications with different credentials. For instance I may need to connect to a website that users Windows integrated authentication, but I need to use my admin account, not my normal logon to access this. Instead of logging off or switching users I simply created a batch script which launches IE with a different set of credentials.

runas /user:[domain]\[user account] "C:\Program Files (x86)\Internet Explorer\iexplore.exe \"[URL]\""

You can use the runas command to run any application you like, just substitute the values above.
Also note that doing as above will prompt you for your password when you run it, so it is best for interactive use. You can store your password in the batch script, but I would not recommend this.

Restarting Windows XP Mode Virtual Machine via script

Let me give you some background into the problem that I had. I have several users who use an application that does not work on Windows 7, so I had to load Windows XP mode on their machines to allow them to access the software. The software in question uses a trusted connection to connect to a database server so the users have to logon to the Windows XP mode VM with their Active Directory credentials. This works pretty well up until the point that the user has to change their AD password. The Windows XP Mode VM never actually shuts down after the application is used, it goes into hibernate / suspended mode and therefore the users old credentials are still cached on the VM and the connection to the DB server fails.

The way to resolve this is to reboot the VM, but this is a huge pain in the butt when you have to get VNC access to a machine and run through the whole process of launching the VM and restarting it, so I made some scripts to make my life a bit easier.

Please note that you require administrator privileges on the remote system in order to execute these scripts. You could modify them to include username and password if required. The Virtual machine also needs to be running, so ask the user to open the app that is giving them issues and execute the deploy script. The user can then goto C:\scripts and run the resart-vm.bat file to restart the VM on their machine. This saves me a ton of time and I hope you find it useful.


Scripts list:

restart-vm.ps1	Powershell script that restarts the Virtual machine.
restart-vm.bat	Batch script to execute the powershell script.
deploy-restart-vm.bat	Script copies the files to the remote system C:\Scripts directory. Allows input of host name.

restart-vm.ps1
$vpc = New-Object -ComObject "virtualpc.application" 
$vm = $vpc.FindVirtualMachine("Windows XP Mode") 
$vm.GuestOS.ShutDown("true")

restart-vm.bat:
powershell -ExecutionPolicy ByPass -File C:\Scripts\restart-vm.ps1

deploy-restart-vm.bat:
@echo off
cls
echo.
echo Deploy shutdown-vm script to remote host.
echo -----------------------------------------
echo.
set /p host="Please enter host name: "
cls

echo.
echo Deploy shutdown-vm script to remote host.
echo -----------------------------------------
echo remote host: %host%
echo.
echo.
echo Making directory on remote machine
echo.
md \\%host%\c$\scripts
echo.
echo Copying files to remote host
echo.
xcopy /Y C:\Scripts\Deploy\restart-vm\restart-vm.* \\%host%\c$\scripts
echo.
pause

Backup and Compress SQL Server Express Backup

I have multiple MS SQL express servers that I need to manage and backup and I have found that the backups can get quite large, so I looked into methods of compressing the backups. I found that there is a builtin method in SQL server, but this is only for a licensed copy of SQL server, not for the express edition. So being the hacker that I am I decided to do it myself with some scripting and 7zip.

This is the process:

• backup SQL server DB.
• compress backup file with 7zip. (7zip website)
• rename the file and copy it to a backup location.
• send an email notification that the backup completed using blat.

It sounds simple right? Well it can be a bit difficult to tie it all together so here are some scripts that I have used to achieve this.

1. Create backup device
• Open SQL server management studio
• Expand Server Objects
• Right Click Backup Devices
• Click new backup device
  
• Give the device a name (you need this for the batch script so try excluding spaces) and give it a backup path. I prefer using another disk and a simple path, something like D:\backups\db-backup.bak
  
  
2. Backup Database Script
• The backup batch script should look something like below, just substitute the databasename to the name of the database you are backing up and backup-location to the name of the backup device you specified above.
  
  sqlcmd -S localhost\databasename -E -Q "BACKUP DATABASE [databasename] TO [backup-location]"
  
  IF NOT EXIST "D:\backup\db-backup.bak"
      set subject=Backup error : SQL Backup failed
      set body=SQL Backup failed. %date% %time%
      GOTO SENDMSG
3. Compress the backup file using 7zip and rename file
• Again update the script below accordingly. Remember the 7zip directory needs to have no spaces otherwise the script does not work.
  
  set zipdir=C:\progra~1\7-Zip
  %zipdir%\7z.exe a "D:\backup\db-backup.bak" "D:\backup\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z"
  
  IF NOT EXIST "D:\backup\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z"
      set subject=Backup error : 7zip failed
      set body=7zip operation failed. %date% %time% 
      GOTO SENDMSG
4. Copy the file to a remote location
• This is pretty simple again just update the script as per you needs
  
  xcopy "D:\backup\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z" "\\backupserver\folder" /s /d
  
  IF EXIST "\\backupserver\folder\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z"
      set subject=Backup successful
      set body=Backup successful. %date% %time%
      GOTO SENDMSG
5. Send email message
• This function requires BLAT, just download blat and copy it to a directory something like C:\blat. It s a great command line app that I have used before for sending mails from some PHP scripts.
  
  set blatlocation=C:\blat
  set smtpserver=mail.myserver.com
  REM add addresses separated by commas
  set toaddress=address@myserver.com
  set fromaddress=address@myserver.com
  set appname=SQL Backup App
  
  :SENDMSG
  %blatlocation%\blat.exe -to %toaddress% -i "%appname%" -server %smtpserver% -f %fromaddress% -subject "%appname% : %subject%" -body "%body%"
  GOTO END