## QuickTip: PowerShell scripting – How long did it take to run the script?

Have you ever wondered how long it took to run your script? Well, you dont need to wonder anymore. The following couple of lines will provide a visual output how long it take to execute your script from start to finish

$Start = [system.datetime]::Now { Script run…. }$End = [system.datetime]::Now
$resulttime =$End – $Start Write-Host: (‘Execution Time : {0}Days:{1}Hours:{2}Minutes:{3}Seconds’ -f$Resulttime.Hours, $Resulttime.Days,$Resulttime.Minutes, $Resulttime.Seconds) Regards The Author – Blogabout.Cloud ## Discovering Distribution Lists using PowerShell Do you have a requirement to understand how many Distribution Lists exist within your Exchange organization or need to understand if they actually being utilized? Well, this is something I have come across recently while working for the customer. They have a mass of distribution lists across their organization which they are trying to tidy up before migrating to Office 365. The organisation I was working for had over 100,000 distributions list but the state of them was unknown so what challenges did I face? #### The challenges faced • Unknown the number of DLs that had 0 members • Unknown the number of DLs that had 0 managers • Unknown the number of DLs that had invalid characters #### The solution… PowerShell So the following script was created to obtain the all the attributes listed before, this enable to put together a business case for which distribution lists should be deleted and which should be migrated. • Distribution List Name • SamAccountName • GroupType • DistinguishedName • Managedby • memberdepartrestriction • memberjoinrestriction • primartysmtpaddress • Number of Members [CmdletBinding()] param() # Call Distribution Lists$dist = @(Get-DistributionGroup -resultsize unlimited)
# Start Transcript
Start-Transcript -Path $env:USERPROFILE\desktop\transcript.txt # Report on Distribution List foreach ($dl in $dist) {$count =@(Get-DistributionGroup $dl.samaccountname).count$report = New-Object -TypeName PSObject
$report | Add-Member -MemberType NoteProperty -Name 'Group Name' -Value$dl.Name
$report | Add-Member -MemberType NoteProperty -Name 'samAccountname' -Value$dl.samaacountname
$report | Add-Member -MemberType NoteProperty -Name 'Group Type' -Value$dl.grouptype
$report | Add-Member -MemberType NoteProperty -Name 'DN' -Value$dl.distinguishedName
$report | Add-Member -MemberType NoteProperty -Name 'Manager' -Value$dl.managedby
$report | Add-Member -MemberType NoteProperty -Name 'Member Depart Restriction' -Value$dl.memberdepartrestriction
$report | Add-Member -MemberType NoteProperty -Name 'Member Join Restriction' -Value$dl.memberjoinrestriction
$report | Add-Member -MemberType NoteProperty -Name 'PrimarySMTPAddress' -Value$dl.primartysmtpaddress
$report | Add-Member -MemberType NoteProperty -Name 'Number of Members' -Value$count
Write-Host ('INFO: {0} has {1} members' -f $dl.name, ($count))
$reportoutput +=$report
}
# Stop Transcript
Stop-Transcript
# Report
$reportoutput | Export-Csv -Path$env:USERPROFILE\desktop\DistributionListReport.csv -NoTypeInformation -Encoding UTF8

Regards

## Merging Excel files using PowerShell, yes it can be done.

Have you ever worked with Excel files where you wanted to match and compare Columns/Rows? In the past, this has been quite difficult tasks to achieve using the native commands within PowerShell.

So have you heard of the PowerShell module ImportExcel?

It’s is a PowerShell module that is available on the PowerShell Gallery and introduces a number of functions that allow you to work with Excel files using the good old blue background.

From this module we will be working with the following function;

• Merge-Worksheet

Syntax
Merge-Worksheet [-Referencefile] [-Differencefile] [[-WorksheetName] ] [-Startrow ]
-Headername [[-OutputFile] ] [[-OutputSheetName] ] [-Property ] [-ExcludeProperty ]
[-Key ] [-KeyFontColor ] [-ChangeBackgroundColor ] [-DeleteBackgroundColor ]
[-AddBackgroundColor ] [-HideEqual] [-Passthru] [-Show] [-WhatIf] [-Confirm] []

## Example usage of Function

The below shows the Reference and Difference Excel files that are being used in this example. I am going to merge the two excel files based on Column A the EmployeeNumber. During my testing, I have had issues using -HeaderName parameter. In this post I will not be specifying the headings and just modify the output file.

# Variables
$ref = “$env:USERPROFILE\desktop\test\ref.xlsx”
$dif = “$env:USERPROFILE\desktop\test\dif.xlsx”
$out = “$env:USERPROFILE\desktop\test\out.xlsx”

# Script Block

get-mailbox -resultsize unlimited | where {$_.emailaddress -like "*@blogabout.cloud"} | Measure-Object Regards, The Author – Blogabout.Cloud ## Working with Active Directory Attributes with multi-values. It is common for organisations to use or create Active Directory Attributes that may contain multiple different values and when trying to obtain the information using PowerShell you might receive Microsoft.ActiveDirectory.Management.ADPropertyValueCollection Which isn’t helpful to man or beast. However, I have been recently working with custom attributes so its time to share my experiences once again. In this post I will be working with information that is located within my personal lab, where I have customattribute10 defined with O365. # Command Get-ADUser -Properties * -Filter * | Select-Object samaccountname,customattribute10 | export-csv -Path$env:USERPROFILE\desktop\test1.csv

As you can see that from the above I am not receiving the desired output from Get-ADUser. So lets use a PowerShell string that obtains the required information

Let’s discuss the below string in detail to explain what each part does

@{name=” customattribute10 ”;expression={$_. customattribute10}} The @ symbol, is the property you are retrieving is an array, which means it contains multiple values. Then you gave the property a name/label (you can name it anything you like). This will be the header of the column in the CSV file @{name=” customattribute10 ”; Then you provide an expression; this is the script block where you tell the PowerShell cmdlet what you are trying to fetch. For example; we want to fetch the values for the customattribute10 attribute. expression={$_. customattribute10}}

So, now we understand the require array to pull the multi-values from lets execute the below command

# Command
Get-ADUser -Filter * -Properties proxyaddresses,customattribute10 | select samaccountname, @{L='customAttribute10'; E={$_.customAttribute10}} | Export-Csv -Path$env:USERPROFILE\desktop\test.csv

Now executing this command you will receive the correct output from the attribute which you desired.

Regards

## Working with Active Directory using Get-ADUsers

When working with Active Directory Users sometimes its a lot easier using PowerShell to obtain all the information you require from your environment. As a Consultant I have lost count how many times I’ve used PowerShell to get information out of Active Directory and its essential to your skill set.

The most simple and effective way by running the following command, as it will dump all Active Directory Users and their properties to a CSV file located on your desktop

# Command
Get-ADUser -Filter * -Properties * | Export-CSV $env:userprofile\desktop\ADExport.csv or # Command Get-ADUser -Filter * | Export-CSV$env:userprofile\desktop\ADExport.csv

What if you only require bits of information? The command only targets the Name and SamAccountName Field. Simple right?

# Command
Get-ADUSer -Filter * -Properties Name,SamAccountName | Export-CSV $env:userprofile\desktop\ADExport.csv or # Command Get-ADUSer -Filter * -Properties * | Select-Object -Property Name,SamAccountName | Export-CSV$env:userprofile\desktop\ADExport.csv

The possibilities are endless, you can call all everything from the below table because it exists on the AD object by default. If you have used ExtensionAttributes or CustomAttributes you can also call these as well by adding them to your filter.

Regards

## Dealing with SQL AlwaysOn in Skype for Business Server 2015/2019 Powershell Style

Dealing with a Skype for Business deployment with SQL AlwaysOn isn’t an easy task as there are a number of different elements involved from ensuring your databases are in the correct configuration to security permissions across the nodes.

I did originally create the following script while doing a customer deployment and have improved/modified to ensure I take out most of the headaches involved when deploying your backend databases.

The script runs through the following actions;

• Check if SQL Instance
• Check if Failover Clustering Role in configured on the two SQL servers
• Ask to configure Failover Clustering Role (If required)
• Convert and backup all Skype for Business Databases
• Detect Skype for Business Folder on source SQL Server and copy to secondary
• Configure SQL Server Service for AlwaysOn
• Repeat task manually on the secondary server

Complete your AlwaysOn Configuration using the SQL Management Studio

## Change Log

Version 1.1 – Features

• Support Skype for Business 2019
• Support for SQL Server 2014 Enterprise, SQL Server 2016 Enterprise

## Reporting Issues

If you identity any issues within running the script please email theauthor@blogabout.cloud

Regards

## Office ProPlus ToolKit

Installing Office 365 ProPlus can be a bit of a headache and also there are many different tricks/hacks which can be implemented to test out the latest channels.

The Office ProPlus ToolKit script is built with following options to help you test and deploy ProPlus in the most effective way. This script is built with the following menu options

1) Configure Monthly Channel –>
2) Configure Semi Annual (Targeted) Channel –>
3) Configure Semi Annual Channel –>
4) Configure Monthly (Targeted) Channel –>
5) Configure Insider (Unsupported) Channel –>
6) Check your Office 365 ProPlus Configuration –>

20) Install Office 365 ProPlus –>
21) Install SQL Express –>
22) Install SQL Management Studio –>
23) Install Office Telemetry Dashboard –>

30) Build your own configuration.xml (config.office.com) –>
31) Install Office using your modified configuration.xml –>

This script has seen a number of updates recently which have been logged below

## Change Log

Version 1.1 – Features

• Support for PowerShell Version 5 – This script using Version 5 to expand Zip archives which allows the script to call the contents. If Version 5 is not detected you will be prompted to extract the required files manually.

## Reporting Issues

If you identity any issues within running the script please email theauthor@blogabout.cloud

Regards

## PowerShell – Filtering your PowerShell outputs using Where-Object and Select-Object

When working with PowerShell and using a (get-command | fl or format-list) you will receive a whole list of information which sometimes can be difficult to digest as shown below. In most cases normally you are only after one or two pieces of key information.

## Using Select-Object

Select-Object is a great command when filtering PowerShell output by a particular property example Get-Service and Name/Status

# Command
Get-Service | Select-Object -Property Name,Status

This command isnt limited to just Get-Service and can be used across all Microsoft workloads for example you wanted to see UserPrincipleName and O365 licence, Select-Object can help you achieve that.

## Using Where-Object

Where-Object is a powerful option when filtering PowerShell output by a particular value, for example, Get-Service state based on stopped services

# Command

Get-Service | select -Property Name,Status | where {$_.Status -like 'Stopped'} Where-Object doesn’t just stop there, what if you just wanted all the Stopped Service begining with the letter C? # Command Get-Service | select -Property Name,Status | where {($_.Name -like 'C*') -and (\$_.Status -like 'Stopped')

Again where-object isnt just limited to this command, I have just both commands in PowerShell scripts I have written for a customer to achieve a desired state.

Now go and try this commands within on your computer or organisation and see what you automate.

Regards

## PowerShell – How to format your PowerShell output into a table using Format-Table

When working with PowerShell and using a (get-command | fl or format-list) you will receive a whole list of information which sometimes can be difficult to digest as shown below. In most cases normally you are only after one or two peices of key information

Using Format-List or LT you can specify the required information into something a bit more readable.

For example, I am currently working Get-SPOSite (SharePoint Online Sites) and I would like to know if any of the sites have sharing capabilities and site defined sharing capabilities.

# Command
Get-SPOSite | Select-Object -Property URL,SharingCapability,SiteDefinedSharingCapability | ft

As you can see from the above image the output for the required fields is more readable to my needs. You can use this approach for many different scenarios and maybe within a technical script you are writing for a deployment or an action.

Regards