Subsearch for negation

[satishsdange]

I am trying to write a subsearch which will negate few days/hours from results considering event count. But below search is not giving me desired result. Could someone please point me the right search?

index=_internal sourcetype="splunkd" NOT [search index=_internal sourcetype="splunkd" | timechart count | search count > 10] | timechart count

[DalJeanis · Accepted solution]

I have no idea why you are trying to do what you are trying to do, but it really won't work that way.

First, your subsearch is scanning the _internal index for that sourcetype, then creating a table that contains only _time and count. The field _time that is returned by timechart will have been binned into buckets of whatever the default duration is for timechart, based on the time period that you are running the search over, so very few transactions would possibly match the exact _time values returned. The field count does not even exist on the _internal index, so nothing ever will be returned.

Then even if that worked, you are then asking splunk to go back and add it all up again... getting no more data than the first time, and no different answer.

Now, understand, anything CAN be done. We could code a way to take the results of a subsearch and collect the data off the _internal index (preferably using tstats rather than a full scan) and use that to populate the search with time inclusion criteria. For some reason, tstats refuses to work correctly with complex _time tests, so you'd feed it back to your original search like this...

index=_internal sourcetype="splunkd" 
    [
    | tstats count as spancount where index=_internal AND sourcetype="splunkd" BY _time span=5m 
    | eval spanstart=_time, spanend=_time+300
    | eval highlow=if(spancount>10000,"high","low")
    | streamstats current=f last(highlow) as prevhighlow
    | eval spanchange=if(highlow=coalesce(prevhighlow,"none"),0,1)
    | streamstats sum(spanchange) as spannumber
    | stats sum(spancount) as spancount count as periods min(spanstart) as spanstart, max(spanend) as spanend, min(highlow) as highlow by spannumber
    | where highlow="low"
    | table spanstart spanend
    | format
    | rex mode=sed field=search "s/spanend=\"/_time</g"
    | rex mode=sed field=search "s/spanstart=\"/_time>=/g"
    | rex mode=sed field=search "s/\"//g"
    | table search
    ]
| bin _time span=5m 
| stats count by _time

All that mumbo-jumbo between | table spanstart spanend and | table search is turning our paired _time values into a search format that reads like...

( ( _time<1494608100 AND _time>=1494607800 ) OR ( _time<1494609000 AND _time>=1494608400 )  ... )

Note that in my highlow test I I used 10000 rather than 10, due to the traffic on my index.

...and after all that work, it gets the exact same results as somesoni2's

 index=_internal sourcetype="splunkd" | timechart span=5m count | WHERE count <= 10000

[somesoni2]

Why not just run this

index=_internal sourcetype="splunkd" | timechart count | WHERE count <= 10

[satishsdange]

Thanks. But i want to use subsearch only. Any suggestion?

[ckunath]

Doing it the way somesoni suggested is much better - why do you insist on using subsearch if I may ask?

[somesoni2]

Since the timechart will use some default span based on time range (as you've not specified anywhere), using subsearch will be a complex solution. If your requirement is met using just a single-simple search, any specific reason you want to go with subsearch (you'll be running the same search twice)?