Creating sub-tables from results

firmbyte · November 27, 2018, 9:22am

Please excuse the way I’m asking…

I want to query a Zabbix custom page that displays the jobs running on batch servers. That page is split into 3 groups bu ItemID: v1,v2,v3. Each of those groups may have multiple jobs running at the same time. When I query the custom page using item.get the results are returned grouped by ItemID and the information returned is: ItemID, Host, LastClock and LastValue.

The LastValue can be 1 or more processes that are running on the Host.

How can I display those items in a table, each row with Host and Last Clock and 1 of the values in LastValue ?

$Itemget = @{
jsonrpc = "2.0"
method = "item.get"
params = @{
output = "extend"
itemids = $itemlist
sortfield = "name"
}
auth = $session.result
id = $session.id
} | ConvertTo-Json

$Results = Invoke-RestMethod "$site/api_jsonrpc.php" -ContentType "application/json" -Body $itemget -Method Post
$output = @()
ForEach($item in $Results.result | ?{$_.LastValue -ne ""}) {
$output += [PSCustomObject] @{
Itemid = $item.itemid ;
Host = Get-HostNamefromId($item.hostid) ;
Lastclock = Convert-FromUnixdate($item.lastclock) ;
Lastvalue = $item.lastvalue ;
}
}
$output

rob-simmers · November 27, 2018, 9:32am

It’s going to be much easier to assist you if you provide the JSON that is being parsed and explain what is not working\expected results.

firmbyte · November 27, 2018, 10:26am

Rob, Thanks for responding.

The json is Zabbix: https://www.zabbix.com/documentation/2.0/manual/appendix/api/item/get. How I use it is what’s in the post above…

When I run the process above, item.get, it returns:

Itemid Host Lastclock Lastvalue
------ ---- --------- ---------
258558 hostname 11/27/2018 9:47:19 AM Started: 11/27/2018 9:41:23 AM Process: Sbc.App...

When you look into the LastValue, there is a sub-table of values, see below, and it is this sub-table that I want to display in a table with corresponding Host, LastClock

Started: 11/27/2018 9:41:23 AM Process: Sbc.App.JobHost.exe - "E:\Octopus\prod\blah\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" blag blah cbiOngoingEdfLoad 2196 37823 4e3fca9e-ced5-4e7d-9fad-7a6
48c5a9683
Started: 11/27/2018 9:46:09 AM Process: Sbc.App.JobHost.exe - "E:\Octopus\prod\blah\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" balg blah rptAllAEElections 517 27816 17e9f115-f305-
4f92-b3ca-d797b2a77d10
Started: 11/27/2018 9:46:49 AM Process: Sbc.App.JobHost.exe - "E:\Octopus\prod\blah\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" blah blah rptAEChange 519 27818 17e9f115-f305-4f92-b
3ca-d797b2a77d10

What I’m looking to do it soemthing like this:

Host   LastClock LastValue

hostname 11/27/2018 9:47:19 AM  Started: 11/27/2018 9:41:23 AM Process: Sbc.App.JobHost.exe - "E:\Octopus\prod\blah\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" blah blah cbiOngoingEdfLoad 2196 37823 4e3fca9e-ced5-4e7d-9fad-7a6
48c5a9683

hostname 11/27/2018 9:47:19 AM  Started: 11/27/2018 9:46:09 AM Process: Sbc.App.JobHost.exe - "E:\Octopus\prod\blah\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" blah blah rptAllAEElections 517 27816 17e9f115-f305-
4f92-b3ca-d797b2a77d10

hostname 11/27/2018 9:47:19 AM  Started: 11/27/2018 9:45:52 AM Process: Sbc.App.JobHost.exe - "E:\Octopus\prod\blah\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" balh balh rptAEChange 519 27818 17e9f115-f305-4f92-b
3ca-d797b2a77d10

rob-simmers · November 27, 2018, 10:50am

Give something like this a shot:

$output = foreach ( $item in $Results.result | ?{$_.LastValue}) {
    $Itemid = $item.itemid ;
    $Host = Get-HostNamefromId($item.hostid) ;
    $Lastclock = Convert-FromUnixdate($item.lastclock) ;
    
    foreach ( $value in @($item.lastvalue) ) {
        [PSCustomObject] @{
            Itemid    = $Itemid ;
            Host      = $Host ;
            Lastclock = $Lastclock ;
            Lastvalue = $value ;
        }    
    }
}

$output

firmbyte · November 27, 2018, 11:45am

Thanks Rob, I gave that a try but got an error:

Cannot overwrite variable Host because it is read-only or constant.
At line:4 char:5
+ $Host = Get-HostNamefromId($item.hostid) ;
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Host:String) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : VariableNotWritable

rob-simmers · November 27, 2018, 12:23pm

Sorry, $Host is reserved. Just update the variable to $HostName or anything else.

PS C:\Users\Rob> $Host


Name             : Windows PowerShell ISE Host
Version          : 5.1.17134.407
InstanceId       : a1570b44-f5e2-43c1-9268-a734dcd7a1b2
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : en-US
CurrentUICulture : en-US
PrivateData      : Microsoft.PowerShell.Host.ISE.ISEOptions
DebuggerEnabled  : True
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

firmbyte · November 27, 2018, 12:44pm

it’s still just resulting in 1 line.

PS C:\Users\svn_admin> $output = foreach ( $item in $Results.result | ?{$_.LastValue}) {
$Itemid = $item.itemid ;
$HostName = Get-HostNamefromId($item.hostid) ;
$Lastclock = Convert-FromUnixdate($item.lastclock) ;

foreach ( $value in @($item.lastvalue) ) {
[PSCustomObject] @{
Itemid = $Itemid ;
Host = $HostName ;
Lastclock = $Lastclock ;
Lastvalue = $value ;
}
}
}

$output

Itemid Host Lastclock Lastvalue
------ ---- --------- ---------
258558 hostserver 11/27/2018 12:09:18 PM Started: 11/27/2018 12:05:07 PM Process: Sbc.Ap...

When I check the contents of $value, there is multiple values in it.

PS C:\Users\svn_admin> $value
Started: 11/27/2018 12:05:07 PM Process: Sbc.App.JobHost.exe - "E:\Octopus\prod\blah\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" whoever whoever importOngoingEdf 469 2477 3f156804-b56c-42ea-87be-47377
1a7fba0
Started: 11/27/2018 12:09:09 PM Process: Sbc.App.JobHost.exe - "E:\Octopus\prod\blah\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" whoknows whoknows rptAllPayrollDeductions 450 2647 4f3a
91e7-c5d0-4133-924a-a92f646d09b6

rob-simmers · November 27, 2018, 5:15pm

That’s why it’s easier to look at the JSON, because it would tell me if that is an array or just a string in “Values”. If you do:

$Results.result[0].LastValue.GetType()

What type is it returning? My guess is String. If it is, then you need to make it an array for the loop to work. Something like this:

@($item.lastvalue -split [environment]::NewLine())

firmbyte · November 27, 2018, 5:44pm

Rob, I don’t know how to supply or show the JSON. I’m querying the Zabbix website using the api they provide.

I ran the ‘gettype’ and got:

IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True String System.Object

rob-simmers · November 27, 2018, 6:38pm

Right, the LastValue is a string, not an array of lines. You’ll need to try to split the lines into an array. Typically, you’ll want to split on a new line if possible. Try something like this:

$lastValue = $Results.result[0].LastValue
$test = $lastvalue -split '\r?\n'
$test2 = $lastValue -split [environment]::NewLine

"Split 1 Count:  " -f $test.Count
"Split 2 Count:  " -f $test2.Count

You should see one of the split methods parsed properly and if there are 3 entries, you’ll see one of the split counts indicate 3. Then, assuming split 2 worked, you should be able to do $test2[0] and only return the first line. Then you can update this:

...
foreach ( $value in @($item.lastvalue) ) {
...

to

...
foreach ( $value in @($item.lastvalue -split [environment]::NewLine) ) {
...

firmbyte · November 28, 2018, 9:51am

Rob, I really appreciate your help, and patience, lol.

I think as a result of your help, I’ve got a solution that seems to be giving me what I was looking for…

$Results = @()

ForEach($item in $ItemGetResults.result) { #| ?{$_.itemid -eq 139302} ) {

$output = @()
$output += [PSCustomObject] @{
Itemid = $item.itemid ;
Host = Get-HostNamefromId($item.hostid) ;
Lastclock = Convert-FromUnixdate($item.lastclock) ;
}

$Lastvalues = @()
foreach($value in ($item.lastvalue -split '\r?\n')) {
$Lastvalues += [PSCustomObject] @{
ItemId = $output.Itemid ;
Host = $output.Host ;
LastClock = $output.LastClock ;
JobStarted = if($value){([regex]::Matches($value, '(\d\d/\d\d/\d\d\d\d.+): ')[0].Groups[1].Value).split('Process')[0]}else { "" }
Running = $value ;
}
}
$results += $Lastvalues
}

$Results | Format-List

That results in:

ItemId : 139302
Host : server99
LastClock : 11/28/2018 9:08:42 AM
JobStarted : 11/28/2018 4:00:11 AM
Running : Started: 11/28/2018 4:00:11 AM Process: Sbc.App.JobHost.exe - "E:\Octopus\Applications\env\Gateway\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" balgA NETWORK
rptDataChecksJob 13367 69216 65a9903f-6243-4da3-9316-3d6da2e59e73

ItemId : 139302
Host : server99
LastClock : 11/28/2018 9:08:42 AM
JobStarted : 11/28/2018 9:07:02 AM
Running : Started: 11/28/2018 9:07:02 AM Process: Sbc.App.JobHost.exe - "E:\Octopus\Applications\env\blah.Sbc.Gateway\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" blahB balhB ongoingEdfImportYMCATCMN 48364 536072 c821c075-cf8e-497d-83c8-2895a131aa16

 

ItemId : 258558
Host : server01
LastClock : 11/28/2018 9:07:18 AM
JobStarted : 11/28/2018 9:05:27 AM
Running : Started: 11/28/2018 9:05:27 AM Process: Sbc.App.JobHost.exe - "E:\Octopus\prod\blah.Gateway\2.195.4_3\lib\net45\Sbc.App.JobHost.exe" blah1 blah1 rptCarrierRemittance 3022 18977
e4affd5f-5181-4f7a-a197-ec8193259031

Topic		Replies	Views
Create custom table for showing results PowerShell Help	0	150	December 31, 2011
Q: got trouble getting a hash table / custom object together PowerShell Help	8	130	November 3, 2014
Hashtable driving me mad. PowerShell Help	1	137	May 18, 2017
Output Data to Custom Table PowerShell Help	7	112	June 15, 2016
Script advice for grouping values in tables and email formatting PowerShell Help	2	148	April 14, 2020

Creating sub-tables from results

Related Topics