How to achieve proper search for timechart with where clause?

[hartfoml]

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

[tedwroks]

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

[splunklearner12]

This is exactly what I needed, thanks

[thinhdinh]

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

[sowings]

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

[hartfoml]

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.

[Damien_Dallimor · Accepted solution]

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

[wjblazek]

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!

[essklau]

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.

[Damien_Dallimor]

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 !

[DT]

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

[cvajs]

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.

[hartfoml]

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

[rajanala]

Try using all uppercase WHERE

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