topic Re: How do I find count of duplicates along with total events count? in Splunk Search

How do I find count of duplicates along with total events count?

relango — Fri, 12 Jan 2018 00:04:03 GMT

I have payload field in my events with duplicate values like

val1
val1
val2
val2
val3

How to do I search for the count of duplicate events (in above e.g 2 with val1,val2) vs count of total events (5)? I am able to find duplicates using search stats count by payload | where count > 1 but can't able to figure out beyond that...

Re: How do I find count of duplicates along with total events count?

mayurr98 — Fri, 12 Jan 2018 08:29:10 GMT

hey

Try this run anywhere search

| makeresults | eval raw="val1" | append [| makeresults | eval raw="val1" ] | append [| makeresults | eval raw="val2"] | append [| makeresults | eval raw="val2" ] | append [| makeresults | eval raw="val3" ] | stats count(raw) as count by raw  | eventstats sum(count) as total_count | where count > 1

Ideally in your environment you should write

basesearch | stats count(payload) as count by payload| eventstats sum(count) as total_count | where count > 1

Let me know if this works!

Re: How do I find count of duplicates along with total events count?

relango — Fri, 12 Jan 2018 15:03:29 GMT

Thanks that works!. But I actually want to count the duplicates and also bucket it by time. Here what I tried:

basesearch | bin _time span=1d| stats count by payload,_time | eventstats sum(count) as total | where count > 1 | eventstats count as dup_count | table count, dup_count, total

This works, but one problem. The results are repeated for each value of payload and _time. How can I get just one result for each day.

Basically, I am trying to find the percentage of duplicate payloads per day by computing count of duplicate payloads per day and total number of payloads

Re: How do I find count of duplicates along with total events count?

mayurr98 — Fri, 12 Jan 2018 15:08:59 GMT

hey I do not understand what do you want to achieve. You can just provide me sample input like you did for the above question and also provide what output you want.

Re: How do I find count of duplicates along with total events count?

relango — Tue, 29 Sep 2020 17:38:28 GMT

Using above example: I added one more duplicate "val4" to above example and tried this query:

| makeresults | eval raw="val1" | append [| makeresults | eval raw="val1" ] | append [| makeresults | eval raw="val2"] | append [| makeresults | eval raw="val2" ] | append [| makeresults | eval raw="val3" ]| append [| makeresults | eval raw="val4" ] | append [| makeresults | eval raw="val4" ]   | stats count(raw) as count by raw  | eventstats sum(count) as total_count | where count > 1 |eventstats count as dup_count | table dup_count,total_count

It correctly returns dup_count as 3 and total_count 7 , but it is repeated three times for each duplicated value (val1,val2,val3) , but I just need one.

Piping it to | dedup total_count works for this eg: but not for the actual use case in which I am also trying to bucket stats by 1 day.

Re: How do I find count of duplicates along with total events count?

mayurr98 — Fri, 12 Jan 2018 15:37:41 GMT

you are misunderstanding the concept | dedup total_count is just removing duplicates in a column as you have three time 7 now after this it is showing only one time as it removed duplicates whereas |eventstats count as dup_count works at each event/row which means total statistics if you see you have three rows that is why it is returning 3
I hope you understand what I am trying to say
If you are interested to find total payloads and duplicated payloads then try this

| makeresults | eval raw="val1" | append [| makeresults | eval raw="val1" ] | append [| makeresults | eval raw="val2"] | append [| makeresults | eval raw="val2" ] | append [| makeresults | eval raw="val3" ]| append [| makeresults | eval raw="val4" ] | append [| makeresults | eval raw="val4" ]   | stats count(raw) as count by raw   | eventstats sum(count) as total_count | where count > 1 | stats count as duplicated_payload_count first(total_count) as total_payload_count

as there are 7 entries so total_payload_count is 7 and 3 duplicated entries so duplicated_payload_count is three.

Re: How do I find count of duplicates along with total events count?

mayurr98 — Fri, 12 Jan 2018 17:29:01 GMT

hey did you find above query helpful?

Re: How do I find count of duplicates along with total events count?

wrangler2x — Fri, 12 Jan 2018 17:58:55 GMT

You can simplify building raw and get the same results, to wit:

| makeresults | eval raw="val1 val1 val2 val2 val3" | makemv delim=" " raw | mvexpand raw

Re: How do I find count of duplicates along with total events count?

wrangler2x — Wed, 17 Jan 2018 23:08:24 GMT

I was just looking at another Splunk Answer which was asking something slightly different, and what that person was looking for was to get a running total to the side of the count. I tried to answer that question but it had been closed, with a pointer to this one as being the same question (doesn't look the same to me, but heh...). Here is how you do that:

| makeresults | eval raw="val1 val1 val2 val2 val3 val4 val4 val4" | makemv delim=" " raw | mvexpand raw
| stats count by raw
| where count > 1
| sort - count
| accum count as totalCount

Of course the line beginning with makeresults would be replaced by your base search, as well ast the stats count by raw. You get the counts 3, 2, 2 in this example, and the totalCounts 3, 5, 7.

Re: How do I find count of duplicates along with total events count?

wrangler2x — Wed, 17 Jan 2018 23:09:27 GMT

The other Splunk Answer is here: https://answers.splunk.com/answers/609288/how-to-get-count-of-events-as-well-as-count-of-sta.html#answer-form