Powershell: create chart rds users via canvajs, $overview output not correct

Hi,

I'm trying to make a chart of number of rds users with https://canvasjs.com/
Example: https://canvasjs.com/javascript-charts/multi-series-chart/
I gather the info I need on an hourly basis in $tablerdssessions
which is of type System.Data.DataRow

Group                         NumberOfUsers Date              
-----                         ------------- ----              
TEAM01                               57 29/01/2018 14:16:00
TEAM02                                     3 29/01/2018 14:16:00
TEAM03                                 9 29/01/2018 14:16:00
TEAM04                                  5 29/01/2018 14:16:00
TOTAL                                   74 29/01/2018 14:16:00


I then try to create an $Overview which holds Group and details.
Details are the x, y data needed for the graph.


foreach (group in $tablerds3Sessions){
#Selection of GroupName
$GroupName = ($tablerdssessions | Where-Object group -eq $($group.group)).group
#Selection of NumberofUsers and Date
$rds3groupsessions = $($tablerds3Sessions | Where-Object group -eq $group.group | select NumberOfUsers,Date)
foreach ($row in $rds3groupsessions)
{
      #, to be removed in $DatapointRds3sessionsGroup
      $DatapointRds3sessionsGroup += "{ x: new Date($($row.Date.Year),$($row.Date.Month),$($row.Date.Day),$($row.Date.Hour),$($row.Date.Minute)), y: $($row.NumberOfUsers) },"
}

$Overview += [PSCustomObject] @{
      GroupName           = $GroupName
      Details = $DatapointRds3sessionsGroup
        }
}

$overview should be
GroupName                     Details                                    
---------                     -------                                    
TEAM01                        { x: new Date(2018,1,29,14,00), y: 57 },{ x: new Date(2018,1,29,15,00), y: 57 }
TEAM02                   { x: new Date(2018,1,29,14,00), y: 3 },{ x: new Date(2018,1,29,15,00), y: 2 }  
TEAM03                      { x: new Date(2018,1,29,14,00), y: 9 },{ x: new Date(2018,1,29,15,00), y: 6 }
TOTAL                       { x: new Date(2018,1,29,14,00), y: 69 },{ x: new Date(2018,1,29,15,00), y: 63 }



but is now (TEAM01, TEAM02 etc is added as extra row whereas it should just add details for TEAM01, TEAM02 etc
GroupName                     Details                                    
---------                     -------                                    
TEAM01                        {{ x: new Date(2018,1,29,14,00), y: 57 },}
TEAM02                         {{ x: new Date(2018,1,29,14,00), y: 3 },}  
TEAM03                      {{ x: new Date(2018,1,29,14,00), y: 9 },}
TOTAL                       {{ x: new Date(2018,1,29,14,00), y: 69 },}
TEAM01                     { {x: new Date(2018,1,29,15,00), y: 57 },}
TEAM02                                    {{ x: new Date(2018,1,29,15,00), y: 2 },}

Can you advise?
J.
janhoedtAsked:
Who is Participating?
 
Sajen JoseCommented:
Hi

I created a System.Data.Datatable based object and tried to format the data in the way you suggested, it seems to be working at my end (not sure if I have missed out anything), the code I used is below (you can ignore the section used to create a table with the data using System.data.dataTable object)

#####SCRIPT CODE TO CREATE THE TABLE IN SYSTEM.DATA.DATATABLE FORMAT - CAN IGNORE THIS SECTION#######
cls
$tablename = "SampleTable"
$RDSSessionTable = New-Object -TypeName System.Data.DataTable $tablename
[DateTime]$dateString = "01/29/2018 14:16:00"

#Define Columns
$col1 = New-Object -TypeName System.Data.DataColumn Group,([String])
$col2 = New-Object -TypeName System.Data.DataColumn NumberOfUsers,([int])
$col3 = New-Object -TypeName System.Data.DataColumn DateLogged,([DateTime])

#Add the columns
$RDSSessionTable.Columns.Add($col1)
$RDSSessionTable.Columns.Add($col2)
$RDSSessionTable.Columns.Add($col3)

#Create a Row
$row1 = $RDSSessionTable.NewRow()
$row2 = $RDSSessionTable.NewRow()
$row3 = $RDSSessionTable.NewRow()
$row4 = $RDSSessionTable.NewRow()
$row5 = $RDSSessionTable.NewRow()


#Enter data in the row
$row1.Group = "Team01"
$row1.NumberofUsers = 57
$row1.DateLogged = $dateString

$row2.Group = "Team02"
$row2.NumberofUsers = 3
$row2.DateLogged = $dateString

$row3.Group = "Team03"
$row3.NumberofUsers = 9
$row3.DateLogged = $dateString

$row4.Group = "Team04"
$row4.NumberofUsers = 5
$row4.DateLogged = $dateString

$row5.Group = "Total"
$row5.NumberofUsers = 74
$row5.DateLogged = $dateString


#Add Row to the table
$RDSSessionTable.Rows.Add($row1)
$RDSSessionTable.Rows.Add($row2)
$RDSSessionTable.Rows.Add($row3)
$RDSSessionTable.Rows.Add($row4)
$RDSSessionTable.Rows.Add($row5)

#####SCRIPT CODE TO CREATE THE TABLE IN SYSTEM.DATA.DATATABLE FORMAT - CAN IGNORE THIS SECTION#######

$Overview = @()

foreach($group in $RDSSessionTable)
{   
    $lineObj = New-Object System.Object;
    $lineObj | Add-Member -MemberType NoteProperty -Name GroupName -Value $group.Group
    $lineObj | Add-Member -MemberType NoteProperty -Name Details -Value "{ x: new Date($($group.DateLogged.Year),$($group.DateLogged.Month),$($group.DateLogged.Day),$($group.DateLogged.Hour),$($group.DateLogged.Minute)), y: $($group.NumberOfUsers) }"
    $Overview += $lineObj          
}

$Overview

Open in new window


The output I get is attached as PSOutput.png
PSOutput.png
1
 
janhoedtAuthor Commented:
Thanks! Trying to figure out howto implement it in my script.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.