Using Powershell to install advertised OS Patches from SCCM software Center

#Get a list of server from DBA DW datatbase
$servername = invoke-sqlcmd -ServerInstance 2012eitsqltest -Database DBA_ServerDW `
-Query "select ServerName from DBA_ServerDW.dbo.SQLServers
where servername not like '%\% and'
and OSPatchServerMonthly = 1 and Decommissioned = 0
and Environment in('QA','UAT')"


foreach($server in $servername)

{

                $ServerN=$server.ServerName

             
#Compliance state of the software update that indicates if the software update is missing and needs to be installed.ComplianceState=0, 0 =ciNotPresent

$AvailableUpdates = Get-WmiObject -Class CCM_SoftwareUpdate -Filter ComplianceState=0 -Namespace root\CCM\ClientSDK -Computername $ServerN
              

# The following is done to do 2 things: Get the missing updates (ComplianceState=0)and take the PowerShell object and turn it into an array of WMI objects 

$AvailableUpdatesReformatted = @($AvailableUpdates | ForEach-Object {if($_.ComplianceState -eq 0){[WMI]$_.__PATH}}) 
              
                            
                
# The following is the invoke of the CCM_SoftwareUpdatesManager.InstallUpdates with our found updates 
# NOTE: the command in the ArgumentList is intentional, as it flattens the Object into a System.Array for us 
# The WMI method requires it in this format.


$InstallReturn = Invoke-WmiMethod -Class CCM_SoftwareUpdatesManager -Name InstallUpdates -ArgumentList (,$AvailableUpdatesReformatted) -Namespace root\ccm\clientsdk -Computername $ServerN
                 
                               
                                     
                                
}
  
  

foreach($server in $servername)

{

       $ServerN_Check=$server.ServerName


        # Set a variable to True
        $Allinstalledstatus = $True

#After installation is started ,get completion status of all updates installation by getting evaluationstate
#NOTE : When a software update is not in a progress state, the value of EvaluationState is none or available, depending on whether there was any progress at any point in the past
#if a software update was downloaded at activation time, the value of EvaluationState is none. 

While ($Allinstalledstatus -ne $false)
                
                        {
                
                    
 # Checking EvaluationState( 0=ciJobStateNone,2=ciJobStateSubmitted, 3=ciJobStateDetecting, 4=ciJobStatePreDownload, 5=ciJobStateDownloading, 6=ciJobStateWaitInstall, 7=ciJobStateInstalling, 11=ciJobStateVerifying)
#If Evaluation State is not False stay in while loop

$CCMUpdate = get-wmiobject -query "SELECT EvaluationState FROM CCM_SoftwareUpdate" -namespace "ROOT\ccm\ClientSDK" -Computername $ServerN_Check
                            if(@($CCMUpdate | where {$_.EvaluationState -eq 0 -or  $_.EvaluationState -eq 2 -or $_.EvaluationState -eq 3 -or $_.EvaluationState -eq 4 -or $_.EvaluationState -eq 5 -or $_.EvaluationState -eq 6 -or $_.EvaluationState -eq 7 -or $_.EvaluationState -eq 11 }).length -ne 0) { $Allinstalledstatus=$true } else { $Allinstalledstatus=$false }  
write-host $ServerN_Check
write-host $Allinstalledstatus
                
}
                                                    
#When installation is done, it returns False.

If($Allinstalledstatus -eq $false)
{
            
#Get EvaluationState of each patch, KB name and number, URL

$StatusList = (Get-WmiObject -ComputerName $ServerN_Check -Query 'SELECT EvaluationState,ArticleID,Name,URL,ErrorCode FROM CCM_SoftwareUpdate where ComplianceState = 0' -Namespace ROOT\ccm\ClientSDK);
                
                        foreach ($Status in $StatusList)
                        {
                            $InstalledStatus= $Status.EvaluationState
                            $KBDescription= $Status.ArticleID +' '+ $Status.Name +' '+ $Status.URL
                            $ErrorCode = $Status.ErrorCode
                                       
#Log the updates status to Log Table
                            invoke-sqlcmd -ServerInstance 2012eitsqltest -Database DBA_ServerDW `
                            -Query  "INSERT INTO SCCM_InstalledOSUpdates([ServerName],[KBDescription],[InstalledStatus],ErrorCode)
                            VALUES ('$ServerN_Check','$KBDescription','$InstalledStatus','$ErrorCode')"
                        
                        }

                        }

}    

Manage SQL server service on Linux(RHEL)

Check SQL server status

sudo systemctl status mssql-server

Start and Stop service

sudo systemctl stop mssql-server

sudo systemctl start mssql-server

sudo systemctl restart mssql-server

Access error log files

The SQL Server engine logs to the /var/opt/mssql/log

cd /var/opt/mssql/log

cat errorlog

Reset SA password

sudo systemctl stop mssql-server

sudo /opt/mssql/bin/mssql-conf setup

Basic Linux commands

Find out linux version

more /etc/*-release

How long the server is up?

uptime

How many cpus I have ?

lscpu

Last system reboot

who -b

Log off

Ctrl + d

print working directory

pwd

Change to home

cd $home

Create a directory

mkdir name

mkdir -p xx/yy/zz

Remove direcotry

rmidr name

List Directory

ls

ls -l  (long format)

Deleting files

rm filename

rm -i source/report.cpp

rm -r *  -- delete all the files

Process

ps -a 

kill processid

Grep

You can use the grep command to search for a specified pattern in a file or list of files.

The pattern used by the grep command is called regular expression, hence the strange

name of the command (Global Regular Expression Print).

$cat myfile

$grep WORK myfile

touch newfile -d 

CPU usage

top

Basic File Management

cp - copy

dd -image

mv - move and rename

rm - remove

ls - list

Remote Login

ssh -l root

ssh servername

Linux Performance

rpm –ql procps-ng

rpm – redhat package manager

ql – query list

proc ps  -ng (next generation)

rpm –ql  /usr/bin/top

What programs are available in user ?

rpm –ql procps-ng | grep  ‘^/usr/bin/’

Go to the process

cd /proc

Find my process

pwdx $$

Go to my process

cd /proc/$$

Generating Transaction Log script

Production transaction logs are backed up every 15 or 30 mins. We have a full database backup daily. When we need to restore a database point in time. We will need to restore transaction logs. It will take time to restore each transaction log if we have more than 20 transaction log a day.

I wrote the following script to generate Transaction log restore script which I can use to restore T log faster after full backup is restored. 

--***********Read this before running the script**************************************
-- The following script generates Transaction Log restore script.
-- If database crashed , we will need to restore a full database backup and Transaction logs.
-- After restoring a full database backup, we will need to restore all the good transaction logs which are backed up after the full back up is taken. 

-- This script will generates T Log restores scripts. We copy the scripts which is generated in message window.

-- Step1 . We will need to enable xp_cmdshell to 1 by running the following script one time only . After restore is done, we need to disbale xp_cmdshell

--sp_configure xp_cmdshell,1

--go

--reconfigure

--go

-- Step 2 . We need to change 3 variable values in the script, @BP,@BP2,@DatabaseName

-- @BP and @BP2 values are the Transaction Log backup location path and both values must be the same 

-- @DatabaseName is the name of the database that you are restoring

Declare @BP varchar(max)

Declare @GF varchar(max)

Declare @sql nvarchar(max)

--************************************************************

--Change the backup path of Transaction Logs here

--************************************************************

set @BP = 'C:\BackUp'

--************************************************************

--************************************************************

--construct sql for getting files

set @GF = ''' dir '+  @BP +'''' 

set @sql = 'EXEC MASTER..XP_CMDSHELL'+ ''+ @GF + ''

--Create a staging table

CREATE TABLE GetBackupFiles (Files VARCHAR(500))

INSERT INTO GetBackupFiles

Exec sp_executesql @sql

--Cleaning up the output from file list

--delete all directories

DELETE GetBackupFiles WHERE Files LIKE '%
%'

----delete all informational messages

DELETE GetBackupFiles WHERE Files LIKE ' %'

----delete the null values

DELETE GetBackupFiles WHERE Files IS NULL

--add FileDate column

ALTER TABLE GetBackupFiles add FileDate DateTime

GO

--split dateinfo

UPDATE GetBackupFiles SET FileDate =LEFT(files,20)

--get rid of dateinfo

UPDATE GetBackupFiles SET files =RIGHT(files,(LEN(files)-20))

--get rid of leading spaces

UPDATE GetBackupFiles SET files =LTRIM(files)

Declare @Databasename varchar(200)

Declare  @BP2 as varchar(max)

--*************************************************************

--Change the backup path here again same backup path as @BP

--Change the Databaes name here

--*************************************************************

set @BP2 = 'C:\BackUp'

Set @Databasename = 'MyDatabase'

--**************************************************************

--**************************************************************

-- Generate Restore script for Transaction Log

Declare @FN varchar(max)

Declare GetFileNames Cursor LOCAL for

--************************************************************************

--************************************************************************

-- Here we change the timestamp of the full database backup***************

--************************************************************************

--************************************************************************

select RIGHT(files,LEN(files) -PATINDEX('% %',files)) from GetBackupFiles

where FileDate >= '2017-05-24 23:45:00.000'

order by FileDate asc

--************************************************************************

--************************************************************************

Open GetFileNames;

Fetch GetFileNames into

@FN

WHILE @@FETCH_STATUS = 0

Begin

----Build Transaction Logs restore script

Print 'RESTORE DATABASE ' + @Databasename + ' FROM DISK = '''+ @BP2+@FN+' '+ ''' WITH NORECOVERY'

Fetch GetFileNames into

@FN

End

Close GetFileNames

Deallocate GetFileNames

--Drop staging table

Drop table GetBackupFiles

I gave credit to http://www.tek-tips.com/viewthread.cfm?qid=1278283

Thank you for reading.

Fix for “The target principal name is incorrect. Cannot generate SSPI context.”

After changing a service account running SQL server, I get an error when I connect to SQL server from Management Studio from my laptop or other servers. The error is below.

“The target principal name is incorrect.  Cannot generate SSPI context.”

To Fix this,

You need to register the service account in SPN.

MSSQLSvc/ Server1.domain.us.dom Domain\ServiceAccount

MSSQLSvc/ Server1.domain.us.dom:1433 Domain\ServiceAccount

After SPN is registered successful, the error is resolved. Thank you.

xp_logininfo

xp_logininfo 'Domain\ADGroup',members

xp_logininfo returns members of the AD group in SQL server. I use this command to check who has access to a database server in the AD group.

Renaming NIC , NetBios Setting, Disabling Protocols

The objective of powershell script is to automate renaming NIC, changing NetBios setting in Private and Public NIC and Disabling some protocols in Private NIC.

I created the following powershell scripts to automate the changes. The changes are necessary to set up AlwaysOn availability group severs in my process. Thank you for reading.

import-module Netadapter

Get-NetAdapter | ? status -eq 'up'| Get-NetIPAddress -ea 0 -AddressFamily IPv4 | Select InterfaceAlias, IPAddress

#Rename NIC Public 10.*
$PubNic=Get-NetAdapter | ? status -eq 'up'| Get-NetIPAddress -ea 0 -AddressFamily IPv4 | where IPAddress -like '10.*'|Select InterfaceAlias
$PubNicName= $PubNic.InterfaceAlias
$NewPublicNicName = "Public NIC"
Rename-NetAdapter -Name $PubNicName -NewName $NewPublicNicName

#Rename NIC Private 192.*
$PriNic=Get-NetAdapter | ? status -eq 'up'| Get-NetIPAddress -ea 0 -AddressFamily IPv4 | where IPAddress -like '192.*'|Select InterfaceAlias
$PriNicName= $PriNic.InterfaceAlias
$NewPrivateNicName ="Private NIC"
Rename-NetAdapter -Name $PriNicName -NewName $NewPrivateNicName


#In Public NIC, Enable NetBios over TCP
#0: Enable Netbios via DHCP.1: Enable Netbios on the interface.2: Disable Netbios on the interface.

$PubNicConfig = Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '10.*'}
$PubNicConfig.SetTcpipNetbios(1)
#Verify the changes
Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '10.*'}|Select IpAddress,Description,TcpipNEtbiosOptions |format-list


#In Private NIC, Enable NetBios over TCP
#0: Enable Netbios via DHCP.1: Enable Netbios on the interface.2: Disable Netbios on the interface.

$PriNicConfig = Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'}
$PriNicConfig.SetTcpipNetbios(2)
#Verify the changes
Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'}|Select IpAddress,Description,TcpipNEtbiosOptions |format-list


#Private NIC, Do Not Register this connection  address in DNS
$PriNicConfig = Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'}
$PriNicConfig.SetDynamicDNSRegistration($false,$false)  
# Verify Wins, Netbios setting
Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'} |Select * |format-list


#Disable Protocols Public and Private protocols

#Disable Client for Microsoft Network
Disable-NetAdapterBinding -Name 'Private NIC' -ComponentID ms_msclient
#Disable File and Printer Sharing for Microsoft Networks
Disable-NetAdapterBinding -Name 'Private NIC' -ComponentID ms_server
#Enable Ipv6 for both Private and Public NIC
Enable-NetAdapterBinding -Name 'Private NIC' -ComponentID ms_tcpip6
Enable-NetAdapterBinding -Name 'Public NIC' -ComponentID ms_tcpip6
#Verify the changes
Get-netadapterbinding -Name 'Private NIC'
Get-netadapterbinding -Name 'Public NIC'