Problem using the output of a powershell script as the data source.

[JonathanH]

I've been having pretty good success getting things working for me, but I ran into a problem with one particular thing I'm trying to accomplish. Most of my gadgets are using SQL as the data source, and they are all working fine, but I ran into one where I need to "massage" the data as I can't get it into exactly the format that I need directly from the SQL database.

 I've gotten the data brought into powershell and got it into exactly the format that I want, and this is all working great.  Then if I pipe the output of all my work to out-chart with the correct parameters it all works great and generates exactly the information I'm looking for.  However in order to get this to refresh it ends up having to be two scripts, one that gets the data and outputs it and then the second that calls the first and pipes it to out-chart.  Again that all works just fine.  For consistancy's sack however I'd really like to get this whole thing working from a .pgf file so that it's easier to edit the formatting of the chart later with powergadgets creator.  This is where I run into problems.  If I select the Powershell script (the first one that actually returns the data...) as the source for the chart, I can click test and it shows my data in the results window just fine, however if I click the finish button to try and get it to save that change then it simply refreshes the data in the results wizard and won't go any further... Really frustrating... I've tried a number of different methods but I can't seem to get this to work at all.  I've tried building the data as an array of objects, or as a datatable, and either method "looks" the same on the Powershell output screen and works find with the out-chart method, but does *not* work trying to use it as the source in powergadgets creator.

 My data consists basically of a series of data pairs with a company name paired with a percentage value from 0 to 100.

 What am I doing wrong?  What format does the data returned from the powershell script need to be in for it to work properly with powergadget creator?

 Thanks,

 Jonathan

[marco.shaw]

You mention you have 2 scripts.  What does the 2nd script look like that you're using to call the first, and pipe to out-chart?

You're asking a great question about what kind of objects the Creator expects to see.  I'll blog that maybe next week.

[JonathanH]

The second script is pretty straight forward.  It's simply in the form of

script1.ps1 | out-chart -refresh 00:10:00 -floating .... etc etc.. Other options to format the chart the way I wanted..

I then actually simplified that with the use of a template so that it's down to just

script1.ps1 | out-chart -template myscript.pgt -refresh 00:10:00

 The only reason that I even have it as a script rather than a manual entry is I've got several other users who need to be able to run it without having to understand all of the "command line stuff", that goes along with it.

 Script1, basically selects two columns from a database table, one that has the name of a company, and the other that has a series of comma separated values. I then have to take those comma separated values, and use them to perform a calculation which becomes my 0-100 value in the output..  I tried a couple of different ways of building the output and my last attempt looks something like this..

# $result is the variable holding the return of my SQL query from invoke-sql..

$i = 0

#Buld our output table

$output = New-Object System.Data.Datatable "ResultTable"

$col1 = New-Object system.Data.DataColumn Company,([string])

$col2 = New-Object system.Data.DataColumn Score,([decimal])

#Add the Columns

$output.columns.add($col1)

$output.columns.add($col2)

while ( $i -lt $result.count)

{

#Do a bunch of stuff with the comma separated value field to calculate a score...

  .....

#$score = final calculated value, number from 0 to 100.

  $row = $output.newRow()

  $row.Company = $result[$i]["Company"]

  $row.Score = $score

  $output.Rows.Add($row)

  $i += 1

}

$output

 

 

[marco.shaw]

I would have thought the 2 would work the same.  I'll try to check this out, but it could be next week at this point.

[JonathanH]

I would have thought so too which is why this was so puzzling to me.  No big rush, since as I say it's working fine for me with the out-chart method, so I have a work around for now, but I'd really like to get it working with the powergadget's creator too, or at least figure out why it's *not* working.  Like I say when I try to use the first script as a source for the PowerGadgets creator, it "looks" like it's going to work.  I can run the "test" and the data shows up properly in the "results" pane, but clicking the "Finish" button, just refreshes the test results and won't ever let me actually save the change. 

At first I thought the problem might be due to something odd in the data itself, so I modified the data set.  Originally I had the "Score" values as floating point with varying precision, I rounded them to Integers.  That didn't make a difference. Then I thought maybe some odd character in the Company names might be causing the issue (specifically a couple of the companies had "&" in the company name), so I did a character replace on those characters, that didn't make a difference.  Had a couple of company names that were quite large, so I truncated them all to 10 characters or less, and that didn't make a difference either.  Overall, I'm stumped.

[marco.shaw]

OK, I think I know what's going on..  The .PGF file is just a text file, look at my test when I open it with Notepad:

...

  <GadgetVersion>1</GadgetVersion>   <RefreshInterval>10000</RefreshInterval>   <RefreshEnabled>True</RefreshEnabled>   <Data>PowerGadgets.Commands.PowershellSource</Data>   <Data.PrevCommands>& 'c:\users\administrator\desktop\creator.ps1'</Data.PrevCommands>

...

You mentioned you're pasting the PowerShell code into the script window, but I think you should be referencing a PowerShell script as I do above.

That script just has this as contents:

gps powershell|select processname,cpu

 

[JonathanH]

No, we must have had a mis-understanding.  I'm not pasting the powershell script into the script window, I'm referencing the script.  So in the Powershell script window I simply have the script name for the first of the two scripts.. getCompanyScore.ps1.

So the source for the data in the PGF file is the same as the script I call in my "second" script in the working example using out-chart where I basically have the code as ...

getCompanyScore.ps1 | out-chart -template CompanyScore.pgt

 

[marco.shaw]

I did try a very basic test, and I do agree that it appears that if you call out-chart from a PowerShell script versus what things end up looking like when use the creator wizard can be a bit different.

I may try to come back to think about this at another time (as my free spare time is pretty much zero these days), but it might require getting PG devs involved to confirm the different behavior.

Either Juan from SoftwareFX may chime in or you may also want to contact support AT powergadgets.com also.