https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317431#M94954 <P>Thanks both of the answers worked!. Thank you very much</P> Fri, 02 Mar 2018 06:01:08 GMT bora9 2018-03-02T06:01:08Z https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317427#M94950 <P>Hello I've been trying to chart/table the following search but I keep getting the wrong sorting for my array.</P> <P>My search :</P> <PRE><CODE>source="rest://RGM Stats" | head 1 | rename intervals{}.end_at as Final intervals{}.wh_del as "Energy Delivered" | table Final "Energy Delivered" </CODE></PRE> <P>My raw:</P> <PRE><CODE>{ "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" } } </CODE></PRE> <P>This is providing me the correct answer and sorting but its giving me the answer as a string.</P> <P>Im trying to convert the answer into rows without loosing the order for both columns but have been unable to do it.</P> <P>Any help is appreciate it. Thanks in advanced.</P> Fri, 02 Mar 2018 02:56:46 GMT https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317427#M94950 bora9 2018-03-02T02:56:46Z https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317428#M94951 <P>Hi @bora9,</P> <P>Can you please try this search?</P> <PRE><CODE>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 </CODE></PRE> <P>another thing I have validated your provided JSON on <A href="https://jsonlint.com/">https://jsonlint.com/</A> which shows invalid near "system_id":00092384. Can you please check also that?</P> <P><STRONG>My Sample work around:</STRONG><BR /> first execute this search:</P> <PRE><CODE>| 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 </CODE></PRE> <P>then execute below search:</P> <PRE><CODE>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 </CODE></PRE> <P>Thanks</P> Fri, 02 Mar 2018 05:08:01 GMT https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317428#M94951 kamlesh_vaghela 2018-03-02T05:08:01Z https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317429#M94952 <P>@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:</P> <PRE><CODE>| 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\":\"(?&lt;end_at&gt;[^\"]+)\"\,\s+\"devices_reporting\":(?&lt;devices_reporting&gt;[^\,]+)\,\s+\"wh_del\":(?&lt;wh_del&gt;\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 </CODE></PRE> Fri, 02 Mar 2018 05:32:25 GMT https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317429#M94952 niketn 2018-03-02T05:32:25Z https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317430#M94953 <P>Thanks works like a charm.</P> Fri, 02 Mar 2018 06:00:33 GMT https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317430#M94953 bora9 2018-03-02T06:00:33Z https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317431#M94954 <P>Thanks both of the answers worked!. Thank you very much</P> Fri, 02 Mar 2018 06:01:08 GMT https://community.splunk.com/t5/Splunk-Search/How-to-convert-string-result-into-rows/m-p/317431#M94954 bora9 2018-03-02T06:01:08Z