Splunk Search

Efficiently counting the occurrences of a delimited substring across events.

hugh_lacey
New Member

In my event data, I have a field called "blocks", the content of that field is a comma separated list of blocks.

For example:
block_1,block_4,block_10
block_1,block_3,block_4

I want to be able to count the occurrence of the blocks over time.

So from above example, I should get the following

block_1                 2
block_3                 1
block_4                 2
block_10               1

I have this working via the following, however it is very slow.

<my search>  | makemv delim="," blocks| mvexpand blocks| stats count by blocks

Is there a more efficient way to do this?

Tags (1)
0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

If you know all the expected values for field blocks then you can do this. Here, block_1, block_2, block_3, block_4 and block_10 are expected values of multi-value field blocks.

<my search> 
| foreach block_1,block_2,block_3,block_4,block_10 
    [ eval <<FIELD>>=if(like(blocks, "%<<FIELD>>%"), "yes", null)] 
| stats count(block_*) as block_* 
| transpose column_name=blocks

Here you avoid using mvexpand which is time consuming for large number of events.

0 Karma

hugh_lacey
New Member

Hi manjunathmeti,

Thanks for the suggestion.

Yes, the expected values for field "blocks" is known, currently there are 48 possible values. I tried your suggestion and ran it over 24 hours of data (~4 million events), the mvexpand approach was faster:
mvexpand approach - 178.78 seconds
foreach/eval approach - 222.54 seconds

I'll need to run this search ad hoc over longer timer periods (30 - 90 days), hence the focus on performance.

Thanks for the help.

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 ...