Log function and colleague trust issues

2 for the price of 1 today. Ever created a function which worked perfectly fine, until a colleague started using it?
Ask them what they exactly entered and you get “Uhm, I don’t remember and I cleared the screen” and now you cannot reproduce the error.
Well, there’s a nice parameter in powershell called $PsBoundParameters. It contains all parameters for the function call.
Below you find the use of it, together with a nice logging function.
Before you get mad at me for using write-host instead of write-output, 1 word: colors!

Function Log-Script-Parameters()
{
    Param
    (
        [Parameter(Mandatory=$true)]
        $Parameters,
        [Parameter(Mandatory=$true)]
        $Function
    )

    Write-To-Log -Message "Script invoked by $($env:UserName)@$($env:UserDomain) on $($env:ComputerName)" -MessageLevel Informational
    Write-To-Log -Message "Function invoked: $($Function)" -MessageLevel Informational

    Foreach($GivenParam in $Parameters.Keys)
    {
        Write-To-Log -Message "$($GivenParam):$($Parameters.Item($GivenParam))" -MessageLevel Informational
    }
}

Function Write-To-Log()
{
    Param
    (
        [Parameter(Mandatory=$true)]
        [string]$Message,
        [Parameter(Mandatory=$true)]
        [ValidateSet('Informational','Warning','Error')]
        [string]$MessageLevel
    )
    $line = $MessageLevel + ";" + $Message + ";" + (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
    Switch ($MessageLevel)
    {
        "Informational"{write-host $Message -ForegroundColor White}
        "Warning"{write-host $Message -ForegroundColor Yellow}
        "Error"{write-host $Message -ForegroundColor Red}
    
    }
    If(!($global:LogFileLocation))
    {
        Fill-LogPath-Variable
    }
    $line | Out-File -FilePath $global:LogFileLocation -Append
}

Function Fill-LogPath-Variable()
{
    $FullDate = Get-Date
    $FiledateFormatDate = Get-Date -Format FileDate

    $RootFolder = Split-Path -Path $PSCommandPath
    $global:LogFileLocation = $RootFolder + "\logs\" + (Get-Item $PSCommandPath).Name + "_" + $FiledateFormatDate + "_" + $FullDate.Hour + "h" + $FullDate.Minute + ".log"
}

Log-Script-Parameters -Parameters $PsBoundParameters -Function "MyCustomFunction"

Find next free ip

Today a short intro: you want to find the next (or all) free ip in a certain range. Below function will accomplish this for you.

Function Get-FreeIP()
{
    Param
    (
        [Parameter(Mandatory=$true)]
        [ipaddress]$Start,
		[Parameter(Mandatory=$true)]
        [ipaddress]$End,
        [Parameter(Mandatory=$false)]
        [switch]$All
    )
    $IPStart = Calculate-IP-Int-Value -IP $Start
    $IPEnd = Calculate-IP-Int-Value -IP $End
    $IPCurrent = $IPStart

    If($All)
    {
		write-Host "This might take a while" -ForegroundColor Cyan
        $FreeIPs = @()
    }

    while(($IPCurrent -ge $IPStart) -And ($IPCurrent -le $IPEnd))
    {
        $Address = [IPAddress]$IPCurrent.ToString()
        $Ping = New-Object System.Net.Networkinformation.ping
        $PingResult = $Ping.Send($Address, 1)
        If($PingResult.Status -Ne "Success")
        {
            If($All)
            {
                $FreeIPs +=  $Address.IPAddressToString
            }
            Else
            {
                Return $Address.IPAddressToString
            }
        }
        $IPCurrent ++
    }
    If($All)
    {
        Return $FreeIPs
    }
}

Function Calculate-IP-Int-Value()
{
    Param
    (
        [Parameter(Mandatory=$true)]
        [IPAddress]$IP
    )
    $IPSplit = $IP.ToString().Split(".")
    $IPInt = 256*256*256*$IPSplit[0] + 256*256*$IPSplit[1] + 256*$IPSplit[2] + $IPSplit[3]
    Return ([long]$IPInt)
}

Trigger SCCM updates

After another long period of not doing anything for this sideproject, finally a new post.

This time I’ll try to help the SCCM guys and more specific those who are responsible for the updates of the servers. Some servers require updates to be installed during a specific timeframe, which is not really an option in SCCM. The way I did it might not be the best but it sure was better than any other solution we could come up with.
We make the updates available for the servers but make them not automatically install. This way they get downloaded and are ready to install inside software center. There you can trigger the updates manually.

This however is not the laziest way I could come up with so I scripted it.
The script is being run on a management server which can remote (invoke-command) on the target servers. This is a big requirement but it pays of.
So we start by listing all servers in the SCCM collection which has the updates ready. This can also be partial group in SCCM or another way of server input, like parameters, inputfile, adgroups, …
If you are using the SCCM group like we did, this is a way to list them. Be aware SCCM cmdlets cannot be run via script other than directly on the SCCM server itself hence using wmi.

Function Get-SCCMCollectionMembers($SCCMServer,$SiteCode,$SCCMCollection,$Credentials)
{
    Invoke-Command -ComputerName $SCCMServer -Credential $Credentials -ScriptBlock `
    {
        $SiteCode = $args[0]
        $CollectionName = $args[1]
		$SMSClients = New-Object System.Collections.ArrayList
        $Collection = Get-WmiObject -NameSpace "ROOT\SMS\site_$SiteCode" -Class SMS_Collection | where {$_.Name -eq "$CollectionName"}

        If($Collection)
        {
		    $SMSClientsWithoutFQDN = Get-WmiObject -Namespace  "ROOT\SMS\site_$SiteCode" -Query "SELECT * FROM SMS_FullCollectionMembership WHERE CollectionID='$($Collection.CollectionID)' order by name"
		    Foreach($SMSClient in $SMSClientsWithoutFQDN)
		    {
			    $wmireturn = Get-WmiObject -Namespace "ROOT\SMS\site_$SiteCode" -Query "SELECT * FROM SMS_Resource WHERE ResourceID = '$($SMSClient.ResourceID)'"
			    [void]$SMSClients.Add($wmireturn)
		    }
	        Return $SMSClients
        }
    } -ArgumentList $SiteCode,$SCCMCollection  -ErrorAction Stop
}

Next you might want to reboot to get rid of pending reboots in case some update depend on it. This can easily be done by this function.

Function Trigger-Reboot-If-Pending($UpdateServer,$Credentials)
{
    $invocationreturn = Invoke-Command -ComputerName $UpdateServer -Credential $Credentials -ScriptBlock `
    {
        $rebootrequired = $false
        If(Get-ChildItem "HKLM:\Software\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending" -EA SilentlyContinue)
        {$rebootrequired = $true}
        ElseIf(Get-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired" -EA SilentlyContinue)
        {$rebootrequired = $true}
        ElseIf(Get-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager" -Name PendingFileRenameOperations -EA SilentlyContinue)
        {$rebootrequired = $true}
        Else
        {
	        $util = [wmiclass]"\\.\root\ccm\clientsdk:CCM_ClientUtilities"
	        $status = $util.DetermineIfRebootPending()
	        If(($status -ne $null) -and $status.RebootPending)
	        {
		        $rebootrequired = $true
	        }
        }
        If($rebootrequired)
        {
            Restart-Computer -Force
        }
    } -ErrorAction Stop
}

Next we will actually trigger the updates, nothing more, nothing less.

Function Trigger-WindowsUpdates($UpdateServer,$Credentials)
{
    $invocationreturn = Invoke-Command -ComputerName $UpdateServer -Credential $Credentials -ScriptBlock `
    {
            $PendingUpdates = (get-wmiobject -query "SELECT * FROM CCM_SoftwareUpdate" -namespace "ROOT\ccm\ClientSDK") | Foreach{$_.Name}
            $dontshow = ([wmiclass]’ROOT\ccm\ClientSDK:CCM_SoftwareUpdatesManager’).InstallUpdates([System.Management.ManagementObject[]] (get-wmiobject -query "SELECT * FROM CCM_SoftwareUpdate" -namespace "ROOT\ccm\ClientSDK"))
            Return $PendingUpdateseturn "ERROR"
    } -ErrorAction Stop
}

We will end with a reboot.

Function Trigger-Reboot($UpdateServer,$Credentials)
{
    $invocationreturn = Invoke-Command -ComputerName $UpdateServer -Credential $Credentials -ScriptBlock `
    {
        Restart-Computer -Force
    } -ErrorAction Stop
}

So these are the separate functions without any error handling. I need to think about job security once in a while, don’t I 😉

Adding NTFS permissions on a folder

Because I started slacking already after my first post and because the last post was actually a rather simple function, I’ll add another simple function to make up for it.

This function will add AD group on a certain folder with (in this example) modify permissions.
You might also add an AD user instead of a group (but a good administrator might not like this :)) or change it to other permissions like read or full control.

Function AddNTFSPermissions($Folder, $ADGroup, $Permission)
{
	$objAcl = Get-Acl $Folder
	#SetAccessRuleProtection(block inheritance,keep existing permissions)
	$objAcl.SetAccessRuleProtection($False, $True)

	$objRule = New-Object System.Security.AccessControl.FileSystemAccessRule($ADGroup, "Permission", "ContainerInherit, ObjectInherit", "None", "Allow")
	$objAcl.AddAccessRule($objRule)
	Set-Acl $Folder $objAcl
	writeToLog("|--- $($ADGroup): Added NTFS permissions on $($Folder)")
}
AddNTFSPermissions -Folder "\\contoso.com\HR\contracts\" -ADGroup "HR Admins" -Permission "Modify"

A full list of permissions possible can be found here:
https://msdn.microsoft.com/en-us/library/system.security.accesscontrol.filesystemrights(v=vs.110).aspx

Of course I know there are other ways which might be better like icalcs but sometimes there might be a reason to just use Powershell.

Query SQL without a SQL module

Today a very simple function to remotely query a sql database without using a sql module or remoting on the SQL server with integrated security.

Function Invoke-SQL($Instance, $Database, $Query)
{

	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = $Instance; Database = $Database; Integrated Security = True"

	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText = $Query
	$SqlCmd.Connection = $SqlConnection

	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$SqlAdapter.SelectCommand = $SqlCmd
	$SqlAdapter.SelectCommand.CommandTimeout = 720

	$DataSet = New-Object System.Data.DataSet
	$SqlAdapter.Fill($DataSet) | Out-Null

	$SqlConnection.Close()

	Return $DataSet.Tables[0]
}

Of course this function can be broken down in case this doesn’t fit your needs.
You might need to enter credentials (eg Azure SQL, …) or you might be firing lots of queries on the database. In this case you might want to keep the connection open instead of opening and closing it with each query.

Find unused portgroups in VCenter

Let’s start with a VMware based script. This script will get all unused portgroups configured on non distributed vswitches.

This script will first connect to vcenter, get all configured portgroups on all ESX hosts, get all configured portgroups on all vms and compare those. All configured portgroups will be counted on the hosts and at the end only the unused portgroups will be shown.

$VCenter = "yourvcenterserver"
$PortGroupUsage = [System.Collections.ArrayList]@()

Add-PSSnapin *Vmware*
Connect-Viserver -server $VCenter -WarningAction SilentlyContinue

$portGroupsCluster = Get-VirtualPortGroup
$portGroupsClusterCleaned = $portGroupsCluster | Select -Uniq
$portGroupsClusterCleaned = $portGroupsClusterCleaned | Sort-Object Name

$views = Get-View -ViewType VirtualMachine
$portGroupsVM = ($views.Config.Hardware.Device.DeviceInfo | Where{$_.Label -Like "Network adapter*"}).Summary | Sort-Object

Foreach($PortGroup in $portGroupsClusterCleaned)
{
$PortGroupCountObject = New-Object –TypeName PSObject
$PortGroupCountObject | Add-Member –MemberType NoteProperty –Name Name –Value $PortGroup.Name
$PortGroupCountObject | Add-Member –MemberType NoteProperty –Name VLanId –Value $PortGroup.VLanId
$PortGroupCountObject | Add-Member –MemberType NoteProperty –Name Key –Value $PortGroup.Key
$PortGroupCountObject | Add-Member –MemberType NoteProperty –Name ServerCount –Value (($portGroupsVM | Group-Object) | Where {$_.Name -Eq $PortGroup.Name}).Count
$PortGroupCountObject | Add-Member –MemberType NoteProperty –Name HostCount –Value (($portGroupsCluster | Group-Object) | Where {$_.Name -Eq $PortGroup.Name}).Count
[void]$PortGroupUsage.Add($PortGroupCountObject)
}

$PortGroupUsage | Where{$_.ServerCount -Eq 0}

Introduction

Hi guys,

Let’s start with telling you who I am.

I started working as a support engineer in 2011. As I got more experienced over the years I can call myself a senior system engineer after a few years and lots of interesting challenges at my company.

As we are a Microsoft partner I got experience in lots of Microsoft technologies combined with VMWare and Citrix products. Of course with the current growth of Microsoft Azure and all services it offers, the last 2 will get less important in the future.

As a (trying to be) good engineer, a lot of my time goes to scripting as I am lazy in a certain way. Why click 25 times when you can write 5 lines of code? This is where I have to thank Microsoft for making it easy by providing the perfect scripting language, Powershell.

This is also what my blog will be about. Short but useful powershell scripts for various technologies.