SQL and PowerShell Connectivity


Do you have a SQL server you want to gain access to from PowerShell?  Well you’re in luck, so did I…

My recent connection to BigFix has prompted me to gain access to the BFEnterprise database… Thus I have come up with the following PowerShell function to gain access to my BigFix Server DB installation:

 

function SQLExecute {
    param (
        [string]$SQLSERVER = “<MySQLServerName>”,
        [string]$Database = “BFEnterprise”,
        [parameter(Mandatory = $true)]
        [string]$cmd
        )
    $con = “server=$SQLSERVER;database=$Database;Integrated Security=sspi”
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
    $dt = new-object System.Data.DataTable

    $da.fill($dt) | out-null

    return $dt

}

 

This function will return an object(s) of what you requested. 

For Example:  SQLExecute –cmd “select * from computers”

will select all of the registered computers within your BigFix installation.

Or if you have run the Property Mapper utility the following SQL will return object(s) of your computer ID / Computer Name:

SQLExecute -cmd “select ComputerID, ResultsText as ‘Computer Name’ from QUESTIONRESULTS QR where (QR.SiteID = (select SiteID from PROPERTYIDMAP where propertyname like ‘Computer Name’)) AND (QR.AnalysisID = (select AnalysisID from PROPERTYIDMAP where propertyname like ‘Computer Name’)) AND (QR.PropertyID = (select PropertyID from PROPERTYIDMAP where propertyname like ‘Computer Name’))”

 

More to come… have fun!

Digg This
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s