Tailing a file in Windows

Powershell has a nice little function to let you tail files

Get-Content ./log.log -Wait -Tail 10

Get CPU/Core/HT Count

Several different methods available.

T-SQL:

SELECT 
  cpu_count AS NumberOfLogicalCPUs
, hyperthread_ratio
, ( cpu_count / hyperthread_ratio ) AS NumberOfPhysicalCPUs
, CASE
      WHEN hyperthread_ratio = cpu_count THEN cpu_count
      ELSE ( ( cpu_count - hyperthread_ratio ) / 
             ( cpu_count / hyperthread_ratio ) )
 END AS NumberOfCoresInEachCPU
, CASE
    WHEN hyperthread_ratio = cpu_count THEN cpu_count
    ELSE ( cpu_count / hyperthread_ratio ) 
    * ( ( cpu_count - hyperthread_ratio ) / 
            ( cpu_count / hyperthread_ratio ) )
  END AS TotalNumberOfCores
FROM sys.dm_os_sys_info

Powershell – Get Cores:

Get-WmiObject -namespace "root\CIMV2" -class Win32_Processor -Property NumberOfCores | select NumberOfCores

CMD Line:

wmic cpu get NumberofCores, NumberofLogicalProcessors

Useful Links:

http://weblogs.sqlteam.com/tarad/archive/2008/08/05/How-to-get-physical-CPU-count-on-a-server.aspx

http://www.pxserver.com/WinAudit.htm

Creating Self-Signing Certificates and assigning to Powershell scripts

If we take the assumption that you’ve just started out with powershell, you have probably seen this error when attempting to run a script:

File C:\scripts\test.ps1 cannot be loaded because the execution of scripts is 
disabled on this system. Please see "get-help about_signing" for more details.

Probably after some research and looking into about_signing you may have run the following in your Powershell editor:

Get-ExecutionPolicy

and found that your machine was set to be restricted. This policy prevents any scripts from being run in your environment. The next thing you probably did was set your execution policy to be Unrestricted, which then allows any scripts to be run without any security checks.

This would have solved your immediate problem of getting the script to run, however, to create more control over what can run and what cant, you can introduce certificates.

Self-Sign Certificates

To create Self-Signed ceriticates, the makecert.exe tool needs to be installed. This ships as part of the .NET Framework SDK (1.1 or higher) – i have 2.0 installed (found here)

To make the certificate:

C:\Program Files\Microsoft.NET\SDK\v2.0 64bit\Bin>makecert -n "CN=PowerShell Local Certificate Root" -a sha1 -eku 1.3.6.1.5.5.7.3.3 -r -sv root.pvk root.cer -ss Root -sr localMachine

Succeeded

C:\Program Files\Microsoft.NET\SDK\v2.0 64bit\Bin>makecert -pe -n "CN=PowerShell User" -ss MY -a sha1 -eku 1.3.6.1.5.5.7.3.3 -iv root.pvk -ic root.cer

Succeeded

After running these commands you will be prompted to provide passwords.  There are a number of ways to determine if the cert was created correctly including looking in the Certificated snap-in MMC (mmc.exe | add snap-in | certificates)

image

or by running the following PowerShell command (I have narrowed down gci to just return where the subject is Powershell as i have several different ceritifcates):

Get-ChildItem cert:\CurrentUser\My | Where-Object {$_.subject -match "Powershell"}

Directory: Microsoft.PowerShell.Security\Certificate::CurrentUser\My

Thumbprint                                Subject

———-                                ——————-

52F872D96D6A507846875CE83317C99F53E5C48F  CN=PowerShell User 

Sign the script

The last step is to sign the script using the newly created certificate:

$cert = @(Get-ChildItem cert:\CurrentUser\My -codesigning `
  | Where-Object {$_.subject -match "Powershell User"})[0]

  Set-AuthenticodeSignature "\\Path\myscript_New.ps1" $cert

SignerCertificate                         Status       Path

—————–                         ——       ——–

52F872D96D6A507846875CE83317C99F53E5C48F  Valid        myscript_New.ps1

Troubleshooting

If your having issues with signing the script then see this – UnknownError when using Powershell ISE to Set-AuthenticodeSignature

UnknownError when using Powershell ISE to Set-AuthenticodeSignature

This is discussed on Microsoft Connect here

When attempting to assign a certificate to a powershell script, it does not assign and gives a status of ‘UnknownError’

$cert = @(Get-ChildItem cert:\CurrentUser\My -codesigning | Where-Object {$_.subject -match "Powershell User"})[0]
Set-AuthenticodeSignature "\\path\myscript.ps1" $cert

SignerCertificate  Status          Path 
—————–  ——          —- 
                   UnknownError    myscript.ps1

This happens because when using Powershell ISE the default encoding is ‘Unicode Big Endian’

If the file is recreated using UTF-8 then the script is correctly assigned a certificate

#recreate the script into a new file
type "\\Path\myscript.ps1" | out-file "\\Path\myscript_New.ps1" -encoding utf8
$cert = @(Get-ChildItem cert:\CurrentUser\My -codesigning `
   | Where-Object {$_.subject -match "Powershell User"})[0]
#use the UTF-8 encoded file
Set-AuthenticodeSignature "\\Path\myscript_New.ps1" $cert

SignerCertificate  Status          Path 
—————–  ——          —-  
Cert_Thumbprint    Valid           myscript_New.ps1

Powershell – Retrieve SQL Server Info for all my Servers

Learning Powershell – Day 2

My aim in implementing powershell has always been for monitoring purposes.. to easily be able to retrieve a snapshot of my SQL Server estate by simply running a powershell script. Regardless of the SQL Query running in the powershell script, i wanted to achieve the following things:

  • Read a list of SQL Servers from a file
      • For each entry – Make a connection to SQL Server in Powershell (Discussed in more detail here)
      • For each entry – Run the same T-SQL query
      • For each entry – Do some error trapping around connectivity
      • Collate all the results into 1 table
      • Do some formatting
      • Either print the results to the screen or to a file

    The idea was that if i can incorporate these things into 1 script, then it would be easily transferrable to other more complex scripts. In essence, the aim was to create a template for future use.

    For the database connection, i am using smo and the get-credential cmdlet. I am feeding in a preset sql authenticated username and asking the user to provide the password.
    Patch_levels.ps1
    #Declare and set the file variables $filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" $inputfile = "$filelocation\ServerProperties.sql" $outputfile = "$filelocation\serveroutput.log" #Reset the arrays $results = @() $resultsout = @() #Preset the default database for invoke-sqlcmd $db = "master" #Supply the username and password (SQL Authentication popup) $server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server $server.ConnectionContext.LoginSecure=$false; $credential = Get-Credential -credential "DBA_Monitor" $userName = $credential.UserName -replace("\\","") $pass = $credential.GetNetworkCredential().password #Run the query for each server foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd -Database $db -ServerInstance $instanceName -U $username -P $pass -InputFile $inputfile #handle login failures trap { "Oops! $_ on server $instancename"; continue } } # print results $results | format-table -property Host, Instance, Version, Edition, 'Patch Level', SP, ServerType -Autosize #Uncomment below to send to file #$resultsout = $results | format-table -property Server, Instance, Edition, Version, SP -Autosize #$resultsout | out-file -filepath $outputfile

    The script uses the following serverproperties.sql file
    SELECT 
            SERVERPROPERTY('MachineName') as Host,
            SERVERPROPERTY('InstanceName') as Instance,
            SUBSTRING(@@VERSION, 1,25) as Version,
            SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
            SERVERPROPERTY('ProductVersion') AS 'Patch Level', 
            SERVERPROPERTY('ProductLevel') as SP, /* RTM or SP1 etc*/
            Case SERVERPROPERTY('IsClustered') 
            when 1     then 'CLUSTERED' else
                      'STANDALONE' end as ServerType

When run using the Windows Powershell ISE, the following happens:

1. Prompted for password

image

As the screenshot below shows, the login is disabled in 1 of the servers in the list, which creates an error trap. The other 2 servers work successfully and return the data

image

The formatting is done via the format-table cmdlet. I specify the columns to ensure a table is built, but also to make it easier to add and remove columns from the end result.

Although commented out in the script, there is an option to send the results to file. This is done via the out-file cmdlet.

Connecting to SQL Server through Powershell

Being a newbie to powershell, ive been researching and testing different connection options for powershell scripts. Ive come across the following ways:

      • Invoke-sqlcmd Windows Authentication
      • Invoke-Sqlcmd SQL Authentication
      • Invoke-Sqlcmd with Authentication Prompt
      • SQLConnection.connectionstring property (.NET)
      • SMO Connection with Authentication prompt
      • SMO Connection with hardcoded details

Invoke-sqlcmd Windows Authentication

Takes current windows connection details to try to do pass through authentication onto SQL Server

#Uses windows Authentication to traverse through instance list and return db count $filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" $results = @() foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd ` -Query "select SERVERPROPERTY('ServerName') as Server, count(*) as 'DB Count' from sys.databases" ` -ServerInstance $instanceName } # print results $results | Format-Table -autosize

Invoke-Sqlcmd SQL Authentication

#Uses sql Authentication to traverse through instance list and return db count $filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" $results = @() foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd ` -Query "select SERVERPROPERTY('ServerName') as Server, count(*) as 'DB Count' from sys.databases" ` -ServerInstance $instanceName ` -U "MyUsername" -P "password" } # print results $results | Format-Table -autosize

Invoke-Sqlcmd with Authentication prompt

This option uses the get-credentials cmdlet to bring up a prompt whereby connection details can be entered. These are then passed through to a connection method.

$filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" #resets the variable $results = @() # login using SQL authentication, which means we supply the username # and password $server.ConnectionContext.LoginSecure=$false; $credential = Get-Credential #the backslash is regular expression to remove \ (prompt will give it as default for domain) $userName = $credential.UserName -replace("\\","") #getnetworkcredential gives the passport unencrypted $pass = $credential.GetNetworkCredential().password $db = "master" foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd -U $userName -P $pass -Database $db -ServerInstance $instancename ` -Query "select SERVERPROPERTY('ServerName') as Server, count(*) as 'DB Count' from sys.databases" } # print results $results | format-table -Autosize

When the script is run, a popup is presented to the user:

image

If the credentials are correct, then the script returns how many databases are in each server in the instancelist.

SQLConnection.ConnectionString Property

The connection string and the query are fed into a SQLClient.SQLDataAdapter and the output formatted

foreach ($svr in get-content "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\serverlist.txt") { $con = "server=$svr;database=master;Integrated Security=sspi" $cmd = "SELECT SERVERPROPERTY('ServerName') AS Instance, SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP" $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con) $dt = new-object System.Data.DataTable $da.fill($dt) | out-null $dt | Format-Table -autosize }

SMO Connection with Authentication Prompt

# load assemblies [Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, ` Version=9.0.242.0, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, ` Version=9.0.242.0, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, ` Version=9.0.242.0, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, ` Version=9.0.242.0, Culture=neutral, `PublicKeyToken=89845dcd8080cc91") # connect to SQL Server named instance # use server\instancename $serverName = "Server\INSTANCE" $server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName" # login using SQL authentication, which means we supply the username # and password $server.ConnectionContext.LoginSecure=$false; $credential = Get-Credential $userName = $credential.UserName -replace("\\","") $server.ConnectionContext.set_Login($userName) $server.ConnectionContext.set_SecurePassword($credential.Password) # clear the screen cls # list connection string Write-Host "--------------------------------------------------------" Write-Host "Connection String : " Write-Host $server.ConnectionContext.ConnectionString Write-Host "--------------------------------------------------------"

Powershell: Invoke-Sqlcmd command not recognized

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, 
script file, or operable program. Check the spelling of the name, or if a path 
was included, verify that the path is correct and try again.

When trying to run the Invoke-sqlcmd command within the powershell utility, an error is returned suggesting the command is not recognized.

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "mysqlserver"

image

This happens because the Invoke-sqlcmd cmdlet is not included as part of PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). Before using invoke-sqlcmd you should install SSMS or the SQL Server 2008 Feature Pack

When sqlps has been installed, you may then load invoke-sqlcmd into powershell.exe by using the below script from Michiel Wories blog http://blogs.msdn.com/b/mwories/archive/2008/06/14/sql2008_5f00_powershell.aspx

Im still a novice at powershell so Ive ended up building the initiliaze into all my basic scripts using dot-source

# Use Dot-Source to Call the “Initialize-SQLPSEnvironment” Script. .{."\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\Initialize-SqlPsEnvironment.ps1"} $instanceNameList = Get-Content "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\ServerList.txt" foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd -Query "select SERVERPROPERTY('ServerName') As ServerName, Name, crdate from sysdatabases" -ServerInstance $instanceName } # print results $results

The above script calls the initialize script, and then does a basic SQL Query