Always got zero for count

[hongbo_miao]

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

[hongbo_miao · Accepted solution]

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

[ITWhisperer]

Try 

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

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

[hongbo_miao]

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.

[ITWhisperer]

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

[hongbo_miao]

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

[to4kawa]

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

[hongbo_miao]

Thanks @to4kawa still zero...

[ITWhisperer]

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?

[hongbo_miao]

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)

[thambisetty]

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.

[hongbo_miao]

Thanks @thambisetty but not work.

[thambisetty]

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

[hongbo_miao]

I tried, but still no luck...

[Nisha18789]

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.

[hongbo_miao]

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

[Nisha18789]

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

[hongbo_miao]

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

Still thanks for help @Nisha18789 !