topic Re: How do I search the count of how many times a keyword appears, not the event count? in Splunk Search

How do I search the count of how many times a keyword appears, not the event count?

PeterChu — Fri, 26 Jun 2015 02:59:09 GMT

Hi All:

How do I write a search to find the count of how many times a keyword appears, not the event count?
As far as I know, |stats count just searches the event count.

ex:
myLog="Helen is a good girl. Helen is beautiful."

I want to know "Helen" occurs with a count of 2.

Thanks a lot.

Re: How do I search the count of how many times a keyword appears, not the event count?

HiroshiSatoh — Fri, 26 Jun 2015 06:23:12 GMT

What in this?

・・・・|eval list=split(_raw," Helen is")|eval count=mvcount(list)-1

However, also counts "XXXHelen is" and "YYYHelen is".

Re: How do I search the count of how many times a keyword appears, not the event count?

MuS — Fri, 26 Jun 2015 07:16:58 GMT

Hi PeterChu,

I don't know if there is a better way to do this; but have a look at this run everywhere example to get an idea how it could be done:

| gentimes start=-1 |  eval myLog="Helen is a good girl. Helen is beautiful." 
| rex field=myLog "(?<word>\S+)" max_match=0 
| mvexpand word 
| search word="Helen"
| stats count  
| eval Count=if(count=="2", "Twice", count) 
| table word, Count

The first line is only to create the event, then I use rex to get the single words and expand it into single value field called word, search for all word="Helen", count them and display the result.

Hope that helps ...

cheers, MuS

Re: How do I search the count of how many times a keyword appears, not the event count?

martin_mueller — Fri, 26 Jun 2015 09:12:53 GMT

Alternate solution avoiding mvexpand so it could be applied to many events at once:

| stats count as text | eval text = "Helen is a good girl. Helen is beautiful."
| eval tokens = lower(replace(text, "\W+", " "))
| makemv tokens
| eval matches = mvfilter(match(tokens, "^helen$"))
| eval count = mvcount(matches)

Replace the first line with your search returning a field text and it'll produce a count for each event.

Re: How do I search the count of how many times a keyword appears, not the event count?

PeterChu — Thu, 02 Jul 2015 09:35:10 GMT

Hi Martin:
Thanks your help, but I still don't know how to apply my search language to replace text.
ex:my search is " sourcetype=test " and the result will be "Helen is a good girl. Helen is beautiful."
Can I use the search cmd to replace the log. Maybe it is likely a subsearch?

Thanks again.

Re: How do I search the count of how many times a keyword appears, not the event count?

martin_mueller — Thu, 02 Jul 2015 09:37:58 GMT

If that's the raw text returned then this should do:

sourcetype=test
| eval tokens = lower(replace(_raw, "\W+", " "))
| makemv tokens
| makemv tokens
| eval matches = mvfilter(match(tokens, "^helen$"))
| eval count = mvcount(matches)

Re: How do I search the count of how many times a keyword appears, not the event count?

PeterChu — Thu, 02 Jul 2015 09:55:50 GMT

Hi Martin:
I use the search command you mentioned above, but the result only can see the log event, can't see anything on statistics.
Maybe I must count _raw to a field ?

Thanks.

Re: How do I search the count of how many times a keyword appears, not the event count?

martin_mueller — Thu, 02 Jul 2015 10:02:56 GMT

You should see a field count in the left bar. Alternatively, add | table _raw count to the end to make it show in the Statistics tab.

Re: How do I search the count of how many times a keyword appears, not the event count?

PeterChu — Thu, 02 Jul 2015 10:13:10 GMT

Nice , if I add "| table _raw count " I can get count=2
Final my search command look like

sourcetype=test
 | eval tokens = lower(replace(_raw, "\W+", " "))
 | makemv tokens
 | eval matches = mvfilter(match(tokens, "^helen$"))
 | eval count = mvcount(matches)
 | table _raw count

I deeply appreciated your kindness .