Monday, September 11, 2017

Powershell CSV Join

I am currently working on a fairly large project migrating mailboxes from on-premise Exchange 2010 upto Microsoft's O365. One of the issues that we're having is that mailbox permissions don't always migrate correctly. So I've developed process to capture the mailbox permissions in one form or another in a CSV prior to migration, then I reapply them after reapplying them.

One of the issues experienced is that on-premise account information doesn't always match up with what's in O365. For example, I have the local Exchange / AD account information, but not necessarily what the account looks like in o365. So I wrote this script to join two CSV files based on similar properties. This way I can join a CSV containing mailbox information (i.e. SamAccountName) to the Get-MailboxPermission "USER" field.

.\CSVJoin.PS1 -CSV1Data "c:\bin\All-Mailboxes.CSV" -CSV1Property Samaccountname -CSV2Data "c:\bin\All-MailboxPermissions.csv" -CSV2Property USER -JoinedCSV "c:\BIN\Joined-CSV.csv"

The  script will export a CSV containing all fields from CSV1 plus all the fields from CSV2 that don't overlap in name.

#CSVJoin.PS1
[CmdLetBinding()]
param(
    [parameter(Mandatory=$true,HelpMessage="Path and filename for source CSV 1")][ValidateScript({Test-Path $_ })][string]$csv1Name,
    [parameter(Mandatory=$true,HelpMessage="Propery from CSV1 to join files on.")][string]$csv1Property,
    [parameter(Mandatory=$true,HelpMessage="Path and filename for source CSV 2")][ValidateScript({Test-Path $_ })][string]$Csv2Name,
    [parameter(Mandatory=$true,HelpMessage="Propery from CSV2 to join files on.")][string]$csv2Property,
    [parameter(Mandatory=$true,HelpMessage="Path and Name for combined CSV file")][string]$JoinedCSV
)

$csv1Data = Import-CSV $csv1Name | ?{$_.$CSV1Property -ne $null}
$csv2Data = Import-csv $Csv2Name | ?{$_.$CSV2Property -ne $null}

#Capture all the column values for each CSV file and compare them.
$csv1Members = $csv1Data[0] | Get-Member | ?{$_.membertype -eq "NoteProperty"}
$csv2Members = $csv2Data[0] | Get-Member | ?{$_.membertype -eq "NoteProperty"}
$AddCSV2members = Compare-Object $csv1Members $csv2Members | ?{$_.sideindicator -eq "=>"} | %{$_.inputobject}

#Populate HashTable with First CSV based on JOIN fields
$csv1HashTable = @{}
$csv1Data | %{$csv1HashTable[$_.$csv1Property.trim()] = $_}


#Loop through Second CSV and join fields to first CSV. 
$newCSV = @()
ForEach ($c in $csv2Data) {
    $Row = $csv1HashTable[$c.$csv2property.trim()]
    if ($row ) {
        ForEach ($m in $AddCSV2members) {
            $Row | add-member -Membertype NoteProperty -Name $m.name -value $C.$($M.name) -force
        }
        $newCSV += $Row
    }
}

if ($newCSV) {
    $newCSV | Export-csv $JoinedCSV -notypeinformation
}

Thursday, July 6, 2017

Delete and Compress old Log Files using DOS Batch file.

On all of our Exchange Client Access servers, I've been running my Compress and Delete script. Unfortunately, randomly the scheduled task will fail to run the script and the log files don't get purged. This requires kicking off the process by hand to avoid server meltdown.

I am thinking, that powershell may be partially at fault on some of these boxes. Permissions, or run time exceptions, may be causing the script to fail at running. So I've managed to put together this DOS Batch script that does basically the same thing.

  • Deletes all log (and zip) files in the folder older than 30 days
  • (if finds 7-zip) It will compress all log files older than 7 days, then delete them
The one disadvantage that I see is date-stamping. In Powershell, I was going through and stamping the original log files 'last modified' date on the ZIP. This allowed me to easily trigger 30-day deletes on any file in the folder because it would maintain it's original date. I figure that if I run this scheduled task daily, it will only offset the date by 7 days (i.e. an 8 day old log file will take today's date).

I am saving the following as "DOSPurgeOldLogFiles.CMD" and running it from a daily scheduled task.

@Echo off
REM Folder for Log Files
Set InetLogsFolder=c:\inetpub\logs\LogFiles\W3SVC1
if NOT EXIST %InetLogsFolder% Goto :NoLogs

REM Purge all files in log folders older than 30 days old
forfiles -p %InetLogsFolder% /s /m *.* /d -30 /c "cmd /c del @path"

if NOT EXIST "c:\program files\7-Zip\7z.exe" Goto :NoZIP
REM ZIP all files older than 7 days old
for /F %%G in ('forfiles -p %InetLogsFolder% /s /m *.LOG /d -7') DO "c:\program files\7-Zip\7z.exe" a -tzip -mtc=on "%InetLogsFolder%\%%~G.zip" "%InetLogsFolder%\%%~G"

REM delete all files that are now ZIP'd
forfiles -p %INETLogsFolder% /s /m *.log /d -7 /c "cmd /c del @path"

GOTO :END

:NoLogs
Echo Cannot locate log files at %InetLogsFolder%
PAUSE
GOTO :END
:NoZIP
Echo Install 7-ZIP to compress log files.

:END

Friday, May 12, 2017

Pull All WU Patches from Servers

With all the concern in the news lately, we went through all our on-premise servers and reviewed the patching. This script reads all of our Exchange servers (you could replace that for a CSV of server names) and does a remote call for the Windows Update patching. The script will return an object containing each installed patch and if it was successful or not.
#Report-WindowsUpdatePatching.ps1
$scriptBlock = {
 $Session = New-Object -ComObject "Microsoft.Update.Session"
 $Searcher = $Session.CreateUpdateSearcher()
 $historyCount = $Searcher.GetTotalHistoryCount()
 $Searcher.QueryHistory(0, $historyCount) | ?{$_.title -notlike "*definition update for*"} |Select-Object Title, Description, Date,
 @{name="Operation"; expression={switch($_.operation){
    1 {"Installation"}; 2 {"Uninstallation"}; 3 {"Other"}
   }}},
 @{name="Status"; expression={switch($_.resultcode){ 1 {"In Progress"}; 2 {"Succeeded"}; 3 {"Succeeded With Errors"};4 {"Failed"}; 5 {"Aborted"} }}}
}

$serverList = get-exchangeserver
$Patching = @();$serverCount = $serverList.count;$index=1

forEach ($server in $ServerList ) {
    write-progress -activity "reading Windows Update" -Status $server.name -percentcomplete (($index/$serverCount)*100);$index++
    $LastUpdates = Invoke-Command -ScriptBlock $scriptBlock -ComputerName $Server.name -ErrorVariable $failedWINRM
    $Patching+= $lastupdates
}
return $Patching


.\report-WindowsUpdatePatching.ps1 | ?{$_.title -like "*4012212*" -and $_status -eq "Failed"}


Thursday, March 30, 2017

Removing all the bad (email addresses)

With moving to o365, it comes time to get serious about cleaning out all of the invalid smtp domains that we've inadvertently stamped on all our mailboxes. Those other customers where their policy was applied incorrectly and stamped across domains. Objects that moved between customers and took on both domains. That 'default' smtp domain that isn't route-able, but is out there because your customers don't really want your smtp domain stamped on everything.

For this, I've developed two scripts. This first script is a bit of a blunt hammer. I will remove all domains that don't belong to this customer, plus leave any onMicrosoft.com domains.

Considerations:

  1. All customers are sorted into their own OU. I am limiting my search to only their container to avoid removing email domains from objects that belong to other customers. 
  2. All objects have at least one valid email address that should be kept. Since this first one uses native Exchange tools, it won't remove the primary smtp address. 
#Clean-NonAcceptedDomains.ps1
#Remove all but the primary smtp domain and any onmicrosoft.com domains.

[CmdLetBinding()]
param(
 [parameter(Mandatory=$true)]
 [string]$OU,
 [parameter(Mandatory=$true)]
 [string]$PrimarySMTPDomain
)


if ($ou -notlike "ou=*") {
    $OuDN = get-organizationalunit $ou
    $ou = $oudn.distinguishedname
}

write-host "Reviewing objects in: ",$ou

$DomainFilter = "*@"+$PrimarySMTPDomain
$AllRecipients = get-adobject -filter {mail -like $DomainFilter} -properties ProxyAddresses -searchbase $ou -resultsetsize $null

$Index=1;$objCount = $AllRecipients.count; $ModifiedDateStr = "Modified: "+$(get-date).toshortdatestring()

write-host "Found $objCount with $DomainFilter"

Foreach ($m in $AllRecipients) {
    write-progress -Activity "reviewing objects" -Status $m.name -PercentComplete (($index/$objCount)*100);$Index++    
    $removeThese = $m.ProxyAddresses | ?{$_ -like "SMTP:*" -and $_ -notlike $DomainFilter -and $_ -notLike "*@ces.mail.ca.gov" -and $_ -notLike "*.onmicrosoft.com"} | %{$_}
    if ($removeThese) {
        $o = get-recipient $m.distinguishedname
        $n = $null
        if ($o.recipientType -eq "UserMailbox") {
            $n = get-mailbox $o.identity
        } elseif ($o.recipientType -like "mailUniversal*") {  #Some type of group
            $n = get-distributiongroup $o.identity
        } elseif ($o.recipientType -like "mailuser") {  #Some type of group
            $n = get-mailuser $o.identity
        }
        if ($n -ne $null) {
            $removeThese | %{write-host "removing $_ from $n"}
            if ($removethese -is [string]){
                #was getting errors that email address was NULL 
                #  when trying to use foreach loop with single domain.
                $Results = $n.emailaddresses.remove($RemoveThese)
            } else {
                $results = $removeThese | %{$n.emailaddresses.remove($_)}
                break
            }
            if ($o.recipientType -eq "UserMailbox") {
                set-mailbox -identity $n.identity -emailaddresses $n.emailaddresses -customattribute8 $ModifiedDateStr
            }  elseif ($o.recipientType -like "mailUniversal*") {  #Some type of group
                set-distributiongroup -identity $n.identity -emailaddresses $n.emailaddresses -customattribute8 $ModifiedDateStr
            } elseif ($o.recipientType -like "mailuser") {  #Some type of group
                set-mailuser -identity $n.identity -emailaddresses $n.emailaddresses -customattribute8 $ModifiedDateStr
            }
        } else {
            write-host "Error: $o not mailbox or group"
        }
    } else {
        #write-host "nothing to change for $m"
    }
}

Tuesday, January 10, 2017

MemberOf in O365

Now that we have user's in o365, I've been trying to reproduce my on-premise Exchange 2010 scripts using technology available in O365. The biggest issue so far has been the lack of  AD cmdlets. No longer can I run "get-qaduser" to pull the MemberOf and AllMemberOf properties. After numerous searches to find a way to do this, I finally decided it was going to take some powershell recursion with get-distributiongroupMember.. Ugh. I was that close, when I stumbled on this post on Spiceworks. It's a basic brute-force search, but it only took a tiny portion of the time of my recursive process. (of course, I am reading in all the groups into memory with this version, instead of live reads each group).

My starts where Raven left off. My intention is to also reproduce 'allmemberof' which includes all parent groups the user is a member of.  For example:

Windows Engineering -> IT Staff -> California Staff -> All Staff

#O365MemberOf.ps1
#Finds all groups $identity belongs to in local Active Directory.
[CmdLetBinding()]
param(
[parameter(Mandatory=$true)][string]$Identity
) 
write-verbose "confirming identity"
$U = get-user $identity -ea silentlycontinue
if ($U -eq $null) { write-error "Can't find $identity. Try again.";Break}

write-verbose "reading all groups into memory"
$groups = get-group -resultsize unlimited | select name, members

write-verbose "reviewing first level groups that user belongs to."
$MemberOf = $groups | ?{$_.members -contains $u}
$childgroups = $MemberOf
$AllMemberOf = $MemberOf
Do {
    write-verbose "reviewing parent groups that user belongs to."
    $parentGroups = ForEach ($cg in $childgroups) {      
         $groups | ?{ $_.members -contains $cg.name}
    }
    write-verbose "found some parent groups, let me check those as well. Keep climbing up the tree.."
    $AllMemberOf += $parentGroups
    $childgroups = $parentGroups
} While ($ParentGroups.count -gt 0) #Only stop when I reach the top.

Write-Verbose "#Create pretty answer, add to User object that we started with."
$amoj = ($AllMemberOf | select -unique Name | %{$_.name}) -join(";")
$moj = ($MemberOf | select -unique Name | %{$_.name}) -join(";")
$u | Add-Member -Name "AllMemberOf" -MemberType NoteProperty -Value $amoj
$u | Add-Member -Name "MemberOf" -MemberType NoteProperty -Value $moj

write-verbose "#Return a modified user object to requestor."
Return $u

To use:
Example 1:

.\o365MemberOf.ps1 -identity "Joe User" | select MemberOf

- echo first-level group membership to screen

Example 2:

$UserInfo = .\o365MemberOf.ps1 -identity "Joe User"
$UserInfo.AllMemberOf.split(";") | get-distributiongroup

- grab all group membership and find associated distribution groups.

Thursday, October 6, 2016

One Month With Android

WP7 Looking rather toyish now

I love my Windows Phone. 

I was one of the two people standing outside my local AT&T store to get it on release day. My very first smartphone was a Samsung Windows Mobile 7.0. I skipped all other smart devices, turned in my Motorola flip phone and pocketed one of these.. 

Here was such a dramatic change on cell phones seen in a LONG time. Live tiles, Xbox integration, deep in the Microsoft environment, and best of all, no stupid wall of icons (ala Windows 3.1).

Inspiration for iPhone and Android OS.
As the OS matured, it only got better. Tile sizes started ranging from small 1/4th size, to a large 16x grid. Live tiles are absolutely awesome! Weather? 96F. Unread message from my boss in my inbox. My son just got another XBox achievement. Meeting in hour in big conference room. Amazon order just arrived on my porch. My wife has just updated her Facebook profile. Best of all, I only logged into my home screen. "2-click access".

With the OS, came some really interesting phones. My absolute favorite being the Lumia 1020 with it's glorious 41 megapixel camera. A camera that put my 10-year old DSLR to shame for basic, everyday photography. It's only real drawback was the lack of a microSD card slot, to hold those 20mb images. I loved this camera. I went as far as to purchase the extended battery case.


Trips to Disneyland were awesome because I only had to carry my cell to get a big camera; all-in-one, great low-light images, very easy package to carry around. The added battery, plus an real tripod mount on my phone made it possible to take some awesome low-light images.

Tower of Terror via Lumia 1020 from article TechBuffalo

Sadly, the honeymoon ended..  

Lumia, the premier windows phone manufacturer was gobbled up, disassembled and soon afterwards, all their production shut down. We've had 3 mediocre (IMO) phones put out. 640, 650, and 950. A few other's, like HP put a toe in the water and spun up the Elite x3, which I was really excited about, but it isn't going to fix things.

Things like, apps. Unless you're my uncle, who 'only uses his phone for calls', apps play an important role on any modern handheld. I understand, programmatically the pains of supporting a platform. (i.e. scripts that no longer work due to new technologies, scripts that rely on an API that is barely supported by the vendor.) Having not just one, two, but three different operating systems and virtually hundreds of different hardware platforms is more than daunting. A developer's trepidation to simply step into a third handheld arena is palpable. Can you say 'third-wheel'?

Android (aka the suit), and Apple (the cheerleader) hanging with Microsoft (the nerd).

Why go with 'The Suit'? 

Two reasons. I am already one foot into the google landscape. This blog is hosted blogger.com, which is ran by Google. Second, while Apple does have some nice looking hardware, she is very high maintenance. All about the bling. Android is at least grounded in a bit of reality, having reasonably priced hardware. In addition, the OS is 'fairly open'. It's not impossible to customize the loader, or change the search engine to meet my eccentric whims.
I started off with a work paid-for Galaxy S5. I had this for about a week for testing the deployment of new MDM solution in-house. During the week that I had this, I enjoyed being able to play Pokemon Go, stream music via the Amazon Prime app, watch tv via the Xfinity TV app, and even run Disney Appisodes for my toddler. Apps, apps and more apps. Kid in a candy store and all for free!!

Meanwhile on my Windows Mobile: Amazon pulls their app and replaces it with pitiful web skin. Battery on my Lumia 1520 is topping out at 2 hours on fast ring build in stand-by. Pokemon Go had my 10 year old wanting to walk the half mile to the neighborhood park. 

Even better, with a little finagling, I was able to 'win-mobile'-ify my phone. I tried looking for a decent 'live tiles' loader, but none had the real flair of Windows mobile 10, maybe WP7 where there are only 2 button sizes. Bleh. Nova Launcher provided both a sorted app list, and app grouping (which is 70% of what I missed). Home screen now has three groups, 'entertainment', 'games' and 'work' plus shortcut to my wife's contact. Next step, Cortana. Holding down the home key brings up the Cortana (beta) and I get my familiar search functionality (plus my fka Bing rewards for searches). In addition, I get alerts on my Win10PC about new SMS messages, battery low, etc.. The lousy part is I've broken voice searching, 'OK Google' nothing and 'Hey Cortana' only works from inside the app.

After the testing, I returned the phone and then seriously started considering Android as an option. A co-worker presented me with his 'old' work phone. He has a Samsung Galaxy S6 that he wasn't using at the moment and I could long-term borrow it. OK! Pulling the sim from my Lumia, I started rebuilding this phone. Only this time, adding widgets for weather, (Outlook) calendar and Audible.

Android Pain points:

WP Media Controls 

  • Full media control from lock screen - WP has this awesome option that if I tap the physical volume buttons, the system's media interface drops down, then I can pause currently playing media (audible, spotify, etc.). Android appears to not like that and it requires a widget on the screen to give you basic lock-screen functionality. 
  • The land of micro-transaction - Do you want weather with no ads? $2/month. Want spam call blocking? $1/month Want a new theme for your phone (no bing image of day)? $1.50. While I was used to some of this on Windows Phone, the Android market place is more wild-west. You can find a hundred free apps, each with a multitude levels of microtransaction built into it. 
  • Loss of live tiles - Widget have helped with this but it's simply not as elegant. Clicking on an appointment on my work calendar doesn't play well with the corporate MDM policies. I often get a 'your corporate policy doesn't allow this' notification because of 'crossing boundaries'. When it was the Outlook live tile providing the meeting info, it didn't complain. 
  • Bluetooth support - I was living the wireless dream. Sit down in my car, put it on the charger base, start streaming audio. Phone played music/audio via bluetooth connection to my car stereo. Unfortunately, this S6 does not work at all. The bluetooth at best will play 10 seconds of audio, then reset the connection. This will either restart the audio, rewind 5 seconds or stop playback altogether. I'm forced into using a wired 'auxiliary' cable to connect my phone to my stereo. 
  • Bloatware and nagging native apps - it was annoying on Windows that the native apps would reinstall after a full rebuild, but I COULD uninstall all of them if I wanted to. That ISP based navigation system that requires a special subscription? Gone. Family tracking app when only my wife has a phone? Gone. Android on the other hand forces me to keep all of these apps and more. In fact, it makes it REALLY hard to not use them, I continue to get notifications that the native email client needs my credentials for my work email. Guess what, my employer won't let the native app sync, we're an O365 shop, so it will NEVER work. Stop asking me and go away.. 
Amazon sees my giant water glass.

Android Perks:

  • Richer apps - Not only is the Android Amazon app not a web-skin, but it has awesome object scanning functionality. Point the scanner at the a barcode and it quickly find the item. Point the scanner at the water glass on my desk, it returns keywords related to that item. 
  • Richer accessory ecosystem - Search Amazon for a Galaxy S6 (or work sponsored S5) case and it returns 1.6 million options. Search for my Lumia 1520, you don't get even 1% of the options.
  • Microsoft apps still work - Cortana is here for my searches. OneDrive is still backing up my photos. Word, Excel, Outlook are available for my work

Microsoft, do you want me back? Become more Disney.

While, I've quit using my Windows Phone, I still prefer the OS. Win mobile has character. Because it's a brand new OS, you've customized beyond the other two options, adding little tidbits/flavor items. I kind of think of you as a renaissance faire. Tons of character, limitless customization options and your biggest draw is homegrown apps by third party 'entertainers'. On the other hand, Android is like Disneyland. There is no originality, no individuality, no character, but they have a TON of flash, bang and glitz. Fireworks, movie-themed amusement rides and churros draw in a lot more people than jousting tournaments and turkey legs. 

I want Disneyland on my terms. I want a phone to brag about with more flavor of Windows Mobile. I need you to be enthusiastic about selling the phone. That will make people more enthusiastic to buy it and people enthusiastic to develop for it. You and only you are going to break down that app wall. I like that the Amazon app can scan something without a barcode and return results close to it.

How to get there ... 

Windows Mobile's future if no consumer focus. (NYTimes)
Surface phone better come true. It better be awesome, ground breaking and absolutely shiny with all those bells and whistles that that "Surface" name implies. Phone case with BT keyboard? Wireless charging?

Bring back the Android bridge. This will help because we get access to the apps, and developers don't have to design for 3 different OS. You have to realize that if you're target audience is only corporate, you are becoming RIM and what's their latest phone? None. They plan to give up their 0.01% handheld marketshare to focus on MDM solutions. At least Microsoft, you have Office/o365 and Windows on desktop, I suppose there's always that.


Monday, June 27, 2016

Recurse Groups - All Native Tools

Found this post from 2011 that used a slick directory searcher to find group membership. While it doesn't give parents, I was able to simply loop until I went up each chain.
Function AllMemberOf ($SamAccountName) {
# Based off of 
#  http://stackoverflow.com/questions/5072996/how-to-get-all-groups-that-a-user-is-a-member-of
 $groups = ([ADSISEARCHER]"samaccountname=$($samaccountname)").Findone().Properties.memberof  | ?{$_ -ne $null} | get-group
 $indent = 1
 $master = $groups
 #$Master | get-group | %{write-host $_.name}
 Do {
     $parents = $Groups | %{([ADSISEARCHER]"samaccountname=$($_.SamAccountName)").FindOne().Properties.memberof} | ?{$_ -ne $null} | %{get-group $_ }
     if ($parents -ne $null) {        
         #$Parents| get-group | %{write-host $("`t"*$indent),$_.name}
         $Master += $parents
         $groups = $parents
         $Indent++
     }
 } While ($Parents -ne $null) 
 return $master
}
This does roughly the same as (get-aduser 'eric woodford').AllMemberof  | get-group

AllMemberOf $(Get-Mailbox "Eric Woodford").SamAccountName