topic Re: Timechart with Where Clause in Splunk Search

How to achieve proper search for timechart with where clause?

hartfoml — Wed, 21 Sep 2022 20:21:59 GMT

Here is my search:

source="WinEventLog:Security" EventCode=540 | timechart span=1h count by User

This gives me the count by hour that users are logging in but I only want the users that are exceeding a threshold like 200 times an hour, so I do this.

source="WinEventLog:Security" EventCode=540 | timechart span=1h count by User Where count>200

But this does not work

Re: Timechart with Where Clause

Damien_Dallimor — Thu, 05 Apr 2012 01:53:03 GMT

Because the count field is not in the timechart results.The count value is part of the various User fields.

Something like this might work better for you :

source="WinEventLog:Security" EventCode=540 | bucket span=1h _time | stats count by _time User  | where count > 200 | xyseries _time,User,count

Re: Timechart with Where Clause

hartfoml — Thu, 05 Apr 2012 12:45:21 GMT

Right I tried this and did get the results but not the format for charting

My intent is to have a chart with one line per user showing the number of EventCode 540/hour for over time. With stats I don't know how to make the chart I need. I thought to use "limit" but then I only get the top # of users and not the user exceeding the threshold.

How is the count field not in the timechart results? I get a result for count in the output. If I knew the field for count I could do a "where" on the field. Can I rename count like this?

| timechart span=1h count AS Number by User Where Number > 200

Re: Timechart with Where Clause

cvajs — Mon, 28 Sep 2020 11:37:46 GMT

nope, i dont think the where clause can act on results of the chart, only fields within the search. renaming using AS only changes the name in the chart. this search fails to display the data the way you want.
index=cisco_firewall error_code=106023 | chart count(error_code) AS number by src where number>50

however, i am sure there is a way to get what you need.

Re: Timechart with Where Clause

hartfoml — Thu, 05 Apr 2012 14:18:52 GMT

I used this code for now since I could not find how to use the where clause with TimeChart.

| timechart limit=5 span=1h count by User useother=f

I tried this code:

| timechart span=1h count(EventCode) by User useother=f where count > 200

But this didn't work. I tried this code:

| timechart span=1h (EVAL count(EventCode)) by User useother=f where count > 200

But this didn't work either.

Re: Timechart with Where Clause

sowings — Thu, 05 Apr 2012 15:16:41 GMT

Find the list of users over the threshold with a subsearch, then search for only those users, and timechart it.

Example only, not definitive:

source="WinEventLog:Security" EventCode=540
  [ search source="WinEventLog:Security" EventCode=540 | stats count by User | where count > 200 | fields + User ]
  | timechart count by User

Re: Timechart with Where Clause

Damien_Dallimor — Fri, 06 Apr 2012 07:18:07 GMT

Add an "xyseries" command to format the stats output for the charting you require.
I have edited my original answer showing how to do this.
Enjoy !

Re: Timechart with Where Clause

essklau — Tue, 22 Jul 2014 14:41:55 GMT

I love this approach. However, there seems to be a caveat. If I use "where count=0", nothing returns, even though there are "cells" in my xyseries without a count (count=0). If tried to insert ifnull logic to the count, but I didn't pull it off.

Re: Timechart with Where Clause

wjblazek — Mon, 28 Sep 2020 17:46:10 GMT

I had this same problem.
I also ended up using " | bucket ... | stats ... | where ... " to get what I needed.

It seems like the timechart documentation says it, the original problem above, should work:
http://docs.splunk.com/Documentation/Splunk/6.0.2/SearchReference/Timechart

Example 4: Using the where clause with the count function measures the total number of events over the period. This yields results similar to using the sum function.
The following two searches returns the sources series with a total count of events greater than 100. All other series values will be labeled as "other".
index=_internal | timechart span=1h count by source WHERE count > 100
index=_internal | timechart span=1h count by source WHERE sum > 100

Am I reading this right?

Because the count field is not in the timechart results.The count value is part of the various User fields.
So is this documentation wrong?
If so, can it be corrected?

Thanks for the info!

Re: Timechart with Where Clause

tedwroks — Wed, 19 Oct 2016 08:52:41 GMT

I'm just taking this from memory (I can't run a test whilst I write this), but I think it's just:

source="WinEventLog:Security" EventCode=540 | timechart span=1h count by User where max > 200

Regards

Re: Timechart with Where Clause

splunklearner12 — Tue, 09 Jul 2019 08:17:59 GMT

This is exactly what I needed, thanks

Re: Timechart with Where Clause

thinhdinh — Fri, 03 Jul 2020 10:18:12 GMT

Anyone here got the solution worked? I have tried with bucket -> stats -> where but it is not working for me. Here is my query:

....| dedup customerID | bucket span=2h _time | stats count as "Actived customer" by Event where count > 0 | xyseries _time, customerID, count

Re: Timechart with Where Clause

rajanala — Tue, 29 Sep 2020 22:01:07 GMT

Try using all uppercase WHERE

for example :-
timechart count by user WHERE user>200

Re: Timechart with Where Clause

DT — Wed, 21 Sep 2022 20:16:05 GMT

i used as per suggestion but it didn't work for '0' ? any other options Please