2020 | Scott W. Head  |  Sr. Systems Admin | Certified Microsoft Windows Server Administrator

PowerShell Check for SQL & IIS Installs

<#  
    .SYNOPSIS
    Searches servers services for SQL and IIS installs

    .DESCRIPTION
    Queries Active Directory for Enabled Windows Systems
    Tests Windows Remote Access
    Connects and searches services fro SQL and IIS 
    Cleans results and Exports data to file
    Attaches files and sends in email 

    .NOTES
    Requires rights on remote machines
    Requires Active Directory Module           
#>


#======================================================
# Query Active Directory for Enabled Windows Systems
#======================================================

$Computers=Get-ADComputer -properties * -Filter * | Where{($_.OperatingSystem -like "Windows*") -and ($_.Enabled -eq $True)} | Select -ExpandProperty Name 

# Upper Case System names
$MyCAPComputers=$Computers.ToUpper()

# Instantiate Array
$MyComputersPassWinRM=@()

#====================================================================
# Loop through systems and check for Widows remote managerment access
#====================================================================

Foreach ($Comp in $MyCAPComputers)
{
    if ((Test-Connection -ErrorAction SilentlyContinue –ComputerName $Comp –Quiet –Count 1) –and ((Invoke-Command -ErrorAction SilentlyContinue –ComputerName $comp –ScriptBlock { 1 }) –eq 1))
    {
        $MyComputersPassWinRM+=$Comp
    }
}

#======================================================================
# Set Up SQL Service Check Command and Execute as Job - Export to File 
#======================================================================

$SQL_Services={get-service | where{$_.Displayname -like "SQL Server (*"}}
$SQL_JobID=Invoke-Command $MyComputersPassWinRM -ScriptBlock $SQL_Services -AsJob -ThrottleLimit 50 
Receive-Job -id $SQL_JobID.ID -Wait | Export-CSV 'C:\temp\SQLInstall_X.csv' 

#======================================================================
# Set Up IIS Service Check Command and Execute as Job - Export to File 
#======================================================================

$IIS_Services={get-service | where{$_.name -eq "W3SVC"}}
$IIS_JobID=Invoke-Command $MyComputersPassWinRM -ScriptBlock $IIS_Services -AsJob -ThrottleLimit 50 
Receive-Job -id $IIS_JobID.ID -Wait | Export-CSV 'C:\temp\IISInstall_X.csv'

#=======================================
# Import / Export - Cleanup SQL Dataset
#=======================================

$MySQLOutputfile = Import-CSV 'C:\temp\SQLInstall_X.csv' | Select PSComputername, Name, Displayname, Status
$MySQLOutputfile | Export-CSV -NoTypeInformation 'C:\temp\SQLInstall_Cleaned.csv' 

#======================================
# Import / Export - Cleanup IIS Dataset
#======================================

$MyIISOutputfile = Import-CSV 'C:\temp\IISInstall_X.csv' | Select PSComputername, Name, Displayname, Status
$MyIISOutputfile | Export-CSV -NoTypeInformation 'C:\temp\IISInstall_Cleaned.csv'

#====================================
# Create Array for Attachments
#====================================

$Attach=@()
$Attach+='C:\temp\SQLInstall_Cleaned.csv' 
$Attach+='C:\temp\IISInstall_Cleaned.csv'


#====================================
# Attach Files and Send Email 
#====================================

$smtpServer =  "xxxxxxx.COM" 
$strFrom = "Scripts-IIS-SQL_Install@yourdomain.com"
$strTo = "xxxx@xxxx.com"
$StrCC="xxxx@xxxx.com"
$strSubject = “IIS - SQL Install"
$Body="See Attched Files listing IIS & SQL Instances"
Send-Mailmessage -SmtpServer $SmtpServer -from $strFrom -Attachments $Attach -to $strTo -Cc $StrCC  -Subject $strSubject -BodyasHTML $Body