Splunk Search

How to convert string result into rows?

Explorer

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.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

View solution in original post

Explorer

Thanks works like a charm.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!