Creating sub-tables from results

Welcome Forums General PowerShell Q&A Creating sub-tables from results

This topic contains 10 replies, has 2 voices, and was last updated by

 
Participant
2 weeks, 2 days ago.

  • Author
    Posts
  • #126746

    Participant
    Points: 123
    Rank: Participant

    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
    
    
  • #126749

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    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.

  • #126764

    Participant
    Points: 123
    Rank: Participant

    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
    
    

     

     

  • #126768

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    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
    
  • #126779

    Participant
    Points: 123
    Rank: Participant

    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
    
    
  • #126788

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    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
    
  • #126795

    Participant
    Points: 123
    Rank: Participant

    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
    
     
    
    
  • #126822

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    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())
    
  • #126825

    Participant
    Points: 123
    Rank: Participant

    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
    
    

     

  • #126827

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    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) ) {
    ...
    
  • #126911

    Participant
    Points: 123
    Rank: Participant

    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
    
    

You must be logged in to reply to this topic.