Tag Archives: powershell

Use SQL Server Central Management Server remotley through powershell

Ever wanted to run a scheduled powershell script that uses the CentralManagementServer to connect to all your SQL servers ?

After trying out a bunch of diffrerent solutions I found this piece of code on a Microsoft blog.

I have modified it and combined it with several other code pieces to get the result I wanted.
This code will run on towards the Central Management Server you havce setup, it will work from any other server
which has the following installed on it:

* Shared Management Objects
* SQLSysClrTypes
* PowershellTools

I had some initial problems to create the output file beacuse the SQLPS module dosen’t allow use of UNC paths as output,
there fore I had to use the New-PSDrive  command to map a driveletter.

To use the script chnage the pathc and file names to your liking,
change the CMS servername and change the name of you group in CMS .ServerGroups[ “All SQL servers” ] .
Here it is possible to add more levelsin the folder structure if you have organized your servers in prod, test and dev or similar.
Like this .ServerGroups[ “All SQL servers” ].ServerGroups[ “Test” ]

In this code I have just include code to get me the size of all databases on all servers in my CMS server.
But you could of course change that code to execute things or get any other information  out from your servers.
I have added some powershell formatting to get the size in GB and with no decimals.

$date=Get-Date -format "yyMMd"
#Need to map a drive letter since UNC paths are not supported with SQLPS
New-PSDrive -Name "G" -PSProvider FileSystem -Root "\\server\path" -Persist $file = "G:\SQL_dev_stats_$date.csv"

function GetSQLStats {
#Load SMO assemblies
$CentralManagementServer = "CMS-server\instancename"
$MS='Microsoft.SQLServer' @('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') |
foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}}

$connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

$My="$ms.Management.Smo" #
$CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].ServerGroups[ "All SQL servers" ].GetDescendantRegisteredServers() |
    Foreach-object {new-object ("$My.Server") $_.ServerName } | # create an SMO server object
       Where-Object {$_.ServerType -ne $null} | # did you positively get the server?
          Foreach-object {$_.Databases } | #logins for every server successfully reached

          # Put your Powershell SQL statements here
              Select-object @{Name="Server"; Expression={$_.parent}}, Name, @{Label="Size (MB)";Expression={"{0:N0}" -f $_.Size | foreach {$_.replace(",","")}} } |
          # End of your code here
########## Main Program ###########
GetSQLStats  | out-file $file #-notypeinformation -Delimiter ";"

Find unactive AD computer objects that are still enabled

Wants to know which computer in your AD is no longer used, but are still enabled in the AD. Schedule this script to run on a monthly basis and get an email with all computers not used in the last 365 days.

Import-Module ActiveDirectory

$date = get-date

$systems = Get-ADComputer –filter * -prop Lastlogondate,passwordLastSet,whencreated,enabled,OperatingSystem,description |
Where { $_.passwordLastSet –eq $null –or $_.passwordLastSet –lt (Get-Date).AddDays(-365) -and $_.lastlogondate –lt (Get-Date).AddDays(-365) -and $_.enabled -eq “TRUE” }|
select Name,
DistinguishedName |
export-Csv C:\temp\InactiveComputers.csv -notypeinformation -Delimiter “;”

function sendMail{

Write-Host “Sending Email”

#SMTP server name
$smtpServer = “mail.local”

#Creating a Mail object
$msg = new-object Net.Mail.MailMessage

#Attach output file
$file = “C:\temp\InactiveComputers.csv”
$att = new-object Net.Mail.Attachment($file)

#Creating SMTP server object
$smtp = new-object Net.Mail.SmtpClient($smtpServer)

#Email structure
$msg.From = “srv-0001@mail.local”
$msg.ReplyTo = “user@mail.local”
$msg.subject = ” Inactive computer accounts to be removed”
$msg.body = “This is the email Body.”

#Sending email

#Send mail

Xenserver stat script

This powershell script will give you number of virtual machines running on each xenserver in each xen server pool, it will also give you total amount of memory in each server and how much is free. For each poolmaster it will also give you the numer of stopped Vm’s.

Import-Module ‘C:\Program Files (x86)\Citrix\XenServerPSSnapIn\XenServerPSSnapIn.dll’
# Specify each pool master in the $XenServer array.
$XenServers =@(“srv-xen-0001″,”srv-xen-100″,”srv-xen-300”)
$date=Get-Date -format “yyMMd”
$file = “\c:\temp\Xen\XENStats_$date.csv”

function SendMail
# Send-MailMessage -To “someone@mail.com” -Subject “XEN Server Statistics” -From “xenserver@local” -Body “See attachment for the report” -SmtpServer “mail.local” -Attachments $file

function GetXenStats
foreach ($XenServer in $XenServers)
### Create connection to the PoolMaster
$connect=Connect-XenServer -Server $XenServer -UserName root -Password xenserver -NoWarnCertificates -NoWarnNewCertificates
### Ask the poolmaster which XenServers belongs to that pool
$XenHosts=Get-XenServer:Host -Server $XenServer

$HaltedVMs = Get-XenServer:VM -Server $XenServer| Where { $_.power_state -eq “Halted” -and $_.is_a_template -ne “True”}|fl name_label
$Halted = $HaltedVMs.Count


foreach ($XenHost in $XenHosts)
$hostname = $XenHost.hostname
$VMs = Get-XenServer:Host.ResidentVMs -Server $XenServer -Host $hostname
$VM_on_host = $VMs.Count – 1

# -Server is the poolmaster and -Host is the Xen-host to query
$metrics = Get-XenServer:Host.Metrics -Server $XenServer -Host $hostname
$mem_free = $metrics.memory_free
$mem_total = $metrics.memory_total

$temp=Disconnect-XenServer -Server $XenServer
$XenHosts = “”

########## Main Program ###########

$output|out-file $file

Check numer of running and stopped Vm’s in each resource group

Here is another powershell PowerCLI script that will give you the number of running and stopped Vm’s grouped by Cluster and Resource group.

# Add in the VI Toolkit goodness
if ( (Get-PSSnapin -Name VMware.VimAutomation.Core -ErrorAction SilentlyContinue) -eq $null ){Add-PSsnapin VMware.VimAutomation.Core}

$VCs =@(“srv-vc-0001″,”srv-vc-0002”)
$date=Get-Date -format “yyMMd”
$file = “c:\temp\VmwareStats$date.csv”

function GetVmwareStats
$report = @()
foreach ($VC in $VCs)
Connect-VIServer $VC | Out-Null

foreach ($Cluster in Get-Cluster)
#write-host $Cluster
$allcount = @()
foreach ($resourcepool in (Get-ResourcePool -Location $Cluster | Where {$_.Name -ne “Resources“ }))
$count = “” | Select Cluster,Hostname,ResourcePool, RunningVms, StoppedVms
$count.HostName = $vmHost.name
$count.Cluster = $Cluster.Name
$RunningsVms = Get-VM -Location $Cluster,$ResourcePool -NoRecursion | Where {$_.PowerState -eq “PoweredOn”}
$StoppedVms =Get-VM -Location $Cluster,$ResourcePool -NoRecursion | Where {$_.PowerState -eq “PoweredOff”}
$count.RunningVMs = $RunningsVms.Count
$count.StoppedVms = $StoppedVms.Count
$count.ResourcePool = $ResourcePool.Name
$allcount += $count
$allcount | Select Cluster, ResourcePool, RunningVms, StoppedVms
$report += $allcount


GetVmwareStats |export-Csv $file -notypeinformation -Delimiter “;”

Check you Vmware load

Ever wanted to check the average load of your ESXi servers. Check out this PowerShell PowerCLI script to get average CPU and memory load for your different clusters.

Will give you an output file like this:

HostName ClusterName MemMax MemAvg MemMin CPUMax CPUAvg CPUMin
srv-vm-0001.sirius.local DRS1-Test 72.11 1.96 1.73 29.96 0.25 0
srv-vm-0002.sirius.local DRS1-Test 77.05 60.29 1.14 67.5 11.852 0

# Add in the VI Toolkit goodness
if ( (Get-PSSnapin -Name VMware.VimAutomation.Core -ErrorAction SilentlyContinue) -eq $null ){Add-PSsnapin VMware.VimAutomation.Core}

$VCs =@(“vc_server_1″,”vc_server_2”)
$date=Get-Date -format “yyMMd”
$file = “c:\temp\vmware_load_$date.csv”

Function GetVmwareLoad
$report = @()
foreach ($VC in $VCs)

Connect-VIServer $VC | Out-Null

$allhosts = @()
foreach ($Cluster in Get-Cluster)
foreach($vmHost in ($cluster | Get-VMHost))
$hoststat = “” | Select HostName, ClusterName, MemMax, MemAvg, MemMin, CPUMax, CPUAvg, CPUMin
$hoststat.HostName = $vmHost.name
$hoststat.ClusterName = $Cluster.Name

$statcpu = Get-Stat -Entity ($vmHost)-start (get-date).AddDays(-30) -Finish (Get-Date)-MaxSamples 10000 -stat cpu.usage.average
$statmem = Get-Stat -Entity ($vmHost)-start (get-date).AddDays(-30) -Finish (Get-Date)-MaxSamples 10000 -stat mem.usage.average

$cpu = $statcpu | Measure-Object -Property value -Average -Maximum -Minimum
$mem = $statmem | Measure-Object -Property value -Average -Maximum -Minimum

$hoststat.CPUMax = $cpu.Maximum
$hoststat.CPUAvg = $cpu.Average
$hoststat.CPUMin = $cpu.Minimum
$hoststat.MemMax = $mem.Maximum
$hoststat.MemAvg = $mem.Average
$hoststat.MemMin = $mem.Minimum
$allhosts += $hoststat

$report += $allhosts |Select HostName, ClusterName, MemMax, MemAvg, MemMin, CPUMax, CPUAvg, CPUMin

########## Main Program ###########

$output| export-Csv $file -notypeinformation -Delimiter “;”

Powershell to disable unused computer AD accounts.

Here is a powershell script that can be used to cleanup old computer AD accounts:

Import-Module ActiveDirectory

$date = get-date

$systems = Search-ADAccount -ComputersOnly -AccountInactive -TimeSpan 365

foreach($computer in $systems){

$computer | select-object Name, OperatingSystem, DistinguishedName, LastLogonTimeStamp >> “C:\Scheduled Tasks\AD Cleanup\SystemInfo.csv”

$computer | disable-adaccount

$computer | move-adobject -targetpath “ou=Dormant Computers,dc=xxxx,dc=xxx”

write-host “$computer will be moved to Dormant computers”


First we load the Active Directory Module into Powershell. This has to be added as a Winodws Feature first.
Then we search the AD for all computer accounts which have been inactive for the last 365 days.
In the foreach loop we write the name of the server and LastLogonDate to a csv file to keep as a log.
Then we disable the account and move it to a OU where we keept disabled accounts.
Last we output a stsus message to the console.

Staffan Olofsson