7 responses

  1. Profile photo of Stefan Koell
    Stefan Koell
    February 18, 2010

    Pete, you rock, dude!

    I just wanted to fire up google to find an example like this. I am in the process to write a monitor based on a SQL query with the PowerShell module. I think you read my mind and spared me all the trouble stealing and compiling from some sample code.

    Perfect timing! Thanks

  2. Profile photo of Pete Zerger
    Pete Zerger
    March 9, 2010

    Hey Stefan, have you done anything with the SQL queries in PowerShell yet? I have not spent a lot of time parsing multi-record datasets and was wondering what you’ve figured out to this point.

  3. Profile photo of Stefan Koell
    Stefan Koell
    March 10, 2010

    Hi Pete,

    your script was very helpful getting me started! Thanks again. Here’s a trimmed version what I do right now:

    $SQLServer = “hugo”

    $SQLDBName = “somedb”

    $SqlQuery = ‘EXEC [dbo].[usp_mystoredprocedure] ”’ + $parameter1 + ”’, ”’ + $parameter2 + ””

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

    $SqlConnection.ConnectionString = “Server=$SQLServer;Database=$SQLDBName;Integrated Security=True”

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlCmd.CommandText = $SqlQuery

    $SqlCmd.Connection = $SqlConnection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object System.Data.DataSet

    $SqlAdapter.Fill($DataSet)

    $SqlConnection.Close()

    foreach ($row in $DataSet.Tables[0].Rows)

    {

    $Drive = $row[1].ToString().Trim()

    $Threshold = $row[2].ToString()

    $MountPoint = $row[3].ToString().Trim()

    }

    This thing is used for our custom DB disk space monitoring and our DBA’s are putting their thresholds for servers/cluster resources in a table and our monitoring script is fetching those values and checking the disk space (hence the $Drive, $Threshold, … stuff).

    cheers,

    Stefan

  4. Profile photo of Stefan Koell
    Stefan Koell
    March 10, 2010

    Ups,

    looks like formatting is not great in the comments. If you need the script as attachment, let me know…

  5. Profile photo of Bryan
    Bryan
    May 21, 2010

    I recently finished up related script, to connect to a remote ODBC data source. It’s part of a work in progress to check the status of Ops Mgr generated tickets in Remedy, and then close alerts for associated resolved tickets (we don’t have a 2-way connection [yet?]).

    I’m sharing here in case others in the SCC community might also find it helpful:

    http://bryandady.squarespace.com/journal/2010/5/21/new-powershell-scripts-to-share.html

Leave a Reply

You must be to post a comment.

Back to top
mobile desktop