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

[dexxter275]

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

[martin_mueller · Accepted solution]

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.

[dexxter275]

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

[niketn]

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

[niketn]

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)

[DalJeanis]

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

[dexxter275]

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.

[niketn]

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

[dexxter275]

Wow. That works perfectly. Thanks so much 🙂