Category Archives: 2931

Sidebar monitoring of SQL databases

A while back I discussed using the Windows Vista Sidebar to display useful information about applications. This led to other conversations, including one with fellow SQL MVP (and Leeds United fan – sorry to hear about the 15 points, mate) Jamie Thomson about monitoring important SQL database statistics using sidebar gadgets. I didn’t give it much thought, because I still had my PowerGadgets solution in place, monitoring all kinds of things based on my own queries. (Personally, I like using ‘union all’ queries. This lets me get a nice collection of numbers for showing in a graph. But I also find that I can easily have a number produced for displaying in a gauge.)

But Jamie has taken this further, and produced a Sidebar gadget to monitor various database statistics. It doesn’t use PowerGadgets, so it’s completely free. You can grab it from his blog at http://blogs.conchango.com/jamiethomson/archive/2007/08/17/SQL-Server-Monitor-Gadget-v1.0.001.aspx, or the original version from http://blogs.conchango.com/jamiethomson/archive/2007/08/09/Announcing-SQL-Server-Monitor-Gadget-for-Windows-Vista-Sidebar.aspx. Great work, Jamie!

sys.database_files and sys.master_files (rather than sys.sysfiles)

Whenever there are changes, people can be slow to embrace them. One I’ve come across recently is that looking in sysfiles is no longer the best way of getting information about your database files. sys.database_files will tell you a lot more about them, and give you much nicer ways of filtering them. So for example, if you want a list of the log files for your database, try:

select * from sys.database_files where type = 1

And sys.master_files will list them for the whole system. So it becomes really easy to look at the state of things from within T-SQL.

For more information on these, check out http://msdn2.microsoft.com/en-us/library/ms174397.aspx and http://msdn2.microsoft.com/en-us/library/ms186782.aspx.

Now go and make yourself a Vista Sidebar gadget (or use PowerGadgets to do it really easily) to show you the size of your log files (or whatever). Have it update every hour, sitting alongside those gadgets which tell you how nice the Adelaide weather is and show you pictures of the blue skies in Australia.

Cricket PowerGadget

I was thinking about PowerShell and how you can get it to do fantastic things. And I wondered how easily it could be used for scraping cricket scores.

So I threw together four lines of code to grab the cricket scoreboard from cricinfo and rip out the title.

$ret = (new-object Net.WebClient).DownloadString(“http://content-aus.cricinfo.com/ausveng/engine/current/match/249226.html?view=live;wrappertype=mainframe”)
$titlestart = [Regex]::Matches($ret,”<title>”,”IgnoreCase”)[0].Index
$titleend = [Regex]::Matches($ret,”</title>”,”IgnoreCase”)[0].Index
$ret.Substring($titlestart+7,$titleend-$titlestart-7)

Edited: This can be done easily in one line – Lars pointed out the use of Regex to grab the section between the title tags, which then means we don’t need to store $ret at all. It can now be:

[Regex]::Match((new-object Net.WebClient).DownloadString(“http://content-aus.cricinfo.com/ausveng/engine/current/match/249226.html?view=live;wrappertype=mainframe”),”<title>(.*)</title>”,”IgnoreCase”).Groups[1].Value

It’s not particularly elegant, but it works nicely. I would’ve liked to have handled the HTML as XML instead, and just gone straight to the Title tag, but there’s stuff in there that won’t convert to XML, so I guess that option wasn’t available.

And the really nice thing about this is that I can put these four lines into PowerGadgets, and in all of 10 seconds have a floating gadget which I can use in XP as well as Vista, and (in Vista) put in the sidebar if I want. I’ve told it to refresh every minute, which won’t refresh as quick as some, but hopefully won’t stop working too quickly. It’s not quite as nifty as Darren Neimke’s gadget, but then again, this was really really quick to throw together.

And of course, I’ve left the advert for Cricinfo in there. I wouldn’t want to hide the source of the information. And if they ask me not to do this, then of course I’ll stop. Cricinfo have a great site, and I really don’t want to upset them.

cricinfo

PowerGadgets maps

I’ve been told that it’s quite easy to create your own maps for PowerGadgets, and I plan to give it a try some time soon.

If you’re in the US, it’s already very easy to throw together a map of the US which charts the states that have had sales. And in the Advanced Properties window you can add conditional formatting very easily to change the colour according to the range. Easy to script for PowerShell commands too. Of course, if you’re not in the US, then you may want to make your own custom map to show where things are.

It would be nice to have a gadget that showed where your company was making sales were made by Australian postcode. Or make a map which shows the office plan of a call-centre with the number of calls made that day.

Application monitoring in 20 seconds with PowerGadgets

Performance Monitoring is great, but unless you’ve made a bunch of custom monitors, it doesn’t really tell you the health of your application. Well, not completely.

For example, take wHooiz – Cameron and Clarke’s profile tool. I’m sure they have various things running to to persuade them that their system is ticking along nicely. But that doesn’t tell them whether their marketing is working. It doesn’t tell them if their application is actually happy and successful. There may be no 404s in site, but if no-one is using the thing, then they’re failing. Luckily for them, they seem to be having a great deal of success. I’m sure if you go there, you’ll find out they’re giving away a Nintendo Wii soon. But wait until that competition is over before you start using wHooiz, because I want to win it.

To find out if they’re doing well, these guys run a query on their database which shows them how many people have registered. Currently it’s about 125. If the site is going well, it might be 130 by the time I’ve finished writing this, and 200 by the time you’re reading it. Or maybe my blog is really popular and you’re reading this while it’s only 131, who knows?… but Cam and Clarke should know. For the sake of their confidence in their application, they should be seeing a number tick over in the corner of their eye… maybe in their sidebar!

And this is where PowerGadgets comes in. And you don’t even need a sidebar to have PowerGadgets do its thing – it works just fine in XP as well, floating around the desktop, on top of your applications if you want.

So go and download (and install) the evaluation copy, and walk with me for a moment. Counting the seconds, we…

1… 2… find the “PowerGadgets Creator” in the Start Menu and click it… 4… 5…

Pick “Digital Panel” from the list presented in the screen that opens up, and then the SQL option in the next pane. (Don’t bother taking the time to notice that you can very quickly use maps, gauges, charts, or get your data from Web Services or PowerShell – that’s not part of the 20 seconds)… 8…

Enter the database connection details (all us developers know how to do this really quickly, right, but let’s assume it took you three seconds)… 11… 12…

Now paste in the SQL statement you prepared earlier, something like “select count(*) from dbo.PeopleRegistered”. 14…

Ooh, there’s the result of the query in a nice digital format… but we’re not done yet.

Let’s click the “Data Refresh Rate” button, and leave the default of “1 minute” (or whatever you want… 5 seconds if you want to see something happening more often).

15… Change the “Display in” option to Desktop, and hit Save. 17… Give it a name, 18…samplepowergadget

And you’re done. Take the remaining time to launch it and see your numbers happily changing each minute.

Ok, so you might want to explore the options some more, and take a whole minute over it. Alternatively, you might want to pipe the results of a PowerShell script straight into out-gauge or out-chart, possibly using the -configure option to give you more options when you do. Or then pipe the result of out-chart into an email, and see it arrive in your Inbox with a pretty graph!

It costs about US$300 for the tools to make this, which is probably worth it when you consider the time you’ll save. The licence to display a PowerGadget is about US$70, but if you can show someone this easily that their application is working (or that they’re making sales, or whatever), then I’m sure they’ll happily part with that too.