The scripts herein are very powerful and can wreak havoc on a network / Domain if improperly implemented. Use carefully and test thoroughly in a development area before deploying any changes.
PowerShell SQL Update Computer
5) Finally we will have a Update Computer Script to allow a user to
update the services for a server already listed in the system. This
allows updates to the system, for example if IIS was installed;
these running services will need added to the SQL Table for that server.
The execution of the Update Computer Script will simply delete
all the service entries for the server and then add the current
running services to the SQL Database table.
#=====================================================
# Get Service Information and Export \ Import CSV
#=====================================================
#Get List of Servers
$UpdateServers = Get-Content C:\temp\Computers.txt
#Query Each Server for a List of Running Services
$UpdateResults=Invoke-Command -ComputerName $UpdateServers -ScriptBlock {Get-Service | Where{$_.Status -eq "Running"}}
#Export & Import Select Results
$UpdateResults | Select PSComputerName,Name, DisplayName | Export-csv C:\temp\UpdateServices.csv -NoTypeInformation
$UpdateImportData=Import-CSV C:\temp\UpdateServices.csv
#Popup to Assure you Want to Add to SQL
$ReturnValue2=[System.Windows.MessageBox]::Show('Would you like to update SQL Table?','Game input','YesNo','Error')
If($ReturnValue2 -eq "Yes"){
#Downloads and Installs SQL Module and Will Overwrite and Older Version Module Installed
Install-Module -Name SqlServer -AllowClobber
#List of Unique Servers
$UpdateServerList=$UpdateImportData.PSComputerName | Select-Object -Unique
#Da Loop
Foreach($UpdateComputer in $UpdateServerList){
# Delete Each Computer in List by Server Name
Invoke-Sqlcmd -ServerInstance Desktop-Main -ErrorAction SilentlyContinue -database ServerManager -Query "Delete From ComputerServices Where PSComputername = '$UpdateComputer'"
}
}
#Loop Through Each Record and Add to SQL Table
Foreach($UpdateComp in $UpdateImportData){
$insertquery2="
INSERT INTO [dbo].[ComputerServices]
([PSComputerName]
,[Name]
,[DisplayName])
VALUES
('$($UpdateComp.PSComputerName)'
,'$($UpdateComp.Name)'
,'$($UpdateComp.Displayname)')"
#Command to Insert to SQL
Invoke-Sqlcmd -ServerInstance Desktop-Main -database ServerManager -Query $insertquery2
}