topic Re: Count events with differing strings in same field in Splunk Search

Count events with differing strings in same field

guywood13 — Fri, 24 Sep 2021 09:17:26 GMT

So this search...

index="myindex" source="/data/logs/log.json" "Calculation Complete"

... the results return a MessageBody field which has various different strings in. I need to do the most simple regex in the world (*my string) and then want to count the messages which match that string eventually charting them. I thought this would work, but it just returns 0 for them all.

index="myindex" source="/data/logs/log.json" "Calculation Complete" | stats | count(eval(MessageBody="*my string")) as My_String | count(eval(MessageBody="*your string")) as Your_String | count(eval(MessageBody="*other string")) as Other_String

Help 🙂

Re: Count events with differing strings in same field

richgalloway — Fri, 24 Sep 2021 12:47:22 GMT

There are a few corrections to make here.

1) "*my string" is not a valid regex. In regular expressions, the "*" character means to repeat the previous character zero or more times - which makes no sense when the "*" is the first character. If the "*" is intended to be a wildcard then what you have is a pattern rather than a regex.

2) The stats command and its three count functions must be a single command. Since the pipe character ("|") separates commands, this query has an empty stats command (not allowed) and three count commands (which isn't a thing).

3) The eval function within stats compares strings literally so, in this example, it's checking that the MessageBody field starts with an asterisk and the text "my string".

Try this query

index="myindex" source="/data/logs/log.json" "Calculation Complete" | stats count(eval(like(MessageBody, "%my string"))) as My_String, count(eval(like(MessageBody, "%your string"))) as Your_String, count(eval(like(MessageBody, "%other string"))) as Other_String

Re: Count events with differing strings in same field

guywood13 — Mon, 27 Sep 2021 09:49:36 GMT

Thank you @richgalloway for the explanation. Stats look great but it isn't charting properly and I'm not sure why. Seems to be putting the first count on the X-axis then charting the other two counts.

Re: Count events with differing strings in same field

PickleRick — Mon, 27 Sep 2021 10:28:01 GMT

It _is_ charting properly. It's just the way the chart works. It just does a chart over _rows_ of your data. If you have separate series of data in columns, it charts them alongside. So in your case - since you have all your data in one row, it's a chart of two different variables (Your_String and Other_String) over values of a variable My_String.

That's obviously not what you want, so you should do

| transpose 0

To get your data in a proper aspect.

You might also do some renaming on the resulting fields.

Re: Count events with differing strings in same field

guywood13 — Tue, 28 Sep 2021 15:47:34 GMT

Thanks @PickleRick this did the trick on the chart 🙂