Script Dumpster: Find duplicate entries over multiple reports

Another day at the office..

Thanks to our friends who wrote the NotPetya worm, I received an email from our monitoring vendor to run reports to see if our machines are up-to-date on their patching.

Unfortunately their reporting tool doesn’t properly distinguish between Windows Server 2008 and Windows 2008 R2, as well as Windows 2012 and Windows 2012 R2.

Long story short,  I had to create 4 separate reports, telling me if I had or had not installed the proper KB item on each machine.

Because of this flaw I also had to join the reports and check the “Highlight Duplicates” option in Excel to see whether or not servers had their respective Hotfix installed (if the server had a duplicate entry, it meant that it didn’t have either the standard or R2 patch installed, meaning vulnerable).

Each report also came with a 3 row header with random junk that needed to be removed, so a simple Ctrl + A , Ctrl + C, Ctrl + V wouldn’t suffice.

PowerShell to the rescue!

I looked at the email from the vendor and went “Hell no, I’m not going to do that…” and opened up PowerShell ISE.

Having dumped the reports in the folder c:\Temp\NotPetya , I came up with the following script:

While the coding took a little bit longer, the execution was swift and perfect.

Geeks and Automation

 

Happy scripting! 🙂

Facebooktwittergoogle_plusredditpinterestlinkedinmail

So you want to edit your input data using PowerShell

In one of my previous blog posts, I had a CSV worth of content in which I wanted to change a specific field of data.

I was racking my brain on how I could best approach this and while working on this, I kept getting a better and cleaner result, but every step approached the issue a bit different, even though the output was as required.

Requirements

I have a CSV file which contains the following headers

As mentioned in my earlier blog post, I needed to make sure that my PurchaseDate was in a specific format, so it would be processed correctly by Excel.

I wanted to get an Excel file which had all the above data and either a new column with the correct date format or have PurchaseDate values overridden by the proper value.

Ideally I would have an extra column which would calculate the actual age of the machine, by comparing the PurchaseDate to Today, but I already know a nice way to do this in Excel, so that’s just to play around with.

The basics

Ok, first things first, let’s get some data and declare some variables:

Simple right?

I want to output the details to NewList.csv , so I need to keep that in mind.
Every try will eventually get the following added to the end of it

 

First try

I had thought to use all data from my first array of objects $Computers and put all the relevant data in to a new object.

I start off by creating a blank array called $warranties, which I will later fill up with all required data.

Then I create a new PSObject for each machine in my list called $warranty, which I add to the $warranties array using the +=  assignment operator .
It’s rather clunky,  but hey, it gets the job done!

Second shot

But wait, why don’t I just add a new column to my current array and just use that column instead?

Wow, that looks a lot cleaner and I don’t even need to define all the previous columns.
While in my case this is preferred, there are circumstances when you have TOO much data in your original CSV file and in that case using the First Take solution can be used to clean up the data.

Third time’s a charm

I’m on the right track  now, making my script cleaner, easier and still functional.
Just one small tweak should do it:

Again using the Add-Member Cmdlet to add a property to my $computer object, but this time I give it the Name of an already existing property, using the -Force parameter to override the current value.

Awesome!

Bonus points up ahead!

As mentioned before, ideally I would also like to have a column added which displays the current age of a machine.
In order to do this you need to simply compare today’s date to the PurchaseDate.

Here I calculate the amount of ticks between Now and PurchaseDate which gives me my age.
For some strange reason it increments all values by 1 though, which means that when using this value, you need to subtract 1.

Don’t believe me, give it a shot, but this gives me the information required.

Last but not least, perhaps some people would like to know the formula for in Excel, in case they prefer this:

Happy Scripting! 🙂

Facebooktwittergoogle_plusredditpinterestlinkedinmail