Sqlps.exe is the New Black

I have not been posting much :( I have been slammed at work. All of my non-family free time has been spent working on my TechED and SSWUG v-conference presentations and demo's. You can keep up with me on Friend Feed. I share things I find interesting in my google reader subscriptions, tweets, digg's and bookmarks there. We need more SQL people around those parts. There is life after NNTP.  Anyway, on to powershell.

In SQL 2005, you had a few options if you wanted OS or hardware data. You could xp_cmdshell into powershell, you get write a CLR proc or function  or, from powershell, you could create db connection objects and push to SQL. I have examples of doing these in the WMI category.

Now with two lines of code, you can do something like grab the current % CPU usage and insert it like this:

$v = (get-wmiobject -computer localhost Win32_PerfFormattedData_PerfOS_Processor -filter "name like '%total%'").PercentProcessorTime
invoke-sqlcmd -query "insert into demodb..tblCPULogger(value) values($v)"

On top of that, there are powershell job steps so you can run that from a job. Now, you say we can that you can already do that in SQL 2008 with the data collector. Well, -computer can also be a remote server like IIS, SQL Server 2000\2005 etc. The data collector only runs on SQL Server 2008.

Have you ever wanted to suck in a huge eventlog into SQL to analyze? I have. Dump to CSV, then openrowset, then munging dates into the proper format. Again, two lines of code.

$Events = Get-EventLog Application
foreach ($Events in $Events){invoke-sqlcmd -query "insert into demodb..AppLogger([Time],[Source],[type],[EventID],[message]) values ('$($events.Time)', '$($events.Source)','$($events.Type)', '$($events.EventID)', replace('$($events.message)', char(39), ''))" -ServerInstance LocalHost}

Grant it took me a couple days to get those two lines right but there really are not many examples on the net using invoke-sqlcmd. Thus the post. I think I will be getting up close and personal with powershell and I will post more samples. Please comment if you have a useful script already.

Permalink     3 Comments     RSS feed