topic Re: Adding dedup _raw before timechart returns 0 results in Splunk Search

Adding dedup _raw before timechart returns 0 results

tommy_o — Tue, 07 Jan 2014 17:50:46 GMT

I apologize if this is asked already but I search to no avail.

When writing a Splunk query that will eventually be used for summary indexing using sitimechart, I have this query:

index=app sourcetype=<removed> host=<removed> earliest=-10d
    | eval Success_Count=if(scs=="True",1,0)
    | eval Failure_Count=if(scs=="False",0,1)
    | timechart span=1d sum(Success_Count) as SuccessCount sum(Failure_Count) as FailureCount count as TotalCount by host

Results are as expected. However, some data was accidentally indexed twice, so I need to remove duplicates. If I'm doing a regular search, I just use | dedup _raw to remove the identical events. However, if I run the following query, I get zero results returned (no matter where I put | dedup _raw😞

index=app sourcetype=<removed> host=<removed> earliest=-10d
    | dedup _raw
    | eval Success_Count=if(scs=="True",1,0)
    | eval Failure_Count=if(scs=="False",0,1)
    | timechart span=1d sum(Success_Count) as SuccessCount count(Failure_Count) as FailureCount count as TotalCount by host

What am I doing wrong? I'm using Splunk 4.3.2.

Re: Adding dedup _raw before timechart returns 0 results

tommy_o — Tue, 07 Jan 2014 17:55:38 GMT

There's a type-o on the eval Failure_Count line, but the reCaptcha blocked me from editing 😞 Edit: there should have been sum(), sum(), count but again, captcha is keeping me from fixing that.

Re: Adding dedup _raw before timechart returns 0 results

somesoni2 — Tue, 07 Jan 2014 18:44:04 GMT

When you said the data was duplicated, duplicate events have same timestamp or different?

Re: Adding dedup _raw before timechart returns 0 results

somesoni2 — Tue, 07 Jan 2014 18:52:23 GMT

Try following

index=app sourcetype=<removed> host=<removed> earliest=-10d
    | fields _time, scs,host
    | dedup _time, scs,host
    | timechart span=1d count(eval(scs="True")) as SuccessCount count(eval(scs="False")) as FailureCount count as TotalCount by host

Re: Adding dedup _raw before timechart returns 0 results

tommy_o — Tue, 07 Jan 2014 19:21:57 GMT

They have the same timestamp

Re: Adding dedup _raw before timechart returns 0 results

lukejadamec — Tue, 07 Jan 2014 19:36:17 GMT

Careful with that. Depending on the volume and timestamp extraction you can have many legit non-duplicate events with the same timestamp that will hidded by deduping _time.

Re: Adding dedup _raw before timechart returns 0 results

somesoni2 — Tue, 07 Jan 2014 19:43:24 GMT

True, best approach would to be to include all the fields which make an event unique in the "fields" and "dedup" clause, so that all those legit events are not getting filtered out.

Re: Adding dedup _raw before timechart returns 0 results

tommy_o — Tue, 07 Jan 2014 19:48:43 GMT

I was under the impression that I couldn't use eval() on the same line as sitimechart (which I will be switching over to once I've ironed out this duplicate problem). Is that not correct? This is essentially what my query looked like originally.

Re: Adding dedup _raw before timechart returns 0 results

somesoni2 — Tue, 07 Jan 2014 20:33:37 GMT

I am able to use eval() in the sameline as sitimechart command (and don't see any restriction about same in the documentation).

Re: Adding dedup _raw before timechart returns 0 results

tommy_o — Tue, 07 Jan 2014 21:05:56 GMT

Okay, thank you for the confirmation. That was written in an internal corporate document and I wasn't getting any summary data in my index -- I was thinking my use of eval on the same line as sitimechart may have been causing that problem (but glad to hear that it shouldnt be). Thanks again.

Re: Adding dedup _raw before timechart returns 0 results

somesoni2 — Tue, 07 Jan 2014 21:28:19 GMT

Is your duplicate records issue resolved?