Ben Taylor – Cloudy But Fine

Multiple script execution

May 8, 2017May 8, 2017 Ben Taylor Leave a comment

So, I had a scenario the other day where I wanted to run a PowerShell script on approximately 300 remote computers in an outage windows of no more than 30 minutes. The challenge was the script took 2 minutes to complete. I didn’t have enough fingers and toes for the math, so used a calculator to determine it would be… 600 minutes. Hmm, last time I checked 600 minutes is more than 30. So what to do..

The only way I could see this getting across the line was doing things in parallel. At 2 minutes per computer, I would need… give me a second… 20! batches of 15 computers. That would allow me to knock it out in 30 minutes. Well that was great, how was I going to do that? Also, with all things I do, I wanted to make the script reusable.

Now there are numerous ways to attack this, PowerShell workflows or jobs for example, but I also wanted to see output on screen as it was running. So I needed to launch separate PowerShell consoles for each of my batches of scripts. Without further ado here is what was cobbled together:

# Take a few parameters
# ComputerName - 1 or more computers (obviously pass more than one! or whats the point??)
# ScriptPath - Path to the PS1 powershell script you want to execute
# Batches - Number of Computers you want to pass to each Powershell script specified (default 10)
function Start-MultipleScriptExecution ($ComputerName, $ScriptPath, $Batches=10)
{
    #Break the passed Computers into their batches
    for ($i = 0; $i -lt ($ComputerName.Length)/$Batches; $i++)
    { 
        #Create an array to hold the computers in each batch
        $Batch = @()
        for ($j = 0; $j -lt $Batches; $j++)
        { 
            
            # Get index of computer to retrieve from array
            $index = ($Batches * $i) + $j

            # Ensure index is not greater than array length
            if ($index -lt $ComputerName.Length)
            {
                #add computer to batch
                $Batch += $ComputerName[$index]
            }
        
        }

        # Join the batch array of computers together with commas
        $BatchArg = [String]::Join(",",$Batch)

        # Create arguments, NOTE the script that is called needs to load an argument 
        # called "Batch" that will contain the computers in it's batch
        $args=" -NoExit -File $ScriptPath -Batch $BatchArg"

        # Start new instance of Powershell with given computers and do it all again (i.e. loop)
        Start-Process "powershell.exe" -ArgumentList $args
    }
}
# Job Done

# Take a few parameters

# ComputerName - 1 or more computers (obviously pass more than one! or whats the point??)

# ScriptPath - Path to the PS1 powershell script you want to execute

# Batches - Number of Computers you want to pass to each Powershell script specified (default 10)

function Start-MultipleScriptExecution ($ComputerName, $ScriptPath, $Batches=10)

{

#Break the passed Computers into their batches

for ($i = 0; $i -lt ($ComputerName.Length)/$Batches; $i++)

{

#Create an array to hold the computers in each batch

$Batch = @()

for ($j = 0; $j -lt $Batches; $j++)

{

# Get index of computer to retrieve from array

$index = ($Batches * $i) + $j

# Ensure index is not greater than array length

if ($index -lt $ComputerName.Length)

{

#add computer to batch

$Batch += $ComputerName[$index]

}

# Join the batch array of computers together with commas

$BatchArg = [String]::Join(",",$Batch)

# Create arguments, NOTE the script that is called needs to load an argument

# called "Batch" that will contain the computers in it's batch

$args=" -NoExit -File $ScriptPath -Batch $BatchArg"

# Start new instance of Powershell with given computers and do it all again (i.e. loop)

Start-Process "powershell.exe" -ArgumentList $args

}

# Job Done

Enjoy, pretty hacky, but that’s the way i roll!

Get Installed Certificates from a remote computer using alternate credentials in Powershell

April 2, 2017April 2, 2017 Ben Taylor Leave a comment

Managing certificates is painful. It’s kind of like brushing your teeth, if you ignore it bad things happen. Sure I’d rather deal with an expired certificate then a painful abscessed rotting tooth, but any business would not want to deal with the impact of an expired certificate on critical services. Hmm, for some reason i have lost my appetite!

Again with good house keeping, windows will alert you when certificates are about to expire, which is awesome. However the way it tells you this is far from awesome. Trawling through event logs, you might get an incredibly informative alert as follows:

Event ID: 64

Log Name: Application

Event Source: CertificateServicesClient-AutoEnrollment

Level: Warning

Message: Certificate for local system with Thumbprint 24 6d 3d de 1e 38 06 83 aa bc

cf b6 8f 29 0a 77 4a c8 13 c3 is about to expire or already expired.

Now I don’t know about you, but I remember the hexadecimal thumbprints for all my certificates at my workplace. Surely you do as well? It’s part of the job description right?? no??. OK, let’s suppose you are some kind of savant and you know exactly what that certificate is. Well, the next statement, “it’s about to expire or ready expired”, pfftb details right?? it doesn’t make that much of a difference…. Actually, it’s a massive difference. So in summary, nice one Microsoft!

Options for checking certificate details

When it comes to checking your certificate details you have a couple of options available to you

Do it manually with mmc and adding the certificate snapin…. Really?, sure this will work, but if you want to do that why are you reading my blog?
Script it. Now you are talking, read on 🙂

When it comes to scripting (with Powershell of course), like most things with scripts there’s more than one way to skin a cat (I wonder where that phrase came from??, quick google.. well that’s gruesome, I wont share). Anyway, here are some scripting options

Using PSDrive to check certificates

the PSDrive “Cert” is great, you can easily browse your certificates, and get more info than just the thumbprint! (such as expiry date). Here is a quick example

PS C:\Users\Ben> cd cert:

PS Cert:\> cd .\\LocalMachine\My

PS Cert:\LocalMachine\My> dir | fl

Subject : CN=localhost

Issuer : CN=localhost

Thumbprint : AAA411374BBEF65E3436C8536A3DA3AE3D1a0241

FriendlyName : IIS Express Development Certificate

NotBefore : 23/03/2013 2:44:21 PM

NotAfter : 23/03/2018 1:00:00 PM

Extensions : {System.Security.Cryptography.Oid, System...}

PS Cert:\LocalMachine\My>

The drawback to this approach is it is harder to use remotely. If Powershell Remoting is enabled, then awesome, wrap it around an Invoke-Command ScriptBlock and Baza’s your uncle, Shaz is your aunt (that’s my Aussie take on that saying) you are done.

Use an existing script

There is an existing script in the MS scripting gallery here

https://gallery.technet.microsoft.com/scriptcenter/a2a500e5-1dd2-4898-9721-ed677399679c#content

This is good, it let’s you query a remote computer for certificate details, so ticks that box. However, you cannot specify alternate credentials. It’s funny how often this is a requirement for me!

I also found some methods using com objects that were ported from original vbscripts, I’d suggest keeping clear of that confusing mess!

My way

Right, so 500 words later, I’m actually writing something of use! The approach I’ve taken is a little different but it ticks all the boxes for me. In my research I’ve found a couple of things:

Local Machine certificates are stored in the registry under HKLM\SOFTWARE\Microsoft\SystemCertificates
They are stored as a ByteArray
This ByteArray contains the raw certificate data we can turn into an X509Certificate.
Since it’s stored in the registry, I can specify alternate credentials as per my early blog post (the person who wrote that’s a legend).
I need to tidy my desk

So, using what I’ve learned I put together the below script to assist with getting certificate details from a remote computer with alternate credentials. Win!, enjoy..

function Get-PkiCertificate ($ComputerName = $env:COMPUTERNAME, $Credential)
{
    # Int for Local Machine Registry Hive
    $HKLM = 2147483650

    # Create our WMI registry object using alternate credentials
    $reg = Get-WmiObject -List -Namespace root\default -ComputerName $ComputerName -Credential $Credential | Where-Object {$_.Name -eq "StdRegProv"}

    # Path to certificate store - NOTE, To keep it brief, I've restricted this example to the Personal "My" store
    $keyPath = "SOFTWARE\Microsoft\SystemCertificates\my\Certificates"
    
    # Allow for multiple computers if passed
    foreach ($computer in $ComputerName)
    {
        # Enumerate all subkeys in Certificate Store Registry Path
        $subkeys = $reg.EnumKey($HKLM,$keyPath).sNames
        foreach ($key in $subkeys)
        {
            # For each certificate found get the "Blob" value and store as a Byte Array
            [byte[]]$byteArray = $reg.GetBinaryValue($HKLM,"$keyPath\$key","Blob").uValue

            # Create a new X509Certificate2 object
            $cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2

            # Import byte array into newly created object, and voila!
            $cert.Import($byteArray)

            # Add ComputerName incase multiple ComputerName's submitted
            $cert | Add-Member -MemberType NoteProperty -Name "ComputerName" -Value $ComputerName

            # return the certificate object with all it's details
            $cert
        }
    }
}

function Get-PkiCertificate ($ComputerName = $env:COMPUTERNAME, $Credential)

{

# Int for Local Machine Registry Hive

$HKLM = 2147483650

# Create our WMI registry object using alternate credentials

$reg = Get-WmiObject -List -Namespace root\default -ComputerName $ComputerName -Credential $Credential | Where-Object {$_.Name -eq "StdRegProv"}

# Path to certificate store - NOTE, To keep it brief, I've restricted this example to the Personal "My" store

$keyPath = "SOFTWARE\Microsoft\SystemCertificates\my\Certificates"

# Allow for multiple computers if passed

foreach ($computer in $ComputerName)

{

# Enumerate all subkeys in Certificate Store Registry Path

$subkeys = $reg.EnumKey($HKLM,$keyPath).sNames

foreach ($key in $subkeys)

{

# For each certificate found get the "Blob" value and store as a Byte Array

[byte[]]$byteArray = $reg.GetBinaryValue($HKLM,"$keyPath\$key","Blob").uValue

# Create a new X509Certificate2 object

$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2

# Import byte array into newly created object, and voila!

$cert.Import($byteArray)

# Add ComputerName incase multiple ComputerName's submitted

$cert | Add-Member -MemberType NoteProperty -Name "ComputerName" -Value $ComputerName

# return the certificate object with all it's details

$cert

}

And here is an example of it in action!

PS C:\> Get-PkiCertificate -ComputerName web1,web2 -Credential CBF\Ben | Select ComputerName, Subject, NotAfter | fl

ComputerName : web1

Subject : CN=DigiCert SHA2 Secure Server CA, O=DigiCert Inc, C=US

NotAfter : 9/03/2023 1:00:00 a.m.

ComputerName : web1

Subject : CN=coconuts.cloudybutfine.com

NotAfter : 1/01/2018 12:00:00 a.m.

ComputerName : web2

Subject : CN=*.cloudyexaample.co.nz, OU=IT, O=Clouds n sh...

NotAfter : 9/05/2017 12:00:00 a.m.

ComputerName : web2

Subject : CN=DigiCert Global Root CA, OU=www.digicert.com, O=DigiCert Inc, C=US

NotAfter : 10/11/2031 1:00:00 p.m.

Working with Email from Powershell via Exchange Web Services

March 27, 2017March 28, 2017 Ben Taylor Leave a comment

I came across one of those situations the other day where we were considering purchasing a product purely for one simple function, extracting attachments from an email and saving it to a folder.. It’s kind of like the time I brought the fanciest food process because I was too lazy to grate cheese (first world problems I know). Sure it did a great job grating cheese, but it was an absolute bugger to clean! But hey, it looks good, so worth it right….

Anyway, like my Cheese Grater analogy, the issue with buying a fancy piece of software is the cost and maintenance are overkill for a simple task. So I rolled up my sleeves and went about attacking this the powershell way. A couple of late nights neglecting my screaming kids I put together a script to do exactly what we wanted. So here is the masterpiece!

The first thing you want to do is install the Microsoft Exchange Web Services Managed API 2.2 . This will give you a library to enable all the fancy pants libraries to interface directly with your Exchange Server (or Office 365) leveraging Exchange Web Services (EWS). You only need to refer to the DLL in your script to take advantage of the all the exchange EWS goodness.

First up you will need to import the DLL as follows:

Import-Module -Name “C:\Program Files\Microsoft\Exchange\Web Services\2.2\Microsoft.Exchange.WebServices.dll”

After this, you can start using all of the EWS objects and get down and dirty with your Exchange environment!

Next, create an ExchangeService Object, and set some variables specific to your Exchange enviornment.

#Specify version of Exchange targeted (2013 SP1 will work with 365)
$ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2

#Create Exchange Service Object with given Exchange Version
$ExchangeService = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)

#Specify the EWS endpoint. Typically this is /EWS/Exchange.asmx
$ExchangeService.Url = "https://owa.cloudybutfine.com/EWS/Exchange.asmx"

# Set some Credentials
# Note you can also use PsCredential objects by passing the GetNetworkCredential() method to the constructor
$ExchangeService.Credentials = New-Object Microsoft.Exchange.WebServices.Data.WebCredentials("ben","Dodgy Password")

#Specify version of Exchange targeted (2013 SP1 will work with 365)

$ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2

#Create Exchange Service Object with given Exchange Version

$ExchangeService = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)

#Specify the EWS endpoint. Typically this is /EWS/Exchange.asmx

$ExchangeService.Url = "https://owa.cloudybutfine.com/EWS/Exchange.asmx"

# Set some Credentials

# Note you can also use PsCredential objects by passing the GetNetworkCredential() method to the constructor

$ExchangeService.Credentials = New-Object Microsoft.Exchange.WebServices.Data.WebCredentials("ben","Dodgy Password")

Right, now that we’ve setup our environment, let’s grab an email! In the below email I’ve searched my inbox for any email containing ‘stapler’ in the subject. Then, I save any attachments found to a local folder on my computer.

# Specify max items returned to limit bandwidth - 5 in this example
$itemView = new-object Microsoft.Exchange.WebServices.Data.ItemView(5)

# Add a substring contains filter to search by subject
$searchSubject = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+ContainsSubString([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::Subject,"Stapler")

# Search the inbox of current user for emails containing 'stapler' in the subject
$inbox = [Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox
$results = $ExchangeService.FindItems($inbox,$searchSubject,$itemView)

# Run DataBind to retrieve all properties for each email found (such as attachments)
$emails = $results | % { [Microsoft.Exchange.WebServices.Data.Item]::Bind($ExchangeService,$_.Id) }

#loop through each email found and save attachments to c:\temp
foreach ($email in $emails)
{
    foreach ($attachment in $email.Attachments)
    {
        $savePath = "c:\temp\$($attachment.Name)"
        $attachment.Load($savePath)
    }
}

# Specify max items returned to limit bandwidth - 5 in this example

$itemView = new-object Microsoft.Exchange.WebServices.Data.ItemView(5)

# Add a substring contains filter to search by subject

$searchSubject = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+ContainsSubString([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::Subject,"Stapler")

# Search the inbox of current user for emails containing 'stapler' in the subject

$inbox = [Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox

$results = $ExchangeService.FindItems($inbox,$searchSubject,$itemView)

# Run DataBind to retrieve all properties for each email found (such as attachments)

$emails = $results | % { [Microsoft.Exchange.WebServices.Data.Item]::Bind($ExchangeService,$_.Id) }

#loop through each email found and save attachments to c:\temp

foreach ($email in $emails)

{

foreach ($attachment in $email.Attachments)

{

$savePath = "c:\temp\$($attachment.Name)"

$attachment.Load($savePath)

}

Voila, thats it! Now if I look in c:\temp I can see all of the email attachments saved there.

To make all this a bit more reusable, I’ve created a Powershell module to simplify things. Simply import the PSM1 file (the EWS DLL needs to be available) then you can perform functions similar to below:

$email = Get-Email -EmailAccount "invoices@cloudbutfine.test" `
          -EwsUrl "https://domain.test/EWS/Exchange.asmx" `
          -Credentials "Domain\User" -MaxResults 8 `
          -FolderName "Bills" -SenderFilter "john@somebank.com" `
          -BodyFilter "Due Now" -SubjectFilter "More Bills"
          
#Only save Bills.xls to c:\Bills
$email | Save-EmailAttachments -FolderPath "C:\Bills" -Filter "Bills.xls"

#Move email to "Processed" folder in inbox 
$email | Move-Email -FolderName "Processed"

$email = Get-Email -EmailAccount "invoices@cloudbutfine.test" `

-EwsUrl "https://domain.test/EWS/Exchange.asmx" `

-Credentials "Domain\User" -MaxResults 8 `

-FolderName "Bills" -SenderFilter "john@somebank.com" `

-BodyFilter "Due Now" -SubjectFilter "More Bills"

#Only save Bills.xls to c:\Bills

$email | Save-EmailAttachments -FolderPath "C:\Bills" -Filter "Bills.xls"

#Move email to "Processed" folder in inbox

$email | Move-Email -FolderName "Processed"

EmailManagement.psm1 (8 Kb)

Enjoy, I’m off to eat something greasy, why do I feel like cheese all of a sudden..

Get Installed Software Remotely

March 22, 2017March 22, 2017 Ben Taylor Leave a comment

Much like my 80 year old Grandmother, there is a problem I regularly run into. Fortunately in my case it doesn’t involve a car and a garage door. It’s quickly getting a list of software from a remote computer. Also in my case, running into this issue for the third time didn’t result in my driver license being revoked. But, just like my Grandmother, I thought it wise to change my approach nonetheless 🙂

Throughout my career there are times when I want to get a list of software from a computer, or number of computers. This could be due to an audit, or pure curiosity as to what that awful piece of software one of my colleagues was using. (“Rhianna’s Music Quiz”… Nice one Mitch). As always, I’m following best practice and using a non administrative account so want to also use alternative credentials when querying the remote computer.

At first glance, the best approach here I’ve determined is WMI. On initial investigation the Win32_Product class seems like the perfect fit, running it locally I get a list of software, and it seems to give me a number of useful variables. Unfortunately, it takes a long time to run.. well 30 seconds, and that’s an age for us impatient Gen Y’s. Running it remotely this can blow out to minutes. So if you want to run a command like this across multiple computers… You’ll be waiting a while, and I’ve got important things to do! (like sending my mates a Snapchat of me as a Lemur). Apparently, the issue has something to do with the Win32_Product query performing a validation on all of your software, it get’s quite technical, a zoned out reading the reasons, drooled a bit and moved on.

So enter the registry. If you query HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall you can see all of your installed software there. However, only the 64bit software. The 32bit software is buried under the Wow6432Node of the registry, and most of the subkeys have lovely GUID’s so it’s quite the job working through that manually. But, after figuring out the logic and putting a script together, it would run in seconds. A win!

The best way to query the registry in my opinion, is to use WMI as you can then specify alternate credentials. If you are on the edge of your seat reading this then treat yourself to an earlier blog covering off just this thing. It’s bloody good I tell ya…

Anyway, after some coffee, some script munging, and a lot of trial and error, I produced the below script. It will accept a ComputerName and Credentials parameters then return all 32 and 64bit software from the remote box. It also includes the UninstallString so you can easily uninstall remote software, I’ll cover that off in another post, so you can actually get rid of that annoying software from your colleagues computer…

function Get-InstalledSoftware ($ComputerName = $env:COMPUTERNAME, $Credential)
{
    
    #Create Registry object for given computer and credentials
    if ($Credential)
    {
        $reg = Get-WmiObject -List -Namespace root\default -ComputerName $ComputerName -Credential $Credential | Where-Object {$_.Name -eq "StdRegProv"}
    }
    else
    {
        $reg = Get-WmiObject -List -Namespace root\default -ComputerName $ComputerName | Where-Object {$_.Name -eq "StdRegProv"}
    }
&nbsp;   
    #Array to store collection of software..
    $array = @()

    #Reg Path for both 64bit and 32bit software installs
    $UninstallKeys="SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall","SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall"
&nbsp;   
    #Int representing Local Machine Registry Hive
    $HKLM = 2147483650

    #Loop through each keypath
    foreach ($UninstallKey in $UninstallKeys)
    {
        $subkeys=$reg.EnumKey($HKLM,$UninstallKey).sNames
&nbsp;
        #Open each Subkey and use GetValue Method to return the required values for each
        foreach($key in $subkeys){
&nbsp;
            $thisKey=$UninstallKey+"\"+$key 

            #Create object to store results
            $obj = New-Object PSObject
            $obj | Add-Member -MemberType NoteProperty -Name "ComputerName" -Value $Computername
            $obj | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value $($reg.GetStringValue($HKLM,$thiskey,"DisplayName").sValue)
            $obj | Add-Member -MemberType NoteProperty -Name "DisplayVersion" -Value $($reg.GetStringValue($HKLM,$thiskey,"DisplayVersion").sValue)
            $obj | Add-Member -MemberType NoteProperty -Name "InstallLocation" -Value $($reg.GetStringValue($HKLM,$thiskey,"InstallLocation").sValue)
            $obj | Add-Member -MemberType NoteProperty -Name "UninstallString" -Value $($reg.GetStringValue($HKLM,$thiskey,"UninstallString").sValue)
            $obj | Add-Member -MemberType NoteProperty -Name "Publisher" -Value $($reg.GetStringValue($HKLM,$thiskey,"Publisher").sValue)
            $obj | Add-Member -MemberType NoteProperty -Name "RegPath" -Value $thisKey 
            $array += $obj
        }
    }
    $array | Where-Object { $_.DisplayName }
}

function Get-InstalledSoftware ($ComputerName = $env:COMPUTERNAME, $Credential)

{

#Create Registry object for given computer and credentials

if ($Credential)

{

$reg = Get-WmiObject -List -Namespace root\default -ComputerName $ComputerName -Credential $Credential | Where-Object {$_.Name -eq "StdRegProv"}

}

else

{

$reg = Get-WmiObject -List -Namespace root\default -ComputerName $ComputerName | Where-Object {$_.Name -eq "StdRegProv"}

}

#Array to store collection of software..

$array = @()

#Reg Path for both 64bit and 32bit software installs

$UninstallKeys="SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall","SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall"

#Int representing Local Machine Registry Hive

$HKLM = 2147483650

#Loop through each keypath

foreach ($UninstallKey in $UninstallKeys)

{

$subkeys=$reg.EnumKey($HKLM,$UninstallKey).sNames

#Open each Subkey and use GetValue Method to return the required values for each

foreach($key in $subkeys){

$thisKey=$UninstallKey+"\"+$key

#Create object to store results

$obj = New-Object PSObject

$obj | Add-Member -MemberType NoteProperty -Name "ComputerName" -Value $Computername

$obj | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value $($reg.GetStringValue($HKLM,$thiskey,"DisplayName").sValue)

$obj | Add-Member -MemberType NoteProperty -Name "DisplayVersion" -Value $($reg.GetStringValue($HKLM,$thiskey,"DisplayVersion").sValue)

$obj | Add-Member -MemberType NoteProperty -Name "InstallLocation" -Value $($reg.GetStringValue($HKLM,$thiskey,"InstallLocation").sValue)

$obj | Add-Member -MemberType NoteProperty -Name "UninstallString" -Value $($reg.GetStringValue($HKLM,$thiskey,"UninstallString").sValue)

$obj | Add-Member -MemberType NoteProperty -Name "Publisher" -Value $($reg.GetStringValue($HKLM,$thiskey,"Publisher").sValue)

$obj | Add-Member -MemberType NoteProperty -Name "RegPath" -Value $thisKey

$array += $obj

}

$array | Where-Object { $_.DisplayName }

}

And here is an example of it in action!

PS C:\> Get-InstalledSoftware -ComputerName MITCH -Credential Domain\BenAdmin | Where-Object {$_.DisplayName -match "Rhianna"}

ComputerName    : MITCH 
DisplayName     : Rhianna Music Quiz
DisplayVersion  : 15.007.20033
InstallLocation : C:\Program Files (x86)\Rhianna Music Quiz
UninstallString : MsiExec.exe /X{AC76BA86-7AD7-FFFF-7B44-AC0F074E4100}
Publisher       : Some Plonker
RegPath         : SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{AC76BA86-7AD7-FFFF-7B44-AC0F074E4100}

ComputerName    : MITCH 
DisplayName     : Rhianna Inspirational Quote Generator
DisplayVersion  : 6.0.1.7427
InstallLocation : C:\Program Files\Rhianna
UninstallString : MsiExec.exe /X C:\Program Files\Rhianna\Get-Rid-Of-This-Crap.msi
Publisher       : Another Plonker
RegPath         : SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{F132AF7F-7BCA-4EDE-8A7C-958108FE7DBC}

PS C:\> Get-InstalledSoftware -ComputerName MITCH -Credential Domain\BenAdmin | Where-Object {$_.DisplayName -match "Rhianna"}

ComputerName : MITCH

DisplayName : Rhianna Music Quiz

DisplayVersion : 15.007.20033

InstallLocation : C:\Program Files (x86)\Rhianna Music Quiz

UninstallString : MsiExec.exe /X{AC76BA86-7AD7-FFFF-7B44-AC0F074E4100}

Publisher : Some Plonker

RegPath : SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{AC76BA86-7AD7-FFFF-7B44-AC0F074E4100}

ComputerName : MITCH

DisplayName : Rhianna Inspirational Quote Generator

DisplayVersion : 6.0.1.7427

InstallLocation : C:\Program Files\Rhianna

UninstallString : MsiExec.exe /X C:\Program Files\Rhianna\Get-Rid-Of-This-Crap.msi

Publisher : Another Plonker

RegPath : SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{F132AF7F-7BCA-4EDE-8A7C-958108FE7DBC}

Get Unique Permissions on a shared Folder

January 9, 2017March 22, 2017 Ben Taylor Leave a comment

I was assisting a wise bearded, bespectacled, beer brewing, bugger the other day (lets call him Burko) with a common IT problem of identifying unique permissions on a shared folder. Much like Burko himself, the folder appeared to be in a dodgy state, most likely due to cancel being clicked when permissions were being propagated, because it takes so long. If it takes longer than 2 minutes, it’s just not worth doing, pretty sure I saw that on a motivational poster somewhere.

Given the ridiculous amount of sub-folders from years of uncontrolled sprawl, I was tempted to have Burko do it all manually just to test his sanity. But being the top guy I am, I got stuck into a bit of PowerShell to better manage the process more accurately. That and I couldn’t help myself.

So without further ado (did you know that comes from Shakespeare, man I am so cultured!), here it is!

# Requires PowerShell 3 for the awesome -Directory parameter in Get-ChildItem
function Get-FolderUniquePermissions ($Path)
{
    # Get all subfolders in given Directory
    $folders = Get-ChildItem $path -Directory
    
    # Get Access Control List (ACL) from current given directory
    $rootAcl = get-item $path | get-acl | Select -ExpandProperty Access
    
    # loop throw subfolders
    foreach ($folder in $folders)
    {   
        # Get ACL from subfolder
        $folderAcl = $folder | get-acl | Select -ExpandProperty Access

        # Compare each Access Control Entry (ACE) from both folder and subfolder, Only match on FileSystemRights and IdentityReference
        $diff = Compare-Object -ReferenceObject $rootAcl -DifferenceObject $folderAcl -Property "FileSystemRights","IdentityReference"
        
        # loop through different ACE's found
        foreach ($ace in $diff)
        {
            # Create new object with Path, Identity, and assigned permissions
            $obj = New-Object psobject
            $obj | Add-Member -MemberType NoteProperty -Name "Path" -Value $folder.FullName
			$obj | Add-Member -MemberType NoteProperty -Name "Identity" -Value $ace.IdentityReference
			$obj | Add-Member -MemberType NoteProperty -Name "Access" -Value $ace.FileSystemRights

            # If ACE is added to subfolder...
            if ($ace.SideIndicator -eq "=>")
            {
                $obj | Add-Member -MemberType NoteProperty -Name "Status" -Value "Addition"
            }

            # If ACE is missing from Subfolder...
            if ($ace.SideIndicator -eq "<=")
            {
                $obj | Add-Member -MemberType NoteProperty -Name "Status" -Value "Missing"
            }

            # Show object
            $obj
        }

        # Call Function again to recursively check additional subfolders
        Get-FolderUniquePermissions -Path $folder.FullName
    }
}

# Requires PowerShell 3 for the awesome -Directory parameter in Get-ChildItem

function Get-FolderUniquePermissions ($Path)

{

# Get all subfolders in given Directory

$folders = Get-ChildItem $path -Directory

# Get Access Control List (ACL) from current given directory

$rootAcl = get-item $path | get-acl | Select -ExpandProperty Access

# loop throw subfolders

foreach ($folder in $folders)

{

# Get ACL from subfolder

$folderAcl = $folder | get-acl | Select -ExpandProperty Access

# Compare each Access Control Entry (ACE) from both folder and subfolder, Only match on FileSystemRights and IdentityReference

$diff = Compare-Object -ReferenceObject $rootAcl -DifferenceObject $folderAcl -Property "FileSystemRights","IdentityReference"

# loop through different ACE's found

foreach ($ace in $diff)

{

# Create new object with Path, Identity, and assigned permissions

$obj = New-Object psobject

$obj | Add-Member -MemberType NoteProperty -Name "Path" -Value $folder.FullName

$obj | Add-Member -MemberType NoteProperty -Name "Identity" -Value $ace.IdentityReference

$obj | Add-Member -MemberType NoteProperty -Name "Access" -Value $ace.FileSystemRights

# If ACE is added to subfolder...

if ($ace.SideIndicator -eq "=>")

{

$obj | Add-Member -MemberType NoteProperty -Name "Status" -Value "Addition"

}

# If ACE is missing from Subfolder...

if ($ace.SideIndicator -eq "<=")

{

$obj | Add-Member -MemberType NoteProperty -Name "Status" -Value "Missing"

}

# Show object

$obj

}

# Call Function again to recursively check additional subfolders

Get-FolderUniquePermissions -Path $folder.FullName

}

An example of this running is as follows:

PS D:\> Get-FolderUniquePermissions -Path "D:\Test\Burkos Brewing Recipes"

Path                                                   Identity                      Access Status  
----                                                   --------                      ------ ------  
D:\Test\Burkos Brewing Recipes\Bitter Bastard          Everyone                 FullControl Addition
D:\Test\Burkos Brewing Recipes\Dark and Bitter Bastard Everyone ReadAndExecute, Synchronize Missing 



PS D:\>

PS D:\> Get-FolderUniquePermissions -Path "D:\Test\Burkos Brewing Recipes"

Path Identity Access Status

---- -------- ------ ------

D:\Test\Burkos Brewing Recipes\Bitter Bastard Everyone FullControl Addition

D:\Test\Burkos Brewing Recipes\Dark and Bitter Bastard Everyone ReadAndExecute, Synchronize Missing

PS D:\>

Hopefully this is of use to someone!

Questions, comments, abuse are always welcome. Don’t ask about beer brewing though, I’ve been asking for years to no avail.

Azure Automation – Running an on-premises PowerShell Script

December 1, 2016March 22, 2017 Ben Taylor Leave a comment

Ramblings

So I thought it was about time to get stuck into Azure Automation. I am a fan of the product in theory, but have yet to delve into the details and figure it all out. Currently we run a number of on-premises powershell scripts on a number of servers performing various tasks. They get the job done, but in a decentralized way with limited visibility.

Having a product like Azure Automation storing, versioning, reporting, and of course, executing scripts would definitely add value, and it’s about time I get out of my old habits.

It’s not like me to start a blog straight into techie stuff, so here is my favourite joke…

I went to a Zoo and all they had was a dog.. it was a Shitzu

Ok that works better when spoken than written down… Now into it!

Setting up Azure Automation

First up log on to your Azure Portal
Click Add –> Monitoring + Management –> Automation
Then set up an Automation Account. It needs some basic stuff, that is a name, subscription, resource group, and location.
Click Create!

create-automation-account — Create the Automation Account

Create Operational Management Suite Workspace

If you don’t already have an Operational Management Suite account, create one as follows

https://www.mms.microsoft.com/Workspace
Complete the registration details to create an account
Associate this with your Azure Active Directory

Deploy the Automation Solution in your OMS Workspace

Log into your Operational Management Suite portal (Created above)
Click Solutions –> Automation & Control to deploy the Automation Solution,

deploy-automation-solution — Add Automation and Control

Click Add to then deploy the solution.
Alternatively, If a configure workspace button is only visible, click this first and associate it with your Resource Group and Automation Account created earlier. After which you can then Add the solution

Install Microsoft Monitoring Agent

On an on premises machine you want to use as a Hybrid runbook worker, install the Microsoft Management Agent
Login to your OMS Workspace https://yourworkspace.portal.mms.microsoft.com
Click on Settings –>Connected Sources–>Windows Servers
Download the appropriate Windows Agent.
Download 32 or 64 bit Agent. Take note of Workspace ID and Primary Key for later use
Kick off the install of the agent on your designated server.
Ensure you check the box “Connect the agent to Azure Log Analytics (OMS)”
Enter the Workspace ID and Workspace Primary Key specified in your OMS portal (see above screenshot).
Complete the installation.
Go to Control Panel –> Microsoft Monitoring Agent
Click on the Azure Log Analytics (OMS) tab and verify connection is succesful
Verify agent has successfully connected to OMS

Create Hybrid Worker Group and connect agent to group

Head back into your Azure portal
Find your Automation Account created earlier (cloudy in my case)
Click on the Keys Icon under essentials and take note of the Primary Access Key and URL

Jump back on the server with the Monitoring Agent installed that you wish to use as your Hybrid Worker

Start and Administrative Powershell Session and enter the following cmdlets to connect to Azure Automation

cd "C:\Program Files\Microsoft Monitoring Agent\Agent\AzureAutomation\<version>\HybridRegistration" 
Import-Module .\HybridRegistration.psd1

Add-HybridRunbookWorker –Url "URL" -Key "Primary Access Key" -GroupName "Cloudy_Servers"

cd "C:\Program Files\Microsoft Monitoring Agent\Agent\AzureAutomation\<version>\HybridRegistration"

Import-Module .\HybridRegistration.psd1

Add-HybridRunbookWorker –Url "URL" -Key "Primary Access Key" -GroupName "Cloudy_Servers"

Use the URL and Primary Access Key from your Automation Account
For Group Name, enter any name you wish. This is the Hybrid Worker Group that will exist in your Azure Portal. If the group doesn’t exist it will be created.
You should now be able to see the new group in the Azure portal under your Azure Automation Account!

Finally, the Hybrid Worker Group is created!

Create a Powershell Script already!!!

Now we are good to go! Jump back in your Azure Portal
on the Automation Account Click Runbooks –> Add a runbook –> Create a new runbook
Call it RestartWorker with the type set to PowerShell
Click Create

add-runbook — The all powerful new script!

Wait while it’s created, then once completed enter type the cmdlet:

Restart-Computer -Force

1	Restart-Computer -Force

Click Save
Click Test pane
Select to run on a Hybrid Worker
Select the Hybrid Worker group we created earlier
Click Start
All going to plan, your server has just restarted!

And that’s it!

I know… thats an awful amount of work to restart a computer 🙂 But now we have the base down to do some more advanced scripting shenanigans! I hope this is of use to someone.

Gaining insight from consolidating and summarising Windows Event Logs.

November 21, 2016November 21, 2016 Ben Taylor Leave a comment

Trawling event logs, man I’m excited. This is the kind of stuff I dream about and it’s what gets me going in the morning. I can’t wait to get stuck into millions of lines of events with the same error innocuous occurring every hundreds of times a second. With that one critical alert buried in all the noise.

I love it as much as I love grating my knuckles on a cheese grater.. Wait a minute, that sounds awful, hang on reviewing event logs is equally awful!

Now that I’ve wasted your time with my pointless preamble, here is how I’ve made things better. In my years reviewing event logs, I’ve taken a few learnings.

It’s tedious
It’s really tedious
I want the information summarized
I want to know if the error is new, or re-occuring.

Yes I can do all the above stuff manually, but why on earth would anyone want to! So I used powershell to improve things.

Let me set the scene. You’ve just patched a critical server and it’s been rebooted. Some of the first things you should review (yes really you should) is the windows system and application event log. So we RDP, open the event log, clikety clickety click and all is well. Sweet, move on to another 50 servers… Well that sucks. So here is a better way.

I produced the following script to do the following.

Get all System and Application log events that are not Informational, so thats the Critical, Error, and Warning events
Group them. If a certain event has repeated itself 1000 times, just tell me once and let me know how many times it appears
Based on item 2, let me know the last time it appeared
Was this event present before I rebooted the server (let’s look back 7 days as a line in the sand)
Who on earth wants to log on to a server, let it take a ComputerName parameter instead
Alternate credentials need to be specified as well as hey you’re running a best practice shop right 😉

Ok enough talk here is the script, hopefully it makes some sort of sense:

function Get-EventLogStats ($ComputerName = $env:COMPUTERNAME, $Credential)
{
    #First up get the Date and Time of last boot
    write-host "Getting $ComputerName Last Boot Time..." 
    $query = "Select LastBootUpTime From Win32_OperatingSystem"
    $LastBootUpTime = (Get-WmiObject -Class Win32_OperatingSystem -Property LastBootUpTime `
                        -ComputerName $ComputerName -Credential $Credential).LastBootUpTime
    
    #Then, get all Non informational events between now and last boot from the System and Application event logs
    write-host "Getting $ComputerName Event Logs since last boot..." 
    $query = "Select * From Win32_NTLogEvent Where (TimeWritten>='$LastBootUpTime') AND (Logfile='System' 
                Or Logfile='Application') AND (Type<>'Information')"
    $RecentEvents = Get-WmiObject -Query $query -ComputerName $ComputerName -Credential $Credential

    #If there logs are found continue..
    if ($RecentEvents)
    {
        $daysBack = 7 # look back 7 days from the last boot for historical analysis
        write-host "Getting $ComputerName Event Logs $daysBack days prior to last boot.." 

        #Jiggery Pokery stuff - The timestamp is in file format, convert to date to subtrack 7 days the reformat it.
        $priorDate = (([wmi]("")).ConvertToDateTime($LastBootUpTime)).AddDays(-$daysBack)
        $priorDate = ([wmi]("")).ConvertFromDateTime($priorDate)

        #Get all events between the last logon date and 7 days ($daysback) prior to that
        $query = "Select * From Win32_NTLogEvent Where (TimeWritten>='$priorDate' AND TimeWritten<='$LastBootUpTime') 
                    AND (Logfile='System' Or Logfile='Application') AND (Type<>'Information')"
        $HistoricEvents = Get-WmiObject -Query $query -ComputerName $ComputerName -Credential $Credential

        #lets start comparing!
        write-host "Anaylizing Events..."

        #First up group the recent events by Event Type, event source, and event ID (Code)
        $grouped = $RecentEvents | Group-Object -Property Type,SourceName, EventCode 
        
        #loop through each group 
        $results = ForEach ($group in $grouped) {
            # $details is a 3 dimensional array of Event Type, Source, and ID
            $details = $group.Name.Split(",") 
            $EventType = $details[0].Trim()
            $Source = $details[1].Trim()
            $EventID = $details[2].Trim()            

            #Grab the most recent event in the group.
            $lastestEvent = $group.Group | Sort-Object TimeGenerated | Select -Last 1

            #Assume event is unique by default
            $preExisting = $false
            #Check Historical events to see if there is a match based on Event Type, Source and Event ID
            $matches = $HistoricEvents | Where-Object {$lastestEvent.EventType -eq $_.EventType -and `
                         $lastestEvent.SourceName -eq $_.SourceName -and $lastestEvent.EventCode -eq $_.EventCode}

            #if we get a match note the event is pre existing
            if ($matches)
            {
                $preExisting = $true
            }
            
            # Create the event object to return (stored as an array in $results)
            $props=@{
                LogName = $lastestEvent.LogFile
                Count = $group.Count
                EventType = $EventType
                Source = $Source
                EventID = $EventID
                LatestMessage = $lastestEvent.Message
                LatestTimeGenerated = ([wmi]("")).ConvertToDateTime($lastestEvent.TimeGenerated)
                PreExistingEvent = $preExisting
            }
            New-Object PsObject -Property $props
        }
        #display results
        $results
        
    }
    # If no events are found, awesome! Or more likely, whats wrong??
    else
    {
        Write-Host "No events found, you fortunate bugger!" 
    }
}

function Get-EventLogStats ($ComputerName = $env:COMPUTERNAME, $Credential)

{

#First up get the Date and Time of last boot

write-host "Getting $ComputerName Last Boot Time..."

$query = "Select LastBootUpTime From Win32_OperatingSystem"

$LastBootUpTime = (Get-WmiObject -Class Win32_OperatingSystem -Property LastBootUpTime `

-ComputerName $ComputerName -Credential $Credential).LastBootUpTime

#Then, get all Non informational events between now and last boot from the System and Application event logs

write-host "Getting $ComputerName Event Logs since last boot..."

$query = "Select * From Win32_NTLogEvent Where (TimeWritten>='$LastBootUpTime') AND (Logfile='System'

Or Logfile='Application') AND (Type<>'Information')"

$RecentEvents = Get-WmiObject -Query $query -ComputerName $ComputerName -Credential $Credential

#If there logs are found continue..

if ($RecentEvents)

{

$daysBack = 7 # look back 7 days from the last boot for historical analysis

write-host "Getting $ComputerName Event Logs $daysBack days prior to last boot.."

#Jiggery Pokery stuff - The timestamp is in file format, convert to date to subtrack 7 days the reformat it.

$priorDate = (([wmi]("")).ConvertToDateTime($LastBootUpTime)).AddDays(-$daysBack)

$priorDate = ([wmi]("")).ConvertFromDateTime($priorDate)

#Get all events between the last logon date and 7 days ($daysback) prior to that

$query = "Select * From Win32_NTLogEvent Where (TimeWritten>='$priorDate' AND TimeWritten<='$LastBootUpTime')

AND (Logfile='System' Or Logfile='Application') AND (Type<>'Information')"

$HistoricEvents = Get-WmiObject -Query $query -ComputerName $ComputerName -Credential $Credential

#lets start comparing!

write-host "Anaylizing Events..."

#First up group the recent events by Event Type, event source, and event ID (Code)

$grouped = $RecentEvents | Group-Object -Property Type,SourceName, EventCode

#loop through each group

$results = ForEach ($group in $grouped) {

# $details is a 3 dimensional array of Event Type, Source, and ID

$details = $group.Name.Split(",")

$EventType = $details[0].Trim()

$Source = $details[1].Trim()

$EventID = $details[2].Trim()

#Grab the most recent event in the group.

$lastestEvent = $group.Group | Sort-Object TimeGenerated | Select -Last 1

#Assume event is unique by default

$preExisting = $false

#Check Historical events to see if there is a match based on Event Type, Source and Event ID

$matches = $HistoricEvents | Where-Object {$lastestEvent.EventType -eq $_.EventType -and `

$lastestEvent.SourceName -eq $_.SourceName -and $lastestEvent.EventCode -eq $_.EventCode}

#if we get a match note the event is pre existing

if ($matches)

{

$preExisting = $true

}

# Create the event object to return (stored as an array in $results)

$props=@{

LogName = $lastestEvent.LogFile

Count = $group.Count

EventType = $EventType

Source = $Source

EventID = $EventID

LatestMessage = $lastestEvent.Message

LatestTimeGenerated = ([wmi]("")).ConvertToDateTime($lastestEvent.TimeGenerated)

PreExistingEvent = $preExisting

}

New-Object PsObject -Property $props

}

#display results

$results

}

# If no events are found, awesome! Or more likely, whats wrong??

else

{

Write-Host "No events found, you fortunate bugger!"

}

And the result?

powershell-get-event-log-stats-example — A thing of beauty! A summarized at a glance view of the Windows event logs, to drive better insight and agile decision making in your environment. Strategic staircases, disruption, top down bottom up view, managerial speak etc.. etc.. Essentially, it good bro.

And there you go, lives saved! (from boredom that is, I’m not some kind of hero.. or am I??)

Hopefully that is of use to somebody, in a later blog I will talk about how you can display stuff like this in fancy HTML pages styled with bootstrap. I find that stuff so much cooler than grating your knuckles.

Cheers!

Get SharePoint List Contents with Powershell using Client Side Object Model

October 30, 2016October 30, 2016 Ben Taylor Leave a comment

So I had a situation the other day where I needed to get a SharePoint list exported as a CSV file on a regular occurance.

Some background, I was working through a process identifying critical information in SharePoint we would like on hand in the event all servers were down (i.e. a brown pants moment). While we have this pretty much sorted for various word and excel documents, there were a number of SharePoint lists that would also be invaluable, and I was loathe to turn these into a static excel spreadsheet instead.

Our current process is a manual one where the list is opened in Excel then saved as a CSV. As this requires human effort, humans get complacent, and doing repetative things over time get done poorly, shortcuts taken, shiny things to distract you, etc.. etc, and isn’t that one of the reasons we use computers?? Where was I going with this again? Ohhh look a Blue Car!

Right back on track… So I performed some research, got distracted, got a coffee, then got back to it. A number of solutions exist which rely on the SharePoint powershell cmdlets that are installed on the SharePoint server, but do not come stand alone for a client. So that discounted them, also in our environment we are are not using PSRemoting. I’m also not a fan of running scheduled scripts on our SharePoint server unless absolutely necessary.

Then I stumbled across the SharePoint Client Side Object Module. Here I could manipulate SharePoint directly from my PC utilizing API calls on HTTPS. It sounded like just the ticket!

I dutily downloaded them: https://www.microsoft.com/en-nz/download/details.aspx?id=35585

Got a little confused reading the basic operations MSDN stuff https://msdn.microsoft.com/en-us/library/office/fp179912.aspx. This was in C# but translates fairly easily to Powershell with a bit of force.

Finally, after a bit of cursing, caffine, and luck I got a result! I wrapped it up in a function, then called it to my hearts content. Essentially it takes any SharePoint list and returns it as a table that I can then export to CSV.

Here is the Powershell:

function Get-SharepointList($SiteUrl, $ListName)
 {
    #Load some SharePoint CSOM Assemblies
    $CSOM = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
    $Runtime = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
    
    #Create the Web ClientContext on specifed Site URL
    $clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)

    #Execute the Query (when you run this Method it connects to Sharepoint and runs the commands queued up)
    $clientContext.ExecuteQuery()

    #Assign Web to SharePoint Web Site
    $web = $clientContext.Web

    #Assign list to Specific List
    $list = $web.Lists.GetByTitle($ListName)

    #Get all Field Definitions
    $fieldDefinitions = $list.Fields

    #Get all Items in SharePoint List
    $allItems = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()
    $items = $list.GetItems($allItems)

    #Load objects into the Client Context
    $clientContext.Load($fieldDefinitions)
    $clientContext.Load($items)

    #Execute Query (essentially returns data to $fieldDefinitions and $items)
    $clientContext.ExecuteQuery()

    #Get all field Title's (what you see in Sharepoint) and the corresponding internalName
    #I've only selected Non Standard fields, modify if you want ALL fields (remove WHERE)
    $fields = $fieldDefinitions | Where-object {$_.FromBaseType -eq $false -or $_.InternalName -eq "Title"} | Select  Title, InternalName 

    #loop through all list items
    foreach ($item in $items)
    {
        #Create a temporary PS Object to store all properties in item
        $obj = New-Object psobject -Property $props
        foreach ($field in $fields)
        {
            #Internal Name required when retrieving item value
            $value = $item[$field.InternalName]
            if ($value){
                #Trim value and remove HTML tags (makes CSV cleaner, optional of course)
                $value = $value.ToString().Trim() -replace '<[^>]+>',''
            }
            
            #Add Member using the value and Friendly Field Name
            $obj | Add-Member -MemberType NoteProperty -Name $field.Title -Value $value
        }
        #Return object
        $obj
    }
}

#Example
Get-SharepointList -SiteUrl "https://sharepoint.cloudybutfine.com/docs/testSite" -ListName "Beer Hiding Locations"

function Get-SharepointList($SiteUrl, $ListName)

{

#Load some SharePoint CSOM Assemblies

$CSOM = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")

$Runtime = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")

#Create the Web ClientContext on specifed Site URL

$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)

#Execute the Query (when you run this Method it connects to Sharepoint and runs the commands queued up)

$clientContext.ExecuteQuery()

#Assign Web to SharePoint Web Site

$web = $clientContext.Web

#Assign list to Specific List

$list = $web.Lists.GetByTitle($ListName)

#Get all Field Definitions

$fieldDefinitions = $list.Fields

#Get all Items in SharePoint List

$allItems = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()

$items = $list.GetItems($allItems)

#Load objects into the Client Context

$clientContext.Load($fieldDefinitions)

$clientContext.Load($items)

#Execute Query (essentially returns data to $fieldDefinitions and $items)

$clientContext.ExecuteQuery()

#Get all field Title's (what you see in Sharepoint) and the corresponding internalName

#I've only selected Non Standard fields, modify if you want ALL fields (remove WHERE)

$fields = $fieldDefinitions | Where-object {$_.FromBaseType -eq $false -or $_.InternalName -eq "Title"} | Select Title, InternalName

#loop through all list items

foreach ($item in $items)

{

#Create a temporary PS Object to store all properties in item

$obj = New-Object psobject -Property $props

foreach ($field in $fields)

{

#Internal Name required when retrieving item value

$value = $item[$field.InternalName]

if ($value){

#Trim value and remove HTML tags (makes CSV cleaner, optional of course)

$value = $value.ToString().Trim() -replace '<[^>]+>',''

}

#Add Member using the value and Friendly Field Name

$obj | Add-Member -MemberType NoteProperty -Name $field.Title -Value $value

}

#Return object

$obj

}

#Example

Get-SharepointList -SiteUrl "https://sharepoint.cloudybutfine.com/docs/testSite" -ListName "Beer Hiding Locations"

Hopefully someone finds that useful, enjoy 😀

Setting Proxy settings in Powershell and .Net Windows Applications

July 26, 2016November 21, 2016 Ben Taylor Leave a comment

There is a problem I have come across a number of times in my career that frustrates me. But I’m no closer to getting a full time barrista on tap to serve me coffee any time I need it. FML as the kids say..

There is also another problem, thats maybe more in line with my blogs so I suppose I should write about that instead!

A forward web proxy server, is a common piece of technology in most organizations where I’ve worked. It’s how we control the minions to ensure they only access the content on the internet we deem acceptable, monitor where they are going and how long they can browse a website etc.. Dance my puppets, muhahaha. I’m not going to argue the pros and cons of proxy servers, but needless to say they are prett standard.

What does bug me, is when business applications come along that say sorry our software doesn’t work through a proxy server… Oh nice one, you never though an application used by businesses may have a proxy server in the mix. Winner… Anyway, I’ve come across sites where there are holes open in the firewall for all manor of websites/ip address ranges etc. because its too hard and the easy out is to just poke a hole in the firewall and say “have at it!” Sure it’s a quick win, and gets you back on to more important things, like what the minions are browsing today. But it doesn’t cut the mustard for me (thats an odd saying, where on earth does that come from…quick google… nothing concrete.. oh well back to blog). Unbeknownst to a number of people, and even the “developers” that write these random third party desktop applications. There is a way to work with the proxy server in some scenarios using application settings.

This process has worked for me approximately 75% of the time, essentially it tells the application to use the proxy settings specified by the user. i.e. what has been configured in IE. I’ve tested it with IP, basic, and NTLM authentaction succesfully.

How to make windows desktop applications work with a proxy server

If it’s a modernish windows application. We can assume it has been built on the .NET Framework.
If you can search the internet using Internet Explorer via the proxy (that is, it has proxy settings specified somehwere), then thats another tick
Navigate to the program files directory. e.g. c:\program files (x86)\CrapVendor\CrapApp
Look for the executable (e.g. crapapp.exe)
An application settings file will typically have the same name as the executable but end in .config (e.g. crappapp.exe.config)
If one exists, great edit it
If one does not exist, then create a new file with that name (crapapp.exe.config)
Add the below settings

<?xml version="1.0" encoding="utf-8" ?> 
<configuration> 
	<system.net> 
		<defaultProxy useDefaultCredentials="true" /> 
	</system.net>
</configuration>

<?xml version="1.0" encoding="utf-8" ?>

<system.net>

</system.net>

</configuration>

If the file already exists, then you may only need to add the defaultproxy line, just ensure its in the system.net tags
Save the file, and fingers crossed it now works
Job Done! Grab a coffee.

Cannot Cancel Meetings as the Organizer in Outlook

July 19, 2016July 19, 2016 Ben Taylor 1 Comment

MFCMAPI - simple, self explanatory interface yeah? — MFCMAPI – simple, self explanatory interface yeah?

A strange issue was bought to my attention the other day where a user could not cancel a meeting they had created.

Looking in Outlook, they were listed as the organizer, yet when right clicking in Outlook the “Cancel Meeting” option was not present, only Delete. After ruling out PIBCAK, I dug a little deeper.

A search of google aluded to ActiveSync being the most likely cause of this issue. As we are running an IOS fleet using the native mail app which is ‘unsupported’ in Microsoft’s eyes. Then this may be where the problem lies. The user recently got married and changed her name, how inconsiderate of her not to think about the impact it caused me! It’s all about me you know 🙂 Anyway, to hedge my bets, the mail profile was recreated on their iDevices, an now to deal with the problematic calendar appointments.

No clear solution was online, but things were aluding to using MFCMAPI to get surgical under the hood on the users mail profile, so I rolled up my sleeves and got stuck in.

An hour later, after getting side tracked several times by stuff much more interesting than using the MFCMAPI tool (basically anything, yes even the Kardashiyans.) I found the field in particular causing an issue, made a change, and BOOM, the user can cancel all those problematic meetings to her hearts content. A win!

So.. Enough of the background.. Here is how it was done. Usual disclaimers apply #YMMV

First up grab the MFCMAPI tool from codeplex http://mfcmapi.codeplex.com/. This is a really useful tool for drilling into the details of a users mailbox, great way to mess things up too! If you’ve not used the tool before you are in for a treat, saying the layout is not userfriendly is an understatement..

So you’ve downloaded, run the exe and vomited at the shear complexity of it, give yourself a high five and perform the rest:

Oh.. Run the tool from the users computer as it needs to access their outlook profile…

Close Outlook if it’s open (precaution)
Click Session–>Logon
Select the specific mail profile and click OK
Double Click on the users Mailbox
Expand Root –> Top of Information Store
Double Click Calendar
If you’ve got hear you’ve done well.
In the top window find the dodgy meeting in question. I find sorting by the subject column useful
Once found single clicl on it to highlight it
In the windows below, sort by column “Named prop name”
Look for id: 0x8217… (obvious yeah??)
Double click this field
If this unsigned decimal value is 3 it indicates the user is a recipient of the meeting. If it’s a 1 it indicates they are the meeting Organizer. Chances are if you have this problem its a 3, hence the wierd and wonderful bug
Set this to 1 and click OK
Close MFCMAPI
Open Outlook and check out the problematic meeting
The Cancel Meeting button should return!
Job done, you are a hero

Hopefully this post is of some help to any poor soul whose stumbled across this part of the internet.