top of page

PowerShell Server Baseline | Compare Script

Online Installation

Install-Module -Name SqlServer

Common CMDlets

Instance Management: Get-SqlInstance Connect-SqlInstance Database Management: Get-SqlDatabase New-SqlDatabase Remove-SqlDatabase Backup-SqlDatabase Restore-SqlDatabase SQL Agent Management: Get-SqlAgentJob New-SqlAgentJob Remove-SqlAgentJob

Steps to Load SqlServer Module from File

  1. Ensure you have the module files: Verify that you have the SqlServer module files on your local machine. The module folder should contain a .psd1 (module manifest) file and possibly other related files.

  2. Determine the path to the module: Identify the directory where the module files are stored. For example, if the SqlServer module files are located in C:\Modules\SqlServer.

  3. Import the module: Use the Import-Module cmdlet to load the module by specifying the path to the module manifest file.

Example Command

Assuming the SqlServer module is located at C:\Modules\SqlServer, you can import it using the following command:

Import-Module -Name "C:\Modules\SqlServer\SqlServer.psd1"

SQL Select & Compare-Object

2) You run script the Compare Script that will Compare the historical

data in SQL to the current list of services Running from servers to a file

called New_Services_Found_Running.txt

3) The Compare Script will also generate a report.txt of servers that were

not entered into the SQL database table on the initial run. This will allow

you to instantly recognize new servers not in this system yet. 

#=======================================================================
#Section to Get Current Running Services on List of Computers
#=======================================================================

#Get List of Online Accessible Servers | Can Change to Query AD if Desired
$CurrentServers = Get-Content C:\temp\ServerList.txt 

#Query Each Server for a List of Running Services
$CurrentResults=Invoke-Command -ComputerName $CurrentServers -ScriptBlock {Get-Service | Where{$_.Status -eq "Running"}}

#Export Select Results to File and for Reference File
$CurrentResults | Select PSComputerName,Name, DisplayName | Export-csv C:\temp\MyCurrnetServices.csv -NoTypeInformation 

#Import Data 
$ImportData=Import-CSV C:\temp\MyCurrnetServices.csv


#=============================================================================================================
#Section to Get Server Service History from SQL and compare to Current Running Services on Servers
#=============================================================================================================

#New Dir for File Output for Keeping a History
$DateForamt = Get-Date -Format("yyyy-MM-dd")
New-Item -Path "C:\Temp" -Name $DateForamt -ItemType "directory"
$Directory = "C:\Temp\$DateForamt"

#Downloads and Installs SQL Module and Will Overwrite and Older Version Module Installed
Install-Module -Name SqlServer -AllowClobber

#List of Unique Servers  
$ServerList=$ImportData.PSComputerName | Select-Object -Unique

#Da Loop
Foreach($ServerName in $ServerList){
   
# Command Query SQL by Each Server name
    $SQLData=Invoke-Sqlcmd -ServerInstance Desktop-Main -database ServerManager -Query "Select DisplayName from ComputerServices Where PSCOMputername = '$ServerName'"    
   
# Export & Import SQL Data to File
    $SQLData | Select -ExpandProperty DisplayName |  Out-File "$Directory\SQLDATA.txt"
    $CompareSQL = Get-content "$Directory\SQLData.txt"
        
# Export & Import Current Services to File by Looped ServerName
    $Names=$ImportData | Where{$_.PSComputerName -eq $ServerName} | Select -ExpandProperty DisplayName
    $Names | Out-File "$Directory\Name.txt"    
    $Names=Get-Content "$Directory\Name.txt" 

    #This Compare Shows items in  $Name (Master List) that are not listed in $CompareSQL
    $newSerivces=Compare-Object -ReferenceObject $Names -DifferenceObject $CompareSQL | ? {$_.SideIndicator -eq '<='} | Select -ExpandProperty inputobject

    #Create Output and Export to txt  tab delimited to import to csv   
    ForEach($NewService in $newSerivces){"$ServerName `t $NewService" | Out-File "$Directory\New_Services_Found_Running.txt" -Append}
}

#Pull Distinct Server Names for Compare to ServerList
$SQLComptuers=Invoke-Sqlcmd -ServerInstance Desktop-Main -database ServerManager -Query "Select Distinct PSCOmputername from ComputerServices" | Select -ExpandProperty PSComputername 
Compare-Object -ReferenceObject $ServerList -DifferenceObject $SQLComptuers | ? {$_.SideIndicator -eq '<='} | Select -ExpandProperty inputobject |  Out-File "$Directory\Report.txt"

PowerShell Service Screenshot
bottom of page