Archive | Automatic Documentation RSS for this section

Writing to Excel

Background

About a year ago I started writing things to Excel with PowerShell, based on the information I found on an MSDN blog – Create Excel file with PowerShell

I wasn’t quite satisfied, though. I wanted some more formatting (specifically: change the top row behavior). It was somewhat frustrating to not be able to get everything the way I wanted, thus I decided to start finding out how to do this. It started early evening and late night (or early morning) I was finally satisfied with all the things I could do with Excel. I decided to share the script and struggles with you. I couldn’t find a place which had all these customizations together with creating the Excel file, thus this might help you save a lot of time when trying to reach the same goal.

Goal

My goal is to freeze the top row, set the text at least to bold (depending on difficulty in setting the font properties) and set an autofilter on the top row. And of course to fill the Excel workbook with data. I also want to refit the column width based on its text.

In real life I use it to document things (who likes documenting and why document things if you can have the computer doing it for you?), in this Example, I’ll use it to write the output of the Get-Process command to get a list of processes, process IDs, UserNames and Sessions (in the example, the CPU object is also documented)

Writing the script

With the Get-Process command including the IncludeUserName parameter in my script, I need to run this as administrator. There are several ways to achieve this, I found these two options on the net: Start script as administrator & Check if user is administrator (nearly last comment from MOW)

Combining those two gets met to these lines of code (check if the user started the script as administrator & check if the user can be an administrator. If the script wasn’t started as admin and the security settings allow it, it’ll start the script as admin, otherwise it’ll give an error that you need to be an admin to be able to run this script):

$IsAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")
$CanBeAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]"$env:USERNAME").IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")

If(!($IsAdmin))
{
   If($CanBeAdmin)
   {
       $Arguments = "& '" + $MyInvocation.MyCommand.Definition + "'"
       Start-Process powershell -Verb runAs -ArgumentList $Arguments
   }
   Else
   {
       Write-Error "You have to be an administrator to run this script" -ErrorAction Stop
   }
   Break
}

These lines go in the top of the script. The script breaks if the user isn’t an admin.

When you live in a non US country (which are more people that actually live in the US, but ok), your CurrentCulture settings will probably have you run into problems when trying to use the Excel com object. The following Error will probably appear on many of your Excel com object calls:

Exception calling "<name>" with "<int>" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
At line:<int> char:<int>
+ <PSObject>.<call> <<<< ()
   + CategoryInfo         : NotSpecified: (:) [], MethodInvocationException
   + FullyQualifiedErrorId : ComMethodTargetInvocation

Thus we’ll have to add this line of code to set the CurrentCulture to US:

[System.Threading.Thread]::CurrentThread.CurrentCulture = "en-US"

Now we can start with the Excel code. In my example I delete the Excel file if it exists, before I start a new one. This makes the code obsolete to Open & Save instead of Create & SaveAs, but I left it in there as a reference. Then came the struggle of setting the top row defaults.

The bold part was pretty easy and straightforward:

$Range = $sheet.UsedRange
$Range.Font.ColorIndex = 11
$Range.Font.Bold = $True

Locking the top rows was somewhat harder, but I stumbled upon it when searching for setting the auto filter. Thanks, rwskas on Experts-Exchange

$sheet.Application.ActiveWindow.SplitColumn = 0
$sheet.Application.ActiveWindow.SplitRow = 1
$sheet.Application.ActiveWindow.FreezePanes = $true

With the top rows locked all that’s left is setting the autofilter. Which proved to be pretty hard information to find. Microsoft has information about the Range.AutoFilter Method, but it still left me with questions on how to implement this in PowerShell. I had this C# example from Stack Overflow. Then I found a nearly correct way to use it on powershell.org. Thanks, Tim Pringle. I still needed to know how get the ‘missing type’ and the Excel.XlAutoFilterOperator.xlAnd (from the C# example), which was (probably) the 1 in the PowerShell example. When searching for the XLAutoFilterOperators I stumbled upon a TechNet post with an answer by Boe Prox (Thank you!), there’s the missing type, now I can try it. And … It works! The 1 seems to be xlAnd (the default value) as we thought. This gives us the following code:

$Range.AutoFilter(1, $MissingType, 1, $MissingType, $MissingType) | Out-Null

All that’s left now is refitting the column width based on its text (pretty straightforward)

$Range.EntireColumn.AutoFit() | Out-Null

Then I added an extra line of code, so my example file won’t contain my user name.

If($Process.UserName -eq "$env:USERDOMAIN\$env:USERNAME") { $Process.UserName = "PowershellAdministrator" }

In the end, I quit Excel, release its com object and remove the Excel variable as used in the script.

$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | out-null
Remove-Variable -Name Excel -Scope Global -EA 0

And that’s it. Now you’re automatically writing to Excel. In the end the code isn’t that hard to understand, but to get there can be a pain sometimes. So I hope this’ll help someone trying to reach the same goal(s) as I did.

The script and Excel example file

The Excel example file can be found here, the PowerShell script here.

Happy scripting.

Analyze Robocopy log files and mail the result

A long, long time ago (back in 2008) I created a VBS script which would analyze a folder filled with Robocopy logs and once done analyzing and creating a summary, it would mail the result to me.

A couple of days ago I decided it was time to update this script to Powershell. Once I was finished, I still wasn’t satisfied with its speed (neither was I with the VBS version of the script, but since it ran nightly I didn’t bother changing it). Since I was working on it, I thought I’d completely rewrite it and increase its speed some, together with making it more diverse, so I can use it in more situations and depending on the needed notification settings for each specific situation. Since Microsoft decided to default add Robocopy to the OS, I’ll be using it a lot more myself. I also checked the internet if others created something similar, I found some, but none could compete with what I just created and/or had the diversity I needed; thus thought I’d share it.

I tested this script on Powershell 2.0 and 4.0, so should also work with 3.0. If not, please contact me and I can update/change it. (also include the error please)

Ok, so what does this script? It will read the contents of all robocopy log files in the specified path. If you add -recurse, it’ll also include subfolders. Depending on your settings, it’ll create a summary file with either a summary of all found log files, only the ones with errors and warnings or only the ones with errors. Depending if you want warnings and/or errors, it’ll add detailed information about the errors it found in all the log files. The detailed information for errors will be placed in the top of the summary file. The output file will be placed in the directory the script is started from. Depending on your settings it’ll send an e-mail report with a summary of the analyzed logs and the summary file (with detailed error and/or warning info) will be attached to this mail.

After I finished creating the script, I also added detailed help information and tested its speed.

Reading of 110 files (95,8MB), which consist of 7 errors in 4 files and 402924 warnings in 4 files;
Running time: 2m8s
Tested with option SummaryAll (maximum output) and a MaxLinesPerFile of 500 (thus 2007 lines (500 per warnings file) to write)

Same conditions as above, but tested with SummaryAllWithoutWarnings (thus only 7 lines to write)
Running time: 20s

The Robocopy settings I use (I also use this to make copties of files that can be open by users in their desktop session, that’s why I excluded the ~$ temporary excel files in this script, otherwise you’d get false-positive errors)

robocopy "<source>" "<target>" /XD "<folder>" /XF "~$*.xls" "~$*.xlsx" "~$*.xlsm" /E /V /NP /LOG:"<Path\Logfile>" /R:2 /W:2

Getting eager to try it out yourself? The complete powershell script can be found here: AnalyzeRobocopyLogs.ps1

Use Get-Help .\AnalyzeRobocopylogs.ps1 to get default help information about the script and

Use Get-Help .\AnalyzeRobocopylogs.ps1 -full to get detailed information about the script

If you use this script change it or provide it to / share it with others, please comment me and/or link to my blog.

The E-mail message will look something like this:

AnalyzeRobocopyLogs MailMessage


 

Edit: Updated the script. It appeared that Powershell v2 had an issue with the summary text. This has been updated in the AnalyzeRobocopyLogs v2.1 script.

Get all (non-default) scheduled tasks

I tried querying the scheduled tasks to give me a list of all (non-default) scheduled tasks. I also wanted to have its schedule information, which appeared to be a pretty hard task in the get-service command. After several unsuccessful attempts, I tried something else with great and immediate results. It appeared that the schtasks did give me all the info I need, so I query the tasks by using the schtasks dos tool and saving that information into variables.

Since all default tasks either have no computername, no author, next run time as N/A, or contains the author Microsoft. So first I will query for all scheduled tasks and then filter out those that I don’t need; thus leaving me with the tasks I need for my automatic documentation purposes.

I haven’t tested the script on Windows 2008, but I assume that it uses the same naming as Windows 2012; the commands itself are all Powershell 2 compatible, so should work on 2008 as they also work on Windows 2003.

In this script I don’t remove disabled tasks from the list, because I need to list those myself as well, but I did supply the line that you can add to the where-object part of the $ScheduledTasks to filter those out as well.

The text for every schedule was returned over several properties, of which I make a readable (pretty plain English) text at both Switch parts of the script (one for Windows 2003, the other for 2012 (and presumably 2008)). Beware of the spaces after Daily and Hourly, which schtasks appears to return!

 

#Get all scheduled tasks on the system in a Csv format
$Tasks = schtasks /query /v /fo csv | ConvertFrom-Csv
#Filtering out all Windows tasks for Windows 2k3 and 2k12 (and 2k8?)
$ScheduledTasks = $Tasks | Where-Object { $_.HostName -eq $env:COMPUTERNAME -and $_.Author -ne "N/A" -and $_.'Next Run Time' -ne "N/A" -and $_.Author -notmatch "Microsoft" -and $_.TaskName -notmatch "User_Feed_Synchronization" }
# -and $_.'Scheduled Task State' -ne "Disabled" # <-- Add this to the where-object selection in the line above to filter out disabled tasks as well

Foreach($ScheduledTask in $ScheduledTasks)
{
$Tasktext = ""
$ScheduledTask.'TaskName'.Substring($ScheduledTask.'TaskName'.IndexOf("\")+1)
$ScheduledTask.'Start In'
$ScheduledTask.'Task To Run'
#In case of W2k12 (and W2k8?)
If($ScheduledTask.'Schedule Type')
{
Switch($ScheduledTask.'Schedule Type')
{
"Hourly " { $Tasktext = $ScheduledTask.'Schedule Type' + "at " + $ScheduledTask.'Start Time' }
"Daily " { $Tasktext = $ScheduledTask.'Schedule Type' + "at " + $ScheduledTask.'Start Time' }
"Weekly" { $Tasktext = $ScheduledTask.'Schedule Type' + " on every " + $ScheduledTask.Days + " at " + $ScheduledTask.'Start Time' }
"Monthly"
{
If($ScheduledTask.Months -eq "Every month") { $Tasktext = $ScheduledTask.'Schedule Type' + " on day " + $ScheduledTask.Days + " at " + $ScheduledTask.'Start Time'}
Else { $Tasktext = "Yearly on day " + $ScheduledTask.Days + " of " + $ScheduledTask.Months + " at " + $ScheduledTask.'Start Time' }
}
}
}
#In case of W2k3
If($ScheduledTask.'Scheduled Type')
{
Switch($ScheduledTask.'Scheduled Type')
{
"Hourly " { $Tasktext = $ScheduledTask.'Scheduled Type' + "at " + $ScheduledTask.'Start Time' }
"Daily " { $Tasktext = $ScheduledTask.'Scheduled Type' + "at " + $ScheduledTask.'Start Time' }
"Weekly" { $Tasktext = $ScheduledTask.'Scheduled Type' + " on every " + $ScheduledTask.Days + " at " + $ScheduledTask.'Start Time' }
"Monthly"
{
If($ScheduledTask.Months -eq "JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC") { $Tasktext = $ScheduledTask.'Scheduled Type' + " on day " + $ScheduledTask.Days + " at " + $ScheduledTask.'Start Time' }
Else { $Tasktext = "Yearly on day " + $ScheduledTask.Days + " of " + $ScheduledTask.Months + " at " + $ScheduledTask.'Start Time' }
}
}
}
#This line can be removed if the filter excludes disabled tasks
If($ScheduledTask.'Scheduled Task State' -eq "Disabled") { $Tasktext = "Disabled" }
$Tasktext
}

This leave you with the following 4 variables, which you can use for exporting or other purposes

$ScheduledTask.'TaskName'.Substring($ScheduledTask.'TaskName'.IndexOf("\")+1)
$ScheduledTask.'Start In'
$ScheduledTask.'Task To Run'
$Tasktext

You can download a copy of this script from my skydrive

Get all non-default Windows services

Getting a list of all services isn’t that hard. This can be achieved with Get-Service or with Get-WmiObject win32_service. It seems that Get-Service doesn’t leave me with enough properties to query and/or filter out, and this time the Get-WmiObject queries work quick enough; thus I’ll use this for my code.

I wanted a list of all non-default Windows services in Windows 2012… This I will use in a script that will automatically write documentation for the application servers which I manage.

I couldn’t find somethins like this, so decided to share my work and how I got to the result once I completed it.

It seems that if I filter out most MS services where its caption doesn’t contain ‘Windows’ or it’s path contains the text ‘Windows’

#Get all services where its caption or its pathname doesn't contain Windows
Get-WmiObject win32_service | where { $_.Caption -notmatch "Windows" -and $_.PathName -notmatch "Windows" }

It seems the Microsoft Policy Platform service and the Local Session Manager services don’t have ‘Windows’ mentioned, so I will filter these out by path/name
The Windows 2012 R2 server I query also contains Microsoft Office and System Center Endpoint Security, thus I need to filter these out as well

#Adding exclusion for "policyhost.exe" removes Microsoft Policy Platform service
Get-WmiObject win32_service | where { $_.Caption -notmatch "Windows" -and $_.PathName -notmatch "Windows" -and $_.PathName -notmatch "policyhost.exe" }

#Adding exclusion for service name "LSM" removes the Local Session Manager service
Get-WmiObject win32_service | where { $_.Caption -notmatch "Windows" -and $_.PathName -notmatch "Windows" -and $_.PathName -notmatch "policyhost.exe" -and $_.Name -ne "LSM" }

#Adding exclusion for "OSE.EXE" removes the Office Source Engine Service
Get-WmiObject win32_service | where { $_.Caption -notmatch "Windows" -and $_.PathName -notmatch "Windows" -and $_.PathName -notmatch "policyhost.exe" -and $_.Name -ne "LSM" -and $_.PathName -notmatch "OSE.EXE" }

#Adding exclusion for "OSPPSVC.EXE" removes the Office Software Protection Platform Service
Get-WmiObject win32_service | where { $_.Caption -notmatch "Windows" -and $_.PathName -notmatch "Windows" -and $_.PathName -notmatch "policyhost.exe" -and $_.Name -ne "LSM" -and $_.PathName -notmatch "OSE.EXE" -and $_.PathName -notmatch "OSPPSVC.EXE" }

#Adding exclusion for "Microsoft Security Client" removes Microsoft Security Client (SCEP)
#This leaves us with all non-default services on a Windows 2012 R2 server!
Get-WmiObject win32_service | where { $_.Caption -notmatch "Windows" -and $_.PathName -notmatch "Windows" -and $_.PathName -notmatch "policyhost.exe" -and $_.Name -ne "LSM" -and $_.PathName -notmatch "OSE.EXE" -and $_.PathName -notmatch "OSPPSVC.EXE" -and $_.PathName -notmatch "Microsoft Security Client" }

Conclusion

This returns all non-default services which I need for my automatic documentation (parts of this script will be posted on this blog, in posts like this one). Now I can put them in an object and query their specific items. I listed several properties in the example script below; using $NonDefaultServices | gm will give you all usable properties of your NonDefaultServices object.

$NonDefaultServices = Get-wmiobject win32_service | where { $_.Caption -notmatch "Windows" -and $_.PathName -notmatch "Windows" -and

$_.PathName -notmatch "policyhost.exe" -and $_.Name -ne "LSM" -and $_.PathName -notmatch "OSE.EXE" -and $_.PathName -notmatch
"OSPPSVC.EXE" -and $_.PathName -notmatch "Microsoft Security Client" }

$NonDefaultServices.DisplayName # Service Display Name (full name)
$NonDefaultServices.PathName # Service Executable
$NonDefaultServices.StartMode # Service Startup mode
$NonDefaultServices.StartName # Service RunAs Account
$NonDefaultServices.State # Service State (running/stopped etc)
$NonDefaultServices.Status # Service Status
$NonDefaultServices.Started # Service Started status
$NonDefaultServices.Description # Service Description