Tool to query mySQL and connect to thousands of remote nodes to gather information

DB_mySQL_SOAP_call.ps1

<#########################################################################

PowerShell tool to query mySQL, obtain node info, verify/bring VPN up, and connect via SOAP to pull config/license/version data
Data used for license true ups, program management/recalls, configuration item record validation, and problem management activities

   ***REMOVED*** Sweep Utilty
   
   Author:       Al Ludwig - al.ludwig at ***REMOVED***
   Usage:        Verify ***REMOVED*** vars and SQL are correct
		          Run using PowerShell
   Depends:      Connector/Net for MySQL
                   http://dev.mysql.com/downloads/connector/net/
   Version:      ***REMOVED***
   Build Date:   ***REMOVED***
#########################################################################>
<#
to-do:
 -group equipment and only log one entry per host when multiple failures occur for one node
 +line 225 - use $arrRocHosts to only hit the EA if not already successful (maybe need to store last checked roc_hostname and success/fail?)
 +convert write-host to write-log function, add writetoconsole option
 +filter down on tool of term server/rdp
 +add the "***REMOVED*** version" to the output
 +add ping loop to make sure connection is up, log if cannot ping
 +rework SQL to capture user/pass and adminuser/adminpass to gain more login combos
#>

# set error preference
    $ErrorActionPreference = "Stop"
# setup file name used to store output
    $strLog = $MyInvocation.MyCommand.Path -replace "(?<=\.)[^.]*$","$(Get-Date -Format yyyyMMddhhmmss).log" #sweep output
    $ScriptLog = $MyInvocation.MyCommand.Path -replace "(?<=\.)[^.]*$","log" #script log
# write status to console when true
    $WriteConsole = $true
# setup variables for mySQL ***REMOVED*** DB
    $strhost = ""
    $struser = ""
    $strpass = ""
    $strDB = ""
# SQL Used to pull ***REMOVED***'s from ***REMOVED***
    $sql = @"
    select distinct
    s.site, rocip,realip,
    IFNULL(openview,rocip) as roc_hostname, d.equipmentgroupdescription, es.status, tls.adminuser as 'user', tls.AdminPassword as 'password', tls.domain
    from ***REMOVED*** e, ***REMOVED*** s, ***REMOVED*** z, ***REMOVED*** d, ***REMOVED*** es, ***REMOVED*** tls where 
    e.statusid in (1,2,3,5,8,9)
    and e.siteid=s.siteid
    and ((s.zoneid=z.zoneid) or (s.zoneid is null))
    and e.EquipmentTypeID=d.EquipmentTypeID 
    and tls.ToolsID in(7, 16)
    and e.statusid=es.statusid and e.equipmentid=tls.equipmentid
    and (
			EquipmentGroupDescription like '***REMOVED***%***REMOVED*** ***REMOVED***%' or
			EquipmentGroupDescription like '***REMOVED***' or
			EquipmentGroupDescription like '***REMOVED*** ***REMOVED***%***REMOVED***%' or
			EquipmentGroupDescription like '***REMOVED*** ***REMOVED*** ***REMOVED***' or
			EquipmentGroupDescription like '***REMOVED*** ***REMOVED*** ***REMOVED***%'
	)
    and d.equipmentgroupdescription not like '%host server%'
    and e.rocip is not null
    and (tls.***REMOVED*** is not null and tls.***REMOVED*** is not null)
    union
        select distinct
    s.site, rocip,realip,
    IFNULL(openview,rocip) as roc_hostname, d.equipmentgroupdescription, es.status, tls.user, tls.password, tls.domain
    from equipment e, site s, zonegroup z, equipmentgroups d, equipmentstatus es, actualtools tls where 
    e.statusid in (1,2,3,5,8,9) #select * from ***REMOVED***
    and e.siteid=s.siteid
    and ((s.zoneid=z.zoneid) or (s.zoneid is null))
    and e.EquipmentTypeID=d.EquipmentTypeID 
    and tls.ToolsID in(7, 16) #see dbo.AvailableTools for descriptions
    and e.statusid=es.statusid and e.equipmentid=tls.equipmentid
    and (
			EquipmentGroupDescription like '***REMOVED***%***REMOVED*** 4%' or
			EquipmentGroupDescription like '***REMOVED***' or
			EquipmentGroupDescription like '***REMOVED***%4%' or
			EquipmentGroupDescription like '***REMOVED*** Unknown Version' or
			EquipmentGroupDescription like '***REMOVED*** Gateway 4%'
	)
    and d.equipmentgroupdescription not like '%host server%'
    and e.rocip is not null
    and (tls.***REMOVED*** is not null and tls.***REMOVED*** is not null)
    order by site asc, realip asc
"@

# Function to write to a log file
Function Write-Log
{
    Param(
        [string[]]$LogMsg,
        [string[]]$MsgType = "I",
        [string[]]$LogFile = $ScriptLog,
        [string[]]$DateTimeNow = (Get-Date -format "yyyy/MM/dd HH:mm:ss")
    )
    Try {
            $ProcessId = [System.Diagnostics.Process]::GetCurrentProcess().Id
            $LogMsg = "$($DateTimeNow) [$($ProcessId)] <$($MsgType)> $LogMsg"
            Add-Content -Path $LogFile -Value $LogMsg
            If ($WriteConsole -eq $true) {
                Write-Host $LogMsg
            }
        }
    Catch {
            Write-Host "An error occurred: $($_.Exception.Message)"
        }
}

# Function to Check a log and Rotate as Needed
Function LogRotate
{
    Param(
        [string[]]$LogFile,
        [Int32]$NumberofLogsToKeep = 5,
        [Int32]$SizeLimitMB = 1MB
    )
    Try {
        #Make sure file exists
        If (Test-Path "$($LogFile)") {
            $File = Get-Item "$($LogFile)"
            # If file is greater than the size limit
            If ($File.Length -gt $SizeLimitMB) {
                # Loop through and rotate logs
                for ($i = $NumberofLogsToKeep; $i -ge 1; $i--) {
                    # Loop through and move files to the next place
                    If (Test-Path "$($LogFile).$($i-1)") {
                        Move-Item "$($LogFile).$($i-1)" "$($LogFile).$($i)" -Force
                    }
                }
                #move current file to .1
                If ($i = 1) {
                    If (Test-Path "$($LogFile)") {
                        Move-Item "$($LogFile)" "$($LogFile).$($i)" -Force
                    }
                }
            }
        }
    }
    Catch {
            Write-Log "An error occurred: $($_.Exception.Message)" "E"
    }
}

# Function to ping a node
Function Ping-Node
{
    Param(
        [string[]]$Node,
        $Attempts = 5,
        $SleepTime = 3
    )
    $ping = Test-Connection -ComputerName "$($Node)" -Count "1" -Quiet
    $AttemptsCnt = 1
    do { 
        $ping
        $AttemptsCnt++
        If ($ping -eq $false) {
            Start-Sleep -Seconds $SleepTime
        }
    }
    until (($ping -eq $true) -or ($AttemptsCnt -gt $Attempts))
}

# Check the log and rotate if needed
LogRotate $ScriptLog
<# Function to make a soap request #>
function Execute-SOAPRequest 
( 
        [Xml]    $SOAPRequest, 
        [String] $SoapAction,
        [String] $URL,
        [String] $user,
        [String] $pass,
        [String] $domain,
        [String] $verbose
)
{
        if ($verbose) {
            write-Log "Request: $SOAPRequest`nAction: $SoapAction`nURL: $URL`nUser: $user`nPass: $pass`nDomain: $domain"
            write-Log "Sending SOAP Request To Server: $URL" 
        }
        $soapWebRequest = [System.Net.WebRequest]::Create($URL)
        $soapWebRequest.Credentials = New-Object System.Net.NetworkCredential($user,$pass,$domain)
        $soapWebRequest.Headers.Add("SOAPAction","`"$SoapAction`"")
        $soapWebRequest.ContentType = "text/xml;charset=`"utf-8`""
        $soapWebRequest.Accept      = "text/xml"
        $soapWebRequest.Method      = "POST"
        $soapWebRequest.Proxy = [System.Net.GlobalProxySelection]::GetEmptyWebProxy()
        if ($verbose) {
            write-Log "Initiating Send."
        }
        $requestStream = $soapWebRequest.GetRequestStream()
        $SOAPRequest.Save($requestStream)
        $requestStream.Close()
        if ($verbose) {
            write-Log "Send Complete, Waiting For Response."
        }
        $resp = $soapWebRequest.GetResponse()
        $responseStream = $resp.GetResponseStream()
        $soapReader = [System.IO.StreamReader]($responseStream)
        $ReturnXml = [Xml] $soapReader.ReadToEnd()
        $responseStream.Close()
        if ($verbose) {
            write-Log "Response Received."
        }
        return $ReturnXml
}

<# Get ***REMOVED*** About Information #>
    $SoapActionEAAbout = "http://tempuri.org/ISystemManagement/***REMOVED***"
    $soapEAAbout = [xml]@"
    <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
    <s:Body>
        <GetAboutInformation xmlns="http://tempuri.org/"/>
    </s:Body>
    </s:Envelope>
"@


# Connect to ***REMOVED*** and pull records to work with
Try {
    # create connection to MySQL
    # you may need this: http://dev.mysql.com/downloads/connector/net/
        [System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") | Out-Null
    # objects
        $conn = New-Object MySql.Data.MySqlClient.MySqlConnection
        $dataset = New-Object System.Data.DataSet
    # connection
        $conn.ConnectionString = "server=$($strhost);port=***REMOVED***;uid=$($struser);pwd=$($strpass);database=$($strDB)"
        $conn.Open()
    # execute the command
        $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $conn)
    # create a table and fill with results
        $adapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)
        $adapter.Fill($dataset, "rs")|Out-Null
    # display all data from the query
      #$dataset.Tables[0]|ft -AutoSize
    # unique node count
      $cntUniqueNodes = ($dataset.Tables[0]|Select-Object -ExpandProperty "roc_hostname"|Sort-Object | Get-Unique | Measure-Object).Count

}
Catch {
    Write-Log "An error occurred: $($_.Exception.Message)" "E"
}
Finally {
    $conn.Close()
}


# create log file header
    Add-Content $strLog ***REMOVED***
	***REMOVED***
	***REMOVED***
# create a counter
    $cnt = 1
    $cntTotal = $dataset.Tables[0].Rows.Count.ToString()
# create array to store successful roc_hostnames in
    $arrRocHosts = @()
# write start time of sweep
    $sweepStart = Get-Date
    Write-Log "[x/$($cntTotal)] Starting sweep at $($sweepStart)"
# loop through the recordset
Try {
    foreach ($device in $dataset.Tables[0]) {
        $ret = $null
        #only try to connect if the device hasn't been successfully hit
        if ($arrRocHosts -notcontains $device.roc_hostname.ToString()) {
            #make sure the ***REMOVED*** ip is not null (SQL query should have done this)
            if ($device.rocip.ToString()) {
                #set to vars and trim everything (***REMOVED*** fields seem to sometimes contain spaces)
                    [string]$devuser           = ($device.user.ToString()).Trim()
                    [string]$devpass           = ($device.password.ToString()).Trim()
                    [string]$devrocip          = ($device.rocip.ToString()).Trim()
                    [string]$devdomain         = ($device.domain.ToString()).Trim()
                    [string]$devrochostname    = ($device.roc_hostname.ToString()).Trim()
                    [string]$devsite           = ($device.site.ToString()).Trim()
                    [string]$devequipgroupdesc = ($device.equipmentgroupdescription.ToString()).Trim()
                #set the domain if not part of the db query
                if ($devdomain -eq "" ) {
                    $devdomain = "."
                }

                Write-Log "[$($cnt)/$($cntTotal)] connecting to $($devrochostname) ($($devrocip)) using $($devdomain)\$($devuser)"
                $urlManagement = "http://$($devrocip)/***REMOVED***/***REMOVED***"
                Try {
                    [System.Array]$pingOutput = Ping-Node $devrocip
                    #make output easier to read
                    If ($pingOutput.Contains($true)) {
                        $pingOutput = "Yes"
                    }
                    Else {
                        $pingOutput = "No"
                    }
                    $ConnErrMsg = $null
                    $ret = Execute-SOAPRequest $soapEAAbout $SoapActionEAAbout $urlManagement $devuser $devpass $devdomain
                }
                Catch {
                    Write-Log "[$($cnt)/$($cntTotal)] An error occurred connecting to $($devrochostname) ($($devrocip)) as user $($devuser): $($_.Exception.Message)" "E"
                    $ConnErrMsg = $_.Exception.Message
                }

                # if the result is not empty work with the result
                If (!$ret.Envelope.Body.***REMOVED***.IsEmpty) {
                    #$ret.Envelope.Body.***REMOVED***.***REMOVED***
                    $ArchiveCount = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $BroadViewSupport = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $Build = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $DicomRetrieveSupport = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $HL7Support = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $IlmSupport = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $LicenseExpirationDate = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $LicenseStartDate = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $LicensedBy = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $LoadBalancingSupport = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $Product = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $RedundancySupport = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $StorageCapacityProcedures = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $StorageCapacityProceduresPerYear = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $TagMorphingSupport = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    $XdsSupport = $ret.Envelope.Body.***REMOVED***.***REMOVED***.***REMOVED***
                    Add-Content $strLog "$($devsite)^$($devrocip)^$($devrochostname)^$($pingOutput)^$($devequipgroupdesc)^$($Build)^$($LicenseExpirationDate)^$($ArchiveCount)^$($BroadViewSupport)^$($DicomRetrieveSupport)^$($HL7Support)^$($IlmSupport)^$($LicenseStartDate)^$($LicensedBy)^$($LoadBalancingSupport)^$($Product)^$($RedundancySupport)^$($StorageCapacityProcedures)^$($StorageCapacityProceduresPerYear)^$($TagMorphingSupport)^$($XdsSupport)^$($ConnErrMsg)"
                    if (!$ConnErrMsg) {
                        $arrRocHosts += $devrochostname
                    }
                }
                Else {
                    Write-Log "Unable to connect to $($devrochostname)($($devrocip)" "E"
                }

            }
        }
        # else for checking if $arrRocHosts contains the roc_hostname already
        Else {
            Write-Log "[$($cnt)/$($cntTotal)] Skipping $($devrochostname) ($($devrocip)), already connected successfully"
        }
        $cnt++
    }

}
Catch {
    Write-Log "[$($cnt)/$($cntTotal)] An error occurred connecting to $($devrochostname) ($($devrocip)) as user $($devuser): $($_.Exception.Message)" "E"
}

# write end time of sweep
    $sweepEnd = Get-Date
    $sweepDuration = New-TimeSpan -Start $sweepStart -End $sweepEnd
    Write-Log "[x/$($cntTotal)] Ending sweep at $($sweepEnd)"
    Write-Log "[x/$($cntTotal)] Successfully connected to $($arrRocHosts.Count) of $($cntUniqueNodes) unique nodes ($("{0:n0}" -f (($arrRocHosts.Count/$cntUniqueNodes)*100))%)"
    Write-Log "[x/$($cntTotal)] Execution Time: $($sweepDuration)"