Email my public address

Email my public address

As by now you would have known that I have a RaspberryPI connected to my TV running OSMC. I recently had to need to connect to this RPI remotely. So as we all do I punched a hole in my router to the device on the ssh port. I also signed up for DynDNS so I can access the Pi from a URL. But being the tinkerer I am I decided, why not let the Pi tell me it’s public IP address. And just like that a new tinkering project was born! I first created a small php script on my server which returns the IP address of the connecting party, you can check it out here.
php script

echo $_SERVER['REMOTE_ADDR'];

Now I had to find a way to get the Pi to connect to this site, collect the returned ip address and check it with some previously stored address for change, and of course email me ONLY when there’s a change. So I created a quick python script to do just that:

#!/usr/bin/python
import requests
import datetime
import smtplib
from email.mime.text import MIMEText

url = "http://173.254.28.92/~meineoas/kimanii/youripaddress"
try:
    response = requests.get(url)
    pipaddr = response.text
    f = open('ipadddress','rw+')
    ipaddr = f.readline()
    f.close()
except:
    pipaddr = ''

if(ipaddr <> pipaddr):
    f = open('ipadddress','w')
    f.write(pipaddr)
    print datetime.datetime.now(), ipaddr, "=>", response.text
    today = datetime.date.today()
    f.close()
    to = 'me@example.com'
    gmail_user = 'another@example.com'
    gmail_password = ''
    smtpserver = smtplib.SMTP('smtp.gmail.com', 587)
    smtpserver.ehlo()
    smtpserver.starttls()
    smtpserver.ehlo
    smtpserver.login(gmail_user, gmail_password)
    msg = MIMEText(pipaddr)
    msg['Subject'] = 'IP For RPI on %s' % today.strftime('%b %d %Y')
    msg['From'] = gmail_user
    msg['To'] = to
    smtpserver.sendmail(gmail_user, [to] ,msg.as_string())
    smtpserver.quit()

And this is not all, now this script must be scheduled so it checks the ip then emails me if need be. I did this via good ole cron. There’s a small caveat though with the cron, I had to first change into the directory of the script then run the script or else it would look for the ipaddress file in the current path.

* * * * * cd ~/Scripts/;./getipaddress.py >> log.txt

Make your Raspberry Pi speak!

I found an article online once that taught me the “say” command on OSX – which i think was pretty cool! The command line function allows you to activate the speech to text feature of OSX and can be used for a variety of fun project. You can try it by firing up terminal (Applications > Utilities > Terminal) in OSX and typing

Say "hello world"

I embarked on an audacious project to teach my son programming over the holiday and thought this feature would be a really cool addition to one of the lessons. The idea is to teach him programming in python on the raspberry pi so he learn basic programming structures and also the hardware that it runs on. Natively the RPI does not have this (say) function built in but lucky I found a way to get it done. Here follows the instructions to get the RPI to speak.

The Raspberry Pi needs mplayer and internet access to make this work. By default it does not have mplayer installed by default.

sudo apt-get update
sudo apt-get install mplayer

Now you can create a command out of it by creating a /usr/bin/say file

#!/bin/bash
mplayer "http://translate.google.com/translate_tts?tl=en&q=$1";

Depending what speaker setup you have, you may need to adjust some settings. In particular, you can try telling the Pi what audio interface to use with the command

amixer cset numid=2


numbid can be any of
0=auto
1=analog
2=HDMI

On a side note, if you get this error from amixer:

amixer: Mixer attach default error: No such file or directory

The fix for this is to run this command

sudo modprobe snd-bcm2835

That command will create the necessary device directories in /dev/snd/

To actually run the file as a command or program it has to be executable, and in linux that means changing the permission attribute on the file:

chmod +x /usr/bin/say

And that’s it, you can now get the RPI to speak by typing the say “hello world” like on OSX.

SQL Reporting Services (SSRS) subscription jobs

SQL Reporting Services (SSRS) subscription jobs

Anyone who have worked with reporting services have run into the problem of a report subscription not executing a report correctly and the usual response is “how do I re-run this report”. For the novice, we know that all the subscriptions are actual jobs under SQL Server Agent on SQL server. But going there will reveal a trove of obfuscation. All the subscription jobs are named with GUIDs, not very useful to a DBA.
ReportServerJobs

There’s is away to reveal the true name of these jobs and a bit more. Here’s a script you can use to do just that:

SELECT Schedule.ScheduleID AS JobName , 
       Catalog.Name AS ReportName , 
       Subscriptions.Description AS Recipients , 
       Catalog.Path AS ReportPath , 
       StartDate , 
       Schedule.LastRunTime
FROM
       ReportServer.dbo.ReportSchedule 
	  INNER JOIN ReportServer.dbo.Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
       INNER JOIN ReportServer.dbo.Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
       INNER JOIN ReportServer.dbo.Catalog ON ReportSchedule.ReportID = Catalog.ItemID AND Subscriptions.Report_OID = Catalog.ItemID;

The ReportSchedule table links the Report ID to the Subscription ID, so this makes a food starting table.
The Schedule and Subscriptions table contain information on just that – subscriptions and schedules but these are stored in GUIDs also so the last table Catalog is required for the user friendly names.

Database Deployment Scripts

Database Deployment Scripts

Many of us as part of our daily job perform database deployments. It is imperative that organizations implement adequate change management procedures to ensure programmers do not make changes directly into the databases of the production environment without first properly justifying the value of this change and testing their change in an appropriate testing databases.

Most database admins and programmers, write scripts to make mass changes to the data in their databases. We are all guilty of writing scripts without proper testing and transactions only to realize a second later after running the script we made a big bobo. Also in many organizations the team or person writing the deployment may not be the same person/team deploying the change, this script will also prove handy in these situations. In-light of this, I decided to share an example of a proper database deployment script. This script displays on screen updates as to what happening and also verifies expected changes before finally commiting the entire transaction.

-------------------------------------------------------------------------
--KDaniel : 2015-02-06
--ISSUE: http://issuetracking/issue/1000
--Description: Used to update tax on sales
-------------------------------------------------------------------------

-- Always specify database to use
USE MAINDATABASE
SET NOCOUNT ON

--Set variables
DECLARE @TransactionName VARCHAR(20), @cnt int, @oldTax int, @newTax int, @expectedChange int
SET @oldTax = .05
SET @newTax = .09
SET @expectedChange = 56

SET @TransactionName = 'ThisIsAnUnneccessarilyLongTransactionNameJustForFunAndJustBecauseICan'

--Alert user of what's about to happen
RAISERROR( 'UPDATE TAX of Sales',0,1) WITH NOWAIT

--Do the work
BEGIN TRANSACTION @TransactionName
RAISERROR( ' 1.0 UPDATING Sales Tax',0,1) WITH NOWAIT

UPDATE Sales
SET Sales.Tax = @newTax
WHERE Sales.Tax = @oldTax
AND Sales.Status = 'Open';

--Verify Data
RAISERROR( ' 1.1 Verifying Sales updates',0,1) WITH NOWAIT
PRINT ' ...Expected: ' + cast (@expectedChange AS varchar)

SET @cnt = (SELECT count(*) FROM dbo.SALES WHERE Sales.Status = 'Open' and Sales.Tax = @newTax)

PRINT ' ...Found: ' + cast (@cnt AS varchar)
if(@cnt=@expectedChange)
BEGIN
COMMIT TRANSACTION @TransactionName
END
ELSE
BEGIN
-- If FAIL then display error, roll back transaction and STOP
PRINT ' ...ERROR: Wrong number of SALES updates!'
ROLLBACK TRANSACTION @TransactionName;
RETURN
END
PRINT 'ALL DONE!'