Category Archives: Powershell

Powershell

Query SQL Server using Powershell

Leave a comment

Just provide the required parameters to query your SQL Server.

$dbConnection: FQDN[,Port]

$dbName: database name

Param([string]$dbConnection, [string]$dbName)

function QuerySqlDatabase
{
    # Setup the SQL connection
    $connection = new-object system.data.sqlclient.sqlconnection("Data Source=$dbConnection;Initial Catalog=$dbName;Integrated Security=SSPI;");

    # Define TSQL
    [string]$query =
                "
                your SQL statement here
                "
   
    # Create the dataset and catch errors
    Try {
        $execute = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
        $dataset = new-object system.data.dataset
        $execute.Fill($dataset) | Out-Null #speeds processing
     }
    Catch {
        [System.Exception]
        write-host "Exception while attempting to connect to SQL Server"
        $connectionDetails = $error[0]
        Write-Host "Exception details: $connectionDetails"
        Write-Host "Exiting"
        Exit
    }
    # Create an empty collection
    [System.Collections.ArrayList]$Rows = @()
   
    # Add each row in dataset to the collection
    foreach ($row in $dataset.Tables[0]) {
        $Rows.Add($row) | Out-Null #speeds processing
    }
   
    # Pass collection to main script.
    return $Rows
   
}

QuerySqlDatabase $dbConnection $dbName
Powershell

Get-ClassPath

3 Comments

I updated my getClassPath script to work with OpsMgr 2012 cmdlet’s.

Original OpsMgr 2007 script is here: http://blogs.technet.com/b/jonathanalmquist/archive/2008/11/01/get-monitoringclass.aspx

#begin

##This script accepts a class name, and returns the entire
##Base class path. It also returns Host Class for each
##Base class returned. Essentially, you’ll see the entire
##class path for the given class.
##Jonathan Almquist
##info@scomskills.com
##version 2.0
##updated on 9/25/2012
##Usage = Get-ClassPath.ps1 <class_name>
##Example = Get-ClassPath.ps1 Microsoft.Windows.Computer

param($classname)
$ast = "-"
$class = get-scomclass | where {$_.name -eq $classname}
Write-Host ($ast * 50)
Write-Host "TARGET CLASS" $class
Write-Host ($ast * 50)`n
while ($class -ne "False")
    {
    $property = $class | foreach-object {$_.getProperties()} | Select-Object name
    foreach ($value in $Property)
        {
        if ($value.name -ne $null)
            {
            write-host `t`t`t`t $value.name
            }
            else
            {
            Write-Host `t`t`t`t "No properties"
            }
        }
    write-host `n
    Write-Host ($ast * 50)
    Write-Host "BASE CLASS PATH for" $class
    Write-Host ($ast * 50)`n
    $baseclass = get-scomclass | where {$_.id -eq $class.base.id.tostring()}
    While ($baseclass.base.id -ne $NULL)
        {
        $baseclass.name
        $property = $baseclass | foreach-object {$_.getProperties()} | Select-Object name
        foreach ($value in $Property)
            {
            write-host `t`t`t`t $value.name
            }
        $baseclass = get-scomclass | where {$_.id -eq $baseclass.base.id.tostring()}
        }
    if ($class.hosted -eq "True")
        {
        $hostclass = get-scomclass | where {$_.name -eq $Class.Name} | ForEach-Object {$_.findHostClass()}
        write-host `n
        Write-Host ($ast * 50)
        Write-Host "HOST CLASS for" $class
        Write-Host ($ast * 50)`n
        $class = get-scomclass | where {$_.name -eq $Class.Name} | ForEach-Object {$_.findHostClass()}
        Write-Host $class
        }
        else
        {
        write-host `t`t "*Not Hosted*" `n`n
        $class = "False"
        }
    }

#end

 

Enjoy!