Splunk Search

How to convert string result into rows?

bora9
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

kamlesh_vaghela
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

niketn
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!!!"

bora9
Explorer

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

0 Karma

kamlesh_vaghela
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

bora9
Explorer

Thanks works like a charm.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...