Running a Powershell script as a Scheduled task which uses the Excel COM object (Open, refresh data, save & Close)

This issue has been giving me a bit of grief lately – I’ve been doing some funky things with Excel and SQL Server Integration services to extract data from the web, do some pre-processing, then import it into a database and writing a report (in SSRS).

Apparently Excel is set up to require that automation tasks are not scheduled – The app throws an error onto the instance running in the scheduled task, but as you cannot see it you cannot troubleshoot it Sad smile

I was looking around the interweb for a solution and came across this thread (Run Powershell script as Scheduled task, that uses Excel COM object) in TechNet which gave me the answer that worked:

You have to create a folder (or two on a 64bit-windows):

(32Bit, always required)

C:\Windows\System32\config\systemprofile\Desktop

(64Bit)

C:\Windows\SysWOW64\config\systemprofile\Desktop

My real gripe here is that it would be so much easier if (when you are logged into the server using the account the task runs under) it actually showed you the dialog (you know, like a “troubleshooting view” which exposes everything directly to the interface but one you could switch on and off as part of the task’s settings). Interactive setting (in the task) does not work in this case, and it’s really frustrating when no errors appear in the event log or on the screen.

Advertisements

About Brad Saide

I'm a SharePoint consultant. I'm also slowly going bald, seem to have a permanent spare tyre around my waist and enjoy socialising with friends over a beer or 10. The last 2 may possibly be related. Started working with SharePoint when the first version was in limited beta release (participated in the Technology Adoption Program while at Woolworths) and have been committed to the adoption of the technology as a business enabler ever since.
This entry was posted in Uncategorized. Bookmark the permalink.

7 Responses to Running a Powershell script as a Scheduled task which uses the Excel COM object (Open, refresh data, save & Close)

  1. laderio says:

    And it does not work on server 2012. Any idea?

    • Brad Saide says:

      Hi laderio – tell me what you are trying to do and the scripts you are running to do it, and finally the error you get that leads you to believe that it doesn’t work, and I’ll have a look at it…

  2. laderio says:

    Hi Brad,

    I’m running a powershell script on Server 2012, which gets me MS Cluster Computer Objects out of AD, does some queries to check for the health status of the cluster, the nodes and the cluster groups and writes the results to excel.
    When starting this script manual everything works fine. But when I schedule the script it does not work. When writing to csv instead of excel everything is fine.
    So I tried to create the folders, but this seems not to work at server 2012. The script still does not run correctly. No error is shown or I look at the wrong places.

    Any Idea? Do you have a server 2012 where it works?

    Greetings
    laderio

    • Brad Saide says:

      Hi Ladero. 2 Questions:
      Have you tried it on a 2008 R2 server? If so, does it work there / If not, can you do so and post the results?
      Can you post the script, so I can have a look at it?

      Thanks!

      • laderio says:

        Sorry for the late answer.
        I have no 2008 R2 Test Server where I could test the script.
        Here ist the script, but it may not be very easy to read:

        import-module activedirectory
        import-module FailoverClusters

        function get-adcomputersrv {
        $Computerlist = @()
        $Computerlist += Get-ADComputer -Filter * -Properties OperatingSystem, servicePrincipalName, lastLogonTimestamp, whenCreated
        Write-Output $Computerlist
        }

        function get-adcluster {
        $computers = get-adcomputersrv
        $computerlist = @()
        foreach ($computer in $computers) {
        $cluster = $false
        foreach ($spn in $computer.servicePrincipalName) {
        if ($spn -like “MSServerCluster/*”) {
        $cluster = $true
        }
        }
        if ($cluster) { $computerlist += $computer }
        }
        Write-Output $computerlist
        }

        function get-ClusterHealth ($cluster, $hours) {
        $CluHealth = @()
        $CluEvents = @()
        $Nodes = Get-Cluster $cluster | Get-ClusterNode | select Name, State
        $clustergroups = get-cluster $cluster | Get-ClusterGroup
        $onlineNodes = @()
        $ownerNodes = @()
        foreach ($clustergroup in $clustergroups) {
        # Online
        $onlineNodes += $clustergroup.ownernode.Name
        # Owner
        $ownerNodes += ($clustergroup | Get-ClusterOwnerNode).OwnerNodes.Name
        }
        foreach ($node in $nodes) {
        #$IsOnlineNode = $false
        #foreach ($onlineNode in $onlineNodes) {
        # if ($node.name -like $onlineNode) {
        # $IsOnlineNode = $true
        # }
        #}
        if (($clustergroups | ? { $_.Name -like “Cluster Group” }).OwnerNode.Name -like $node.Name) { $QuorumOnline = $true }
        else { $QuorumOnline = $false }
        $CluGroupOnline = ($clustergroups | ? { $_.OwnerNode -like $Node.Name -and -not $_.isCoreGroup }).count
        $IsOwnerNode = $false
        foreach ($ownerNode in $ownerNodes) {
        if ($node.name -like $ownerNode) { $IsOwnerNode = $true }
        }
        $CluGroupWrongOwner = @()
        $CluGroupsFailed = @()
        foreach ($clustergroup in $clustergroups) {
        if ($clustergroup.OwnerNode -like $node.name) {
        if (($clustergroup | Get-ClusterOwnerNode).OwnerNodes.Name -ne $null -and -not (($clustergroup | Get-ClusterOwnerNode).OwnerNodes.Name -contains $node.Name)) {
        $CluGroupWrongOwner += $clustergroup.name
        }
        if ($clustergroup.name -ne “Available Storage”) {
        if ($clustergroup.State -ne “Online”) {
        $CluGroupsFailed += $clustergroup.name
        }
        }
        }
        }
        $NodeEvents = Get-WinEventHours $Node.Name $hours | ? { $_.ProviderName -like “Microsoft-Windows-FailoverClustering” }
        $clueventsfailure = $nodeevents | ? { $_.level -eq “1” -or $_.level -eq “2” -or $_.level -eq “3” }
        if ($clueventsfailure) {
        $LastEvent = $clueventsfailure[0].TimeCreated
        }
        else {
        $LastEvent = “”
        }
        $count = $nodeevents | Group-Object -Property level
        $eventscrit = ($count | ? { $_.Name -eq “1” }).Count
        $eventserr = ($count | ? { $_.Name -eq “2” }).Count
        $eventswarn = ($count | ? { $_.Name -eq “3” }).Count
        $Updates = “unknown”
        # $UpdateSession = [activator]::CreateInstance([type]::GetTypeFromProgID(“Microsoft.Update.Session”,$Node.Name))
        # $UpdateSearcher = $UpdateSession.CreateUpdateSearcher()
        # $SearchResult = $UpdateSearcher.Search(“IsAssigned=1 and IsHidden=0 and IsInstalled=0”)
        # $Updates = $($SearchResult.updates.count)
        $Shares = gwmi -ComputerName $node.name win32_clustershare
        $SharesOffline = @()
        foreach ($Share in $Shares) {
        if (-not $Share.Name | test-path) {
        $SharesOffline += $Share.Name
        }
        }
        $drivesover80 = @()
        $drivesover95 = @()
        $drives = gwmi -ComputerName $node.name win32_logicaldisk -filter “drivetype=3”
        foreach ($drive in $drives) {
        if ( (($drive.Size – $drive.FreeSpace) / $drive.Size * 100) -gt 80 ) { $drivesover80 += $drive.DeviceID }
        if ( (($drive.Size – $drive.FreeSpace) / $drive.Size * 100) -gt 95 ) { $drivesover95 += $drive.DeviceID }
        }
        $CluHealth += New-Object psobject -Property @{Cluster=$cluster; Name=$node.name; State=$node.state; LastEvent=$LastEvent; critical=$eventscrit; error=$eventserr; warning=$eventswarn; Owner=$IsOwnerNode; QuorumOnline=$QuorumOnline; CluGroupOnline=$CluGroupOnline; CluGroupWrongOwner=$CluGroupWrongOwner; CluGroupsFailed=$CluGroupsFailed; SharesOffline=$SharesOffline; DrivesOver80=$drivesover80; DrivesOver95=$drivesover95; Updates=$Updates}
        $CluEvents += $NodeEvents
        }
        [hashtable]$Return = @{}
        $Return.CluHealth = $CluHealth
        $Return.CluEvents = $CluEvents
        Write-Output $Return
        }

        #invokes Excel
        $Excel = New-Object -Com Excel.Application
        #creates a new workbook
        $WorkBook = $Excel.WorkBooks.Add()
        $WorkSheet = $WorkBook.WorkSheets.Item(1)
        #sets up the columns
        $intRow = 1
        $WorkSheet.Cells.Item($intRow,1) = date
        $intRow = $intRow + 1
        $WorkSheet.Cells.Item($intRow,1) = “Cluster”
        $WorkSheet.Cells.Item($intRow,2) = “Node”
        $WorkSheet.Cells.Item($intRow,3) = “State”
        $WorkSheet.Cells.Item($intRow,4) = “critical”
        $WorkSheet.Cells.Item($intRow,5) = “error”
        $WorkSheet.Cells.Item($intRow,6) = “warning”
        $WorkSheet.Cells.Item($intRow,7) = “LastEvent”
        $WorkSheet.Cells.Item($intRow,8) = “Owner”
        $WorkSheet.Cells.Item($intRow,9) = “QuorumOnline”
        $WorkSheet.Cells.Item($intRow,10) = “CluGroupsOnline”
        $WorkSheet.Cells.Item($intRow,11) = “CluGroupWrongOwner”
        $WorkSheet.Cells.Item($intRow,12) = “CluGroupsFailed”
        $WorkSheet.Cells.Item($intRow,13) = “SharesOffline”
        $WorkSheet.Cells.Item($intRow,14) = “DrivesOver80”
        $WorkSheet.Cells.Item($intRow,15) = “DrivesOver95”
        $WorkSheet.Cells.Item($intRow,16) = “Updates”
        $CellRange = $WorkSheet.UsedRange
        $Cellrange.EntireColumn.AutoFit()
        $Excel.Visible = $True

        $clusters = get-adcluster | sort-object name
        $CluHealth = @()

        $grün = 4
        $gelb = 6
        $orange = 45
        $rot = 3

        $intRow = $intRow + 1
        $CluEvents = @()
        foreach ($cluster in $clusters) {
        $range = $WorkSheet.Range(“A$intRow”,”P$intRow”)
        $range.borders.item(8).linestyle = 1
        #$CluHealth = get-ClusterHealth $cluster.name 168
        $return = get-ClusterHealth $cluster.DNSHostName 168
        $CluEvents += $Return.CluEvents
        foreach ($Node in $Return.CluHealth) {
        $Worksheet.Cells.Item($intRow, 1) = $Node.Cluster
        $Worksheet.Cells.Item($intRow, 2) = $Node.Name
        $Worksheet.Cells.Item($intRow, 3) = $Node.State.ToString()
        if ($Node.State -eq 0) { $WorkSheet.Cells.Item($intRow,3).Interior.ColorIndex = $grün } else {
        if ($Node.State -eq 2) { $WorkSheet.Cells.Item($intRow,3).Interior.ColorIndex = $gelb } else { $WorkSheet.Cells.Item($intRow,3).Interior.ColorIndex = $rot }
        }
        $Worksheet.Cells.Item($intRow, 4) = $Node.critical
        if ($Node.critical -ne 0) { $WorkSheet.Cells.Item($intRow,4).Interior.ColorIndex = $rot } else { $WorkSheet.Cells.Item($intRow,4).Interior.ColorIndex = $grün }
        $Worksheet.Cells.Item($intRow, 5) = $Node.error
        if ($Node.error -ne 0) { $WorkSheet.Cells.Item($intRow,5).Interior.ColorIndex = $orange } else { $WorkSheet.Cells.Item($intRow,5).Interior.ColorIndex = $grün }
        $Worksheet.Cells.Item($intRow, 6) = $Node.warning
        if ($Node.warning -ne 0) { $WorkSheet.Cells.Item($intRow,6).Interior.ColorIndex = $gelb } else { $WorkSheet.Cells.Item($intRow,6).Interior.ColorIndex = $grün }
        if ($Node.LastEvent) { $LastEvent = Get-Date -uformat “%Y-%m-%d %H:%M” $Node.LastEvent } else { $LastEvent = “” }
        $Worksheet.Cells.Item($intRow, 7) = $LastEvent
        if ($Node.LastEvent) {
        if ( $Node.LastEvent -gt (Get-Date).AddHours(-24) ) {
        $WorkSheet.Cells.Item($intRow,7).Interior.ColorIndex = $rot
        }
        else {
        if ( $Node.LastEvent -gt (Get-Date).AddHours(-72) ) {
        $WorkSheet.Cells.Item($intRow,7).Interior.ColorIndex = $orange
        }
        else {
        $WorkSheet.Cells.Item($intRow,7).Interior.ColorIndex = $gelb
        }
        }
        } else { $WorkSheet.Cells.Item($intRow,7).Interior.ColorIndex = $grün }
        $Worksheet.Cells.Item($intRow, 8) = $Node.Owner
        $Worksheet.Cells.Item($intRow, 9) = $Node.QuorumOnline
        $Worksheet.Cells.Item($intRow, 10) = $Node.CluGroupOnline
        $WrongOwners = $Null
        foreach ($WrongOwner in $Node.CluGroupWrongOwner) {
        $WrongOwners += $WrongOwner + ” ”
        }
        $Worksheet.Cells.Item($intRow, 11) = $WrongOwners
        if ($WrongOwners) { $WorkSheet.Cells.Item($intRow,11).Interior.ColorIndex = $gelb } else { $WorkSheet.Cells.Item($intRow,11).Interior.ColorIndex = $grün }
        $CluGroupsFailed = $Null
        foreach ($CluGroupFailed in $Node.CluGroupsFailed) {
        $CluGroupsFailed += $CluGroupFailed + ” ”
        }
        $Worksheet.Cells.Item($intRow, 12) = $CluGroupsFailed
        if ($Node.CluGroupsFailed) { $WorkSheet.Cells.Item($intRow,12).Interior.ColorIndex = $gelb } else { $WorkSheet.Cells.Item($intRow,12).Interior.ColorIndex = $grün }
        $SharesOffline = $Null
        foreach ($ShareOffline in $Node.SharesOffline) {
        $SharesOffline += $ShareOffline + ” ”
        }
        $Worksheet.Cells.Item($intRow, 13) = $SharesOffline
        if ($SharesOffline) { $WorkSheet.Cells.Item($intRow,13).Interior.ColorIndex = $gelb } else { $WorkSheet.Cells.Item($intRow,13).Interior.ColorIndex = $grün }
        $DrivesOver80 = $Null
        foreach ($HighDrive in $Node.DrivesOver80) {
        $DrivesOver80 += $HighDrive + ” ”
        }
        $Worksheet.Cells.Item($intRow, 14) = $DrivesOver80
        if ($Node.DrivesOver80) { $WorkSheet.Cells.Item($intRow,14).Interior.ColorIndex = $gelb } else { $WorkSheet.Cells.Item($intRow,14).Interior.ColorIndex = $grün }
        $DrivesOver95 = $Null
        foreach ($HighDrive in $Node.DrivesOver95) {
        $DrivesOver95 += $HighDrive + ” ”
        }
        $Worksheet.Cells.Item($intRow, 15) = $DrivesOver95
        if ($Node.DrivesOver95) { $WorkSheet.Cells.Item($intRow,15).Interior.ColorIndex = $orange } else { $WorkSheet.Cells.Item($intRow,15).Interior.ColorIndex = $grün }
        $Worksheet.Cells.Item($intRow, 16) = $Node.Updates
        $intRow++
        }
        $CellRange = $WorkSheet.UsedRange
        $Cellrange.EntireColumn.AutoFit()
        }
        $CellRange = $WorkSheet.UsedRange
        $Cellrange.Font.Bold = $True
        $Cellrange.EntireColumn.AutoFit()
        $WorkSheet.Name = “Health”

        $WorkSheet = $WorkBook.WorkSheets.Item(2)
        #sets up the columns
        $intRow = 1
        $WorkSheet.Cells.Item($intRow,1) = date
        $intRow = $intRow + 1
        $WorkSheet.Cells.Item($intRow,1) = “MachineName”
        $WorkSheet.Cells.Item($intRow,2) = “TimeCreated”
        $WorkSheet.Cells.Item($intRow,3) = “Level”
        $WorkSheet.Cells.Item($intRow,4) = “ID”
        $WorkSheet.Cells.Item($intRow,5) = “Description”
        foreach ($CluEvent in $CluEvents) {
        $intRow = $intRow + 1
        $Worksheet.Cells.Item($intRow, 1) = $CluEvent.MachineName
        $Worksheet.Cells.Item($intRow, 2) = $CluEvent.TimeCreated
        $Worksheet.Cells.Item($intRow, 3) = $CluEvent.Level
        $Worksheet.Cells.Item($intRow, 4) = $CluEvent.ID
        #1069 Cluster Resource Failed
        #1129 Cluster network partitioned
        #1126 Cluster network interface unreachable
        #1127 Cluster network interface failed
        #1129 Cluster network is partitioned
        #1135 Cluster node was removed from cluster membership
        #1205 Cluster Service failed to bring clustered application completely online or offline
        #1282 Security Handshake
        #1548 Different but compatible version of cluster service software
        #$Worksheet.Cells.Item($intRow, 5) =
        }
        $CellRange = $WorkSheet.UsedRange
        $Cellrange.Font.Bold = $True
        $Cellrange.EntireColumn.AutoFit()
        $WorkSheet.Name = “Events”

        $WorkBook.WorkSheets.Item(3).Delete

        $date = Get-Date -uformat “%Y-%m-%d”
        $file = “D:\results\ClusterHealth $date.xlsx”
        del $file
        $WorkBook.SaveAs($file)
        #$WorkBook.Close()
        #$Excel.Quit()

      • laderio says:

        Sorry, you also need this function:

        function get-WinEventHours ($computer, $hours) {
        $events = Get-WinEvent -ComputerName $computer -LogName system -MaxEvents 500 | ? { $_.TimeCreated -gt ((Get-Date).AddHours(-$hours)) }
        if ($events.count -gt 490) {
        $events = Get-WinEvent -ComputerName $computer -LogName system -MaxEvents 5000 | ? { $_.TimeCreated -gt ((Get-Date).AddHours(-$hours)) }
        if ($events.count -gt 4900) {
        $events = Get-WinEvent -ComputerName $computer -LogName system | ? { $_.TimeCreated -gt ((Get-Date).AddHours(-$hours)) }
        }
        }
        Write-Output $events
        }

  3. Pingback: Part 5 – Power Query API – Refreshing Data Indeed! | SouthBayDBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s