I have the following query in a form:
| inputlookup input.csv | fields feature values | spath input=values | fields - values | rename {}.count AS count | rename {}.value AS value | eval reading=mvzip(count, value) | mvexpand reading | makemv reading delim="," | eval count=mvindex(reading, 0) | eval value=mvindex(reading, 1) | fields - reading | outputlookup ouput.csv
where input.csv has a column called values with this kind of data:
[{"value":"6","count":835},{"value":"7","count":794},{"value":"5","count":733},{"value":"1","count":680},{"value":"4","count":675},{"value":"2","count":668},{"value":"3","count":615}]
This query works like a charm in the Splunk Web search form, but I cannot make it work in a dashboard/form. I am using the following XML code without success:
<search>
<query>| inputlookup input.csv | fields feature values | spath input=values | fields - values | rename {}.count AS count | rename {}.value AS value | eval reading=mvzip(count, value) | mvexpand reading | makemv reading delim="," | eval count=mvindex(reading, 0) | eval value=mvindex(reading, 1) | fields - reading | outputlookup output.csv</query>
</search>
Am I missing something? I suspect that there must be a problem with escaping characters but I am not fully sure.
@jacubero
Can you please try this?
<search>
<query>| inputlookup input.csv | fields feature values | spath input=values | fields - values | rename "{}.count" AS count | rename "{}.value" AS value | eval reading=mvzip(count, value) | mvexpand reading | makemv reading delim="," | eval count=mvindex(reading, 0) | eval value=mvindex(reading, 1) | fields - reading | outputlookup output.csv</query>
</search>
OR
<search>
<query><![CDATA[| inputlookup input.csv | fields feature values | spath input=values | fields - values | rename "{}.count" AS count | rename "{}.value" AS value | eval reading=mvzip(count, value) | mvexpand reading | makemv reading delim="," | eval count=mvindex(reading, 0) | eval value=mvindex(reading, 1) | fields - reading | outputlookup output.csv]]></query>
</search>
Thanks
@jacubero
Can you please try this?
<search>
<query>| inputlookup input.csv | fields feature values | spath input=values | fields - values | rename "{}.count" AS count | rename "{}.value" AS value | eval reading=mvzip(count, value) | mvexpand reading | makemv reading delim="," | eval count=mvindex(reading, 0) | eval value=mvindex(reading, 1) | fields - reading | outputlookup output.csv</query>
</search>
OR
<search>
<query><![CDATA[| inputlookup input.csv | fields feature values | spath input=values | fields - values | rename "{}.count" AS count | rename "{}.value" AS value | eval reading=mvzip(count, value) | mvexpand reading | makemv reading delim="," | eval count=mvindex(reading, 0) | eval value=mvindex(reading, 1) | fields - reading | outputlookup output.csv]]></query>
</search>
Thanks
It works great! Thank you.
Glad to help you.
Hello @jacubero
I think the issue is with curly brackets.
try to use CDAT tags if that works for you.
As we have many ways to handle this at index time but as you are doing inputlookup so not much option available :
https://docs.splunk.com/Documentation/Splunk/latest/Admin/Propsconf#Structured_Data_Header_Extractio...
https://answers.splunk.com/answers/494335/how-to-deal-with-curly-brackets-in-field-names-cre.html
I tried CDATA as follows but it doesn't work either:
<search>
<query><![CDATA[| inputlookup input.csv | fields feature values | spath input=values | fields - values | rename {}.count AS count | rename {}.value AS value | eval reading=mvzip(count, value) | mvexpand reading | makemv reading delim="," | eval count=mvindex(reading, 0) | eval value=mvindex(reading, 1) | fields - reading | outputlookup output.csv]]></query>
</search>