Import-CSV to $Cache

[scott.mcallister]

Hey again!

to fix a performance issue I was having i moved a lot of the queries I was running within my dashboard out into automation tasks which run fine and output files to CSV, where as they used to run and populate (for example) $cache:yesterdsayssentmail (which worked totally fine)

So now I’m trying to import those csv’s within my scheduled endpoint but regardless of the way i do it, it doesn’t seem to pull them in!

not quite sure what I’m missing here

code dump. …

$darkdefault = . (Join-Path $PSScriptRoot "\themes\darkdefault.ps1")
$todaysdate = (Get-Date).tostring("yyyyMMdd")
<# $dailyschedule = New-UDEndpointSchedule -Every 1 -Day
$hourlyschedule = New-UDEndpointSchedule -Every 1 -Hour #>
$30minuteschedule = New-UDEndpointSchedule -Every 30 -Minute

$YesterdaysEndpoint = New-UDEndpoint -Schedule $30minuteschedule -Endpoint {
    $Cache:YesterdaysSentMail = @()
    $Cache:YesterdaysReceivedMail = @()
    $Cache:YesterdaysSentMail = Import-Csv ".\data\sentyesterday.csv"
    $Cache:YesterdaysReceivedMail = Import-Csv ".\data\receivedyesterday.csv"
}

$TodaysEndpoint = New-UDEndpoint -Schedule $30minuteschedule -Endpoint {
    $Cache:TodaysReceivedMail = @()
    $Cache:TodaysSentMail = @()
    $Cache:TodaysReceivedMail = Import-Csv (Join-Path $PSScriptRoot "\data\received$todaysdate.csv")
    $Cache:TodaysSentMail = Import-Csv (Join-Path $PSScriptRoot "\data\sent$todaysdate.csv")
}

$darkdefault = . (Join-Path $PSScriptRoot "\themes\darkdefault.ps1")

$Pages = @()
$Pages += New-UDPage -Name 'Home' -Icon Home -Content {
    New-UDRow -Columns {              
        New-UDColumn -Size 4 {
            New-UDChart -Title "Sent mail today & yesterday" -AutoRefresh -Type Bar -Endpoint {  
                @([PSCustomObject]@{Name = 'Sent Today'; 'Value' = $Cache:TodaysSentMail.count }, [PSCustomObject]@{Name = 'Sent Yesterday'; 'Value' = $Cache:YesterdaysSentMail.count }) | Out-UDChartData -DataProperty "Value" -LabelProperty "Name"  
            } -Options @{  
                legend = @{  
                    display = $false  
                }  
            }  
        } 
        New-UDColumn -Size 4 {
            New-UDChart -Title "Received mail today & yesterday" -AutoRefresh -Type Bar -Endpoint {  
                @([PSCustomObject]@{Name = 'Received Today'; 'Value' = $Cache:TodaysReceivedMail.count }, [PSCustomObject]@{Name = 'Received Yesterday'; 'Value' = $Cache:YesterdaysReceivedMail.count }) | Out-UDChartData -DataProperty "Value" -LabelProperty "Name"  
            } -Options @{  
                legend = @{  
                    display = $false  
                }  
            }  
        }            
    }
    New-UdGrid -Title "Sent Mail Today" -Headers @("Sender", "Recipients","Subject") -Properties @("Sender","Recipients","MessageSubject") -AutoRefresh -Endpoint {
        $Cache:TodaysSentMail | Out-UDGridData
    }
    New-UdGrid -Title "Received Mail Today" -Headers @("Sender", "Recipients","Subject") -Properties @("Sender","Recipients","MessageSubject") -AutoRefresh -Endpoint {
        $Cache:TodaysReceivedMail | Out-UDGridData
    }
    New-UdGrid -Title "Sent Mail Yesterday" -Headers @("Sender", "Recipients","Subject") -Properties @("Sender","Recipients","MessageSubject") -AutoRefresh -Endpoint {
        $Cache:YesterdaysSentMail | Out-UDGridData
    }
    New-UdGrid -Title "Received Mail Yesterday" -Headers @("Sender", "Recipients","Subject") -Properties @("Sender","Recipients","MessageSubject") -AutoRefresh -Endpoint {
        $Cache:YesterdaysReceivedMail | Out-UDGridData
    }
}

$Pages += New-UDPage -Name 'Downloads' -Content {}
New-UDDashboard -Pages $Pages -Title 'Email Report' -Theme $darkdefault

Any thoughts?

Scott

[adam]

Could this be a problem with the paths? Try to see if Import-Csv is throwing any errors

$YesterdaysEndpoint = New-UDEndpoint -Schedule $30minuteschedule -Endpoint {
try {
    $Cache:YesterdaysSentMail = @()
    $Cache:YesterdaysReceivedMail = @()
    $Cache:YesterdaysSentMail = Import-Csv ".\data\sentyesterday.csv" -ErrorAction stop
    $Cache:YesterdaysReceivedMail = Import-Csv ".\data\receivedyesterday.csv" -ErrorAction stop
} catch { $_ | Out-File "$Env:Temp\myerror.txt" }
}

[scott.mcallister]

No errors that I can see, I did try a few variations of the specifying the path but no joy.

if I do this just in powershell

$YesterdaysSentMail = Import-Csv ".\data\sentyesterday.csv"

it works as expected

[adam]

I just wonder because the scheduled endpoints run in a background runspace and not in the context of the script so $PSScriptRoot won’t be available and I’m not sure what the default path is when you use .\. That said, if Out-File isn’t reporting errors I’m not sure what’s up. Can you try outputting something at the beginning of the schedule just to make sure it’s running.

$YesterdaysEndpoint = New-UDEndpoint -Schedule $30minuteschedule -Endpoint {
'30mins' | Out-File "$Env:Temp\hello.txt" 
try {
    $Cache:YesterdaysSentMail = @()
    $Cache:YesterdaysReceivedMail = @()
    $Cache:YesterdaysSentMail = Import-Csv ".\data\sentyesterday.csv" -ErrorAction stop
    $Cache:YesterdaysReceivedMail = Import-Csv ".\data\receivedyesterday.csv" -ErrorAction stop
} catch { $_ | Out-File "$Env:Temp\myerror.txt" }
}

[scott.mcallister]

Yup the schedule is definitely running

well at least the initial run.

[adam]

What’s the error in myerror.txt?

[scott.mcallister]

It’s blank!

[adam]

Oh, that’s weird. Seems like it’s ending up in the catch though…not why is there no error logged…

Let’s try this. In the root dashboard.ps1, do this:

$MyRoot = $PSScriptRoot

In the scheduled endpoint do something like:

$YesterdaysEndpoint = New-UDEndpoint -Schedule $30minuteschedule -Endpoint {
'30mins' | Out-File "$Env:Temp\hello.txt" 
try {
    $Cache:YesterdaysSentMail = @()
    $Cache:YesterdaysReceivedMail = @()
    $Cache:YesterdaysSentMail = Import-Csv "$MyRoot\data\sentyesterday.csv" -ErrorAction stop
    $Cache:YesterdaysReceivedMail = Import-Csv "$MyRoot\data\receivedyesterday.csv" -ErrorAction stop
} catch { $_ | Out-File "$Env:Temp\myerror.txt" }
}

[scott.mcallister]

That actually fixed the issue!

The dashboard.ps1 file is located another folder deep from the default path

C:\ProgramData\UniversalAutomation\Repository\dashboard\dashboard.ps1

[adam]

[scott.mcallister]

out of my own curiosity, because I don’t quiet understand…

what was the issue and how was it solved by

$MyRoot = $PSScriptRoot

[adam]

The dashboard.ps1 file is executed just as is within Unviersal Dashboard. This means that the $PSScriptRoot variable is provided by PowerShell itself. So then when we set $MyRoot to $PSScriptRoot, the variable is populated.

When the schedule is run, it’s not actually running a script but just a script block. When running a script block, $PSScriptRoot is null. Universal Dashboard will set all the variables it can in the script block that were available when the script block was created. This script block was created when the dashboard.ps1 file was run.

The problem is that Universal Dashboard can’t set automatic variables like PSScriptRoot because when it goes to execute the script block, PowerShell will overwrite the variable. That’s why when we set a new variable, $MyRoot, it works because PowerShell won’t overwrite that variable.

[scott.mcallister]

Ahh ok I follow you now! thanks for that explanation!

[raymix]

Thanks, that’s actually a great workaround for my issue, too.
Added $ScriptRoot = $PSScriptRoot to the top of my dashboard.ps1 and I can now work with root directory again.
Strange that this only affects framework 3.0 though, $PSScriptRoot works ok with v2.9.
Well, no matter, we’re back in business!