index=foo stringIdList=["id1","id2","id2","id3","id3","id3"]For the above log format, I wish to get a table with the elements in the stringIdList, and their their count. What should the Splunk query be for the same?
I have tried index=foo | top "navItemIdList" command, but it's not working as expected. I wish to get the count of each element in the list, as a table.
Best Answer
While "the above log format" looks more like a Splunk query than a log entry, this query may fulfill the request.
| makeresults | eval _raw="index=foo stringIdList=[\"id1\",\"id2\",\"id2\",\"id3\",\"id3\",\"id3\"]" ``` Above creates test data. Delete IRL`````` Get the list of elements from the raw event ```| extract pairdelim=" " kvdelim="=" ``` Remove the enclosing brackets ```| eval stringIdList=trim(stringIdList,"[]") ``` Break the list at each comma ```| eval stringIdList=split(stringIdList,",") ``` Put each element into its own event ```| mvexpand stringIdList ``` Count events and group them by element ```| stats count by stringIdListNote that the top command returns the 10 most common values of the specified field. In the example, the 'navItemIdList' field doesn't exist so no results can be returned.