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!