Splunk Search

Counting multi-value fields from data

tgow
Splunk Employee
Splunk Employee

I have the following data:

10..20.10.100 - - [11Nov/2011:13:21:16 -0500] "GET /portlets/market_info.json?ID_STUFF=32497,32498,32104,891848,1244022,2474811 HTTP/1.1" 200 3281 "-" "Jakarta Commons-HttpClient/3.1"0/920549

I am creating the "ID_VALUES" field with the following search

index=test ID_STUFF | rex max_match field=_raw "ID_STUFF\=(|\S+,)(?<ID_VALUES>.*?)(\s+|&)" 

The ID_VALUES field is now a multi-value field. I want to extract each value and run additional search commands against it. I have tried the following and it is not working:

index=test ID_STUFF | rex max_match field=_raw "ID_STUFF\=(|\S+,)(?<ID_VALUES>.*?)(\s+|&)"  | mvexpand ID_VALUES | stats count by ID_VALUES

This is not giving me an individual count of each value of the multi-value field of ID_VALUES. My results look like this:

ID_VALUES                                                                     Count 
32497,32498,32104,891848,1244022,2474811                                        2

I want it to look like the following:

ID_VALUES                                                                  Count
32497                                                                         2
32498                                                                         2
32104                                                                         2
891848                                                                        2
1244022                                                                       2
2474811                                                                       2

What is the correct multi-value field search command to pull this together?

Tags (2)
1 Solution

_d_
Splunk Employee
Splunk Employee

Give this a try:

index=test ID_STUFF | rex max_match field=_raw "ID_STUFF\=(|\S+,)(?<ID_VALUES>.*?)(\s+|&)" | makemv delim="," ID_VALUES | mvexpand ID_VALUES | stats count by ID_VALUES

Hope this helps.

> please upvote and accept answer if you find it useful - thanks!

View solution in original post

ajayabburi508
Path Finder

index=test ID_STUFF | rex max_match field=_raw "ID_STUFF=(|\S+,)(?.*?)(\s+|&)" | eval ID_VALUES =split(ID_VALUES ,",") | mvexpand ID_VALUES | stats count by ID_VALUES

0 Karma

tgow
Splunk Employee
Splunk Employee

That was the ticket. I forgot about combining the makemv and mvexpand.

0 Karma

_d_
Splunk Employee
Splunk Employee

Give this a try:

index=test ID_STUFF | rex max_match field=_raw "ID_STUFF\=(|\S+,)(?<ID_VALUES>.*?)(\s+|&)" | makemv delim="," ID_VALUES | mvexpand ID_VALUES | stats count by ID_VALUES

Hope this helps.

> please upvote and accept answer if you find it useful - thanks!

gkanapathy
Splunk Employee
Splunk Employee

Actually, you don't need mvexpand at all, and your search will be more efficient without it. Also, I personally would replace | makemv delim=",",ID_VALUES with | eval ID_VALUES=split(ID_VALUES,","".

Get Updates on the Splunk Community!

Infographic provides the TL;DR for the 2023 Splunk Career Impact Report

We’ve been shouting it from the rooftops! The findings from the 2023 Splunk Career Impact Report showing that ...

Splunk Lantern | Getting Started with Edge Processor, Machine Learning Toolkit ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...