topic Re: count (all) / Count (unique) = result -> chart in Splunk Search

count (all) / Count (unique) = result -> chart

dexxter275 — Mon, 20 Feb 2017 06:31:10 GMT

Hey all,

I have a logfile looking like this:

Host ----- Message
test ----- Error1
test ----- Error1
prod ----- Error2
prod ----- Error2
test ----- Error2
test ----- Error2
prod ----- Error3
prod ----- Error3

Now i want one chart with three numbers. At first only a unique count of the hosts (2 test, prod), second the full count of the messages (8) and at last the result of the unique count divide the full count (8 / 2 = 4).

I tried it with transcaption and where eventcode=1 to make a count without duplicates. That works very well.
And I found a way to make calculations. But I don't know how to combine both.

Hope you can help me, thanks for all.

dexxter275

Re: count (all) / Count (unique) = result -> chart

niketn — Mon, 20 Feb 2017 08:37:43 GMT

Try the following. Use eventstats to compute Total stats and add the the events.

<Your Base Search>
| eventstats count(Message) as FullCount
| stats dc(Host ) as UniqueCount last(FullCount) as FullCount
| eval ratio=round(FullCount/UniqueCount,2)

Re: count (all) / Count (unique) = result -> chart

dexxter275 — Mon, 20 Feb 2017 08:49:31 GMT

Wow. That works perfectly. Thanks so much 🙂

Re: count (all) / Count (unique) = result -> chart

niketn — Mon, 20 Feb 2017 09:06:21 GMT

@dexxter275... kindly accept if this solved your problem. Let me know otherwise.

Re: count (all) / Count (unique) = result -> chart

dexxter275 — Mon, 20 Feb 2017 12:38:19 GMT

It's me again. Your answer helps me a lot and did exactly what i want. Thank you for that.

I thought about a history about the last 7 days (for every day one line). And found this question:
https://answers.splunk.com/answers/239649/need-to-get-stats-count-by-day.html
They used "bucket _time span=day" to separate the day.

Do you know how I implement this? I tried:

<SEARCH> | bucket date span=day | eventstats count(errormessage) as FullCount | stats dc(machine) as UniqueCount last(FullCount) as FullCount | eval ratio=round(FullCount/UniqueCount,2)

but it doesn't work. The field "16/02/2017" is calling date. I do my best and start searching again but maybe you can help me again.

Thanks so much.

Re: count (all) / Count (unique) = result -> chart

martin_mueller — Mon, 20 Feb 2017 12:51:40 GMT

The pattern of eventstats | stats is terrible. eventstats lifts all data from the indexers to the search head, goes through all data once, passes all data to stats, then stats goes through all data again. Instead, use this:

search | bucket span=1d _time 
| stats count as FullCount dc(machine) as UniqueCount by _time
| eval ratio = round(FullCount/UniqueCount, 2)

Now stats only needs to go over all data once, and the indexers can do the bulk of the work before only returning a tiny resultset to the search head.

Re: count (all) / Count (unique) = result -> chart

dexxter275 — Mon, 20 Feb 2017 12:57:29 GMT

Damn you are good. Thats great and exactly doing what I have in my mind.
Thanks!!

Re: count (all) / Count (unique) = result -> chart

DalJeanis — Mon, 20 Feb 2017 20:38:35 GMT

eventstats is totally unnecessary in this one. Delete that line and on the next line, change last(FullCount) to count.

Re: count (all) / Count (unique) = result -> chart

niketn — Tue, 21 Feb 2017 15:21:02 GMT

@dexxter275... That is why I follow @martin_mueller 🙂