How to convert string result into rows?

[bora9]

Hello I've been trying to chart/table the following search but I keep getting the wrong sorting for my array.

My search :

source="rest://RGM Stats"
| head 1
| rename intervals{}.end_at as Final intervals{}.wh_del as "Energy Delivered" 
| table Final "Energy Delivered"

My raw:

{
   "system_id":00092384,
   "total_devices":1,
   "intervals":[
      {
         "end_at":"2018-03-01T00:15:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T00:30:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T00:45:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T01:00:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T01:15:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T01:30:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T01:45:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T02:00:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T02:15:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T02:30:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T02:45:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T03:00:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T03:15:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T03:30:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T03:45:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T04:00:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T04:15:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T04:30:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T04:45:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T05:00:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T05:15:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T05:30:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T05:45:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T06:00:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T06:15:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T06:30:00-07:00",
         "devices_reporting":1,
         "wh_del":0
      },
      {
         "end_at":"2018-03-01T06:45:00-07:00",
         "devices_reporting":1,
         "wh_del":10
      },
      {
         "end_at":"2018-03-01T07:00:00-07:00",
         "devices_reporting":1,
         "wh_del":142
      },
      {
         "end_at":"2018-03-01T07:15:00-07:00",
         "devices_reporting":1,
         "wh_del":273
      },
      {
         "end_at":"2018-03-01T07:30:00-07:00",
         "devices_reporting":1,
         "wh_del":356
      },
      {
         "end_at":"2018-03-01T07:45:00-07:00",
         "devices_reporting":1,
         "wh_del":428
      },
      {
         "end_at":"2018-03-01T08:00:00-07:00",
         "devices_reporting":1,
         "wh_del":483
      },
      {
         "end_at":"2018-03-01T08:15:00-07:00",
         "devices_reporting":1,
         "wh_del":525
      },
      {
         "end_at":"2018-03-01T08:30:00-07:00",
         "devices_reporting":1,
         "wh_del":566
      },
      {
         "end_at":"2018-03-01T08:45:00-07:00",
         "devices_reporting":1,
         "wh_del":593
      },
      {
         "end_at":"2018-03-01T09:00:00-07:00",
         "devices_reporting":1,
         "wh_del":621
      }
   ],
   "meta":{
      "status":"normal",
      "last_report_at":"2018-03-01T09:03:29-07:00",
      "last_energy_at":"2018-03-01T09:02:51-07:00",
      "operational_at":"2017-09-14T16:39:46-06:00"
   }
}

This is providing me the correct answer and sorting but its giving me the answer as a string.

Im trying to convert the answer into rows without loosing the order for both columns but have been unable to do it.

Any help is appreciate it. Thanks in advanced.

[niketn]

@bora9, try the following run any where search. The makeresults and eval command before rex command are used to mock the data as per the question. The rex command is used to extract both fields you are interested in at the same time and then use mvexpand to generate multiple row:

| makeresults
| eval _raw="{
    \"system_id\":\"00092384\",
    \"total_devices\":1,
    \"intervals\":[
       {
          \"end_at\":\"2018-03-01T00:15:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T00:30:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T00:45:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T01:00:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T01:15:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T01:30:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T01:45:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T02:00:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T02:15:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T02:30:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T02:45:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T03:00:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T03:15:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T03:30:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T03:45:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T04:00:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T04:15:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T04:30:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T04:45:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T05:00:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T05:15:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T05:30:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T05:45:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T06:00:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T06:15:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T06:30:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":0
       },
       {
          \"end_at\":\"2018-03-01T06:45:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":10
       },
       {
          \"end_at\":\"2018-03-01T07:00:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":142
       },
       {
          \"end_at\":\"2018-03-01T07:15:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":273
       },
       {
          \"end_at\":\"2018-03-01T07:30:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":356
       },
       {
          \"end_at\":\"2018-03-01T07:45:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":428
       },
       {
          \"end_at\":\"2018-03-01T08:00:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":483
       },
       {
          \"end_at\":\"2018-03-01T08:15:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":525
       },
       {
          \"end_at\":\"2018-03-01T08:30:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":566
       },
       {
          \"end_at\":\"2018-03-01T08:45:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":593
       },
       {
          \"end_at\":\"2018-03-01T09:00:00-07:00\",
          \"devices_reporting\":1,
          \"wh_del\":621
       }
    ],
    \"meta\":{
       \"status\":\"normal\",
       \"last_report_at\":\"2018-03-01T09:03:29-07:00\",
       \"last_energy_at\":\"2018-03-01T09:02:51-07:00\",
       \"operational_at\":\"2017-09-14T16:39:46-06:00\"
    }
 }"
| rex "\"end_at\":\"(?<end_at>[^\"]+)\"\,\s+\"devices_reporting\":(?<devices_reporting>[^\,]+)\,\s+\"wh_del\":(?<wh_del>\d+)\s+\}" max_match=0
| fields - _time _raw
| eval data=mvzip(end_at,wh_del)
| fields data
| mvexpand data
| eval data=split(data,",")
| eval Final=mvindex(data,0)
| eval Energy_Delivered=mvindex(data,1)
| fields - data

[bora9]

Thanks both of the answers worked!. Thank you very much

[kamlesh_vaghela · Accepted solution]

Hi @bora9,

Can you please try this search?

source="rest://RGM Stats" | head 1 | spath intervals{} output=intervals | rename intervals{}.end_at as end_at, intervals{}.wh_del as wh_del | eval temp=mvzip(end_at,wh_del)| stats count by _time temp | eval end_at=mvindex(split(temp,","),0),wh_del=mvindex(split(temp,","),1) | table end_at wh_del

another thing I have validated your provided JSON on https://jsonlint.com/ which shows invalid near "system_id":00092384. Can you please check also that?

My Sample work around:
first execute this search:

| makeresults | eval _raw="{ \"system_id\":\"00092384\", \"total_devices\":1, \"intervals\":[ { \"end_at\":\"2018-03-01T00:15:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T00:30:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T00:45:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T01:00:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T01:15:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T01:30:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T01:45:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T02:00:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T02:15:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T02:30:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T02:45:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T03:00:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T03:15:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T03:30:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T03:45:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T04:00:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T04:15:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T04:30:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T04:45:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T05:00:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T05:15:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T05:30:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T05:45:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T06:00:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T06:15:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T06:30:00-07:00\", \"devices_reporting\":1, \"wh_del\":0 }, { \"end_at\":\"2018-03-01T06:45:00-07:00\", \"devices_reporting\":1, \"wh_del\":10 }, { \"end_at\":\"2018-03-01T07:00:00-07:00\", \"devices_reporting\":1, \"wh_del\":142 }, { \"end_at\":\"2018-03-01T07:15:00-07:00\", \"devices_reporting\":1, \"wh_del\":273 }, { \"end_at\":\"2018-03-01T07:30:00-07:00\", \"devices_reporting\":1, \"wh_del\":356 }, { \"end_at\":\"2018-03-01T07:45:00-07:00\", \"devices_reporting\":1, \"wh_del\":428 }, { \"end_at\":\"2018-03-01T08:00:00-07:00\", \"devices_reporting\":1, \"wh_del\":483 }, { \"end_at\":\"2018-03-01T08:15:00-07:00\", \"devices_reporting\":1, \"wh_del\":525 }, { \"end_at\":\"2018-03-01T08:30:00-07:00\", \"devices_reporting\":1, \"wh_del\":566 }, { \"end_at\":\"2018-03-01T08:45:00-07:00\", \"devices_reporting\":1, \"wh_del\":593 }, { \"end_at\":\"2018-03-01T09:00:00-07:00\", \"devices_reporting\":1, \"wh_del\":621 } ], \"meta\":{ \"status\":\"normal\", \"last_report_at\":\"2018-03-01T09:03:29-07:00\", \"last_energy_at\":\"2018-03-01T09:02:51-07:00\", \"operational_at\":\"2017-09-14T16:39:46-06:00\" } }"
| collect index=main sourcetype=test

then execute below search:

index=main sourcetype=test | head 1 | spath intervals{} output=intervals | rename intervals{}.end_at as end_at, intervals{}.wh_del as wh_del | eval temp=mvzip(end_at,wh_del)| stats count by _time temp | eval end_at=mvindex(split(temp,","),0),wh_del=mvindex(split(temp,","),1) | table end_at wh_del

Thanks

[bora9]

Thanks works like a charm.