topic Re: Always got zero for count in Splunk Search

Always got zero for count

hongbo_miao — Fri, 18 Sep 2020 22:28:28 GMT

I am trying to count the requests which `message.logType` is "Outgoing Response".

My query is like

index="my_index" | stats count as total, count(eval(message.logType="Outgoing Response")) as outgoingCount by log.request.url | table log.request.url, total, outgoingCount | sort -outgoingCount

My logs are like, they do have `message.logType` field.

However, the outgoingCount column is always 0.

I read https://community.splunk.com/t5/Splunk-Search/stats-count-eval-always-returns-zero/m-p/334406

But does not help in my case.

Any idea? Thanks

Re: Always got zero for count

Nisha18789 — Fri, 18 Sep 2020 22:37:45 GMT

Hi @hongbo_miao , could you please check if you are running your search in fast mode? If yes, try running in verbose mode and see if you get the results.

Re: Always got zero for count

thambisetty — Sat, 19 Sep 2020 04:30:09 GMT

Can you try enclosing message.logType in single quotes?

and also try running search by removing sort command at the end. I believe there should be space between - and field name.

Re: Always got zero for count

ITWhisperer — Sat, 19 Sep 2020 13:59:10 GMT

Try

index="my_index" "Outgoing Response" | head 1

in verbose mode and look to see what fields have been extracted

Re: Always got zero for count

hongbo_miao — Mon, 21 Sep 2020 14:05:52 GMT

Thanks @ITWhisperer this is running

index="my_index" "Outgoing Response" | head 1

in verbose mode.

message.logType does in the field list.

And if I run

index="my_index" | search message.logType="Outgoing Response"

It does return matched events.

Re: Always got zero for count

ITWhisperer — Mon, 21 Sep 2020 13:55:06 GMT

Hi @hongbo_miao

As @thambisetty suggested, try enclosing the field name with dot "." in single quotes (and adding a space after the "-" in the sort command)

index="my_index" | stats count as total, count(eval('message.logType'="Outgoing Response")) as outgoingCount by log.request.url | table log.request.url, total, outgoingCount | sort - outgoingCount

Re: Always got zero for count

hongbo_miao — Mon, 21 Sep 2020 14:07:45 GMT

Thanks @thambisetty but not work.

Re: Always got zero for count

hongbo_miao — Mon, 21 Sep 2020 14:08:28 GMT

Thanks @Nisha18789 no, still not work in verbose mode...

Re: Always got zero for count

thambisetty — Mon, 21 Sep 2020 14:20:54 GMT

index="my_index" | stats count as total, count(eval('message.logType'="Outgoing Response")) as outgoingCount by "log.request.url" | table "log.request.url", total, outgoingCount | sort - outgoingCount

Re: Always got zero for count

hongbo_miao — Mon, 21 Sep 2020 20:25:09 GMT

I tried, but still no luck...

Re: Always got zero for count

hongbo_miao — Mon, 21 Sep 2020 19:35:48 GMT

Thanks, I replied under @thambisetty still not work...

Re: Always got zero for count

to4kawa — Mon, 21 Sep 2020 21:55:14 GMT

index="my_index" | stats count as total, sum(eval(if('message.logType'="Outgoing Response",1,0))) as outgoingCount by "log.request.url" | table "log.request.url", total, outgoingCount | sort - outgoingCount

Re: Always got zero for count

hongbo_miao — Tue, 22 Sep 2020 19:03:18 GMT

Thanks @to4kawa still zero...

Re: Always got zero for count

ITWhisperer — Tue, 22 Sep 2020 19:35:09 GMT

Rather than blurred screenshots, please can you share a raw event or two in a code block, anonymising the data appropriately because there seems to be something that we are all missing which might become clearer if we could see the raw data?

Re: Always got zero for count

Nisha18789 — Tue, 22 Sep 2020 21:49:22 GMT

Hi @hongbo_miao , can you try running below query once, and see you get any results ?

index="my_index"
| search message.logType="Outgoing Response"
| stats count as total, count(eval('message.logType'="Outgoing Response")) as outgoingCount by "log.request.url"
| table "log.request.url", total, outgoingCount
| sort - outgoingCount

Re: Always got zero for count

hongbo_miao — Thu, 24 Sep 2020 17:51:12 GMT

Got some help internally, and it finally works! Really appreciate! Here is the original copy:

---

First issue is that referencing json fields with . notation has some oddities.

The correct syntax would be

| stats count(eval('message.logType'="Outgoing Response")) as outgoingCount

Next, the

| stats <statscmd>(<evalcmd>(<stuff))

syntax can be hairy. Instead try:

index="my_index" | eval outgoingCount = if(message.logType="Outgoing Response", 1, 0) | stats count as total, count(outgoingCount) as outgoingCount by log.request.url | table log.request.url, total, outgoingCount | sort -outgoingCount

Re: Always got zero for count

hongbo_miao — Thu, 24 Sep 2020 17:28:16 GMT

This does not work for me too, but I got some help internally, I posted the answer!

Still thanks for help @Nisha18789 !

Re: Always got zero for count

hongbo_miao — Thu, 24 Sep 2020 17:36:51 GMT

Oh I just got some help internally, I posted the working way.

Really really appreciate for the help @ITWhisperer !

(BTW, I think you might be right about the raw data is not that formatted, although when I check they seem just json / object)