Splunk Search

Timechart with Where Clause

Motivator

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

Tags (2)
1 Solution

Ultra Champion

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

View solution in original post

Explorer

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

Path Finder

This is exactly what I needed, thanks

0 Karma

Path Finder

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

 

0 Karma

Splunk Employee
Splunk Employee

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
0 Karma

Motivator

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.

0 Karma

Ultra Champion

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

View solution in original post

Explorer

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!

0 Karma

Path Finder

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.

0 Karma

Ultra Champion

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 !

0 Karma

Contributor

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.

0 Karma

Motivator

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

0 Karma

Path Finder

Try using all uppercase WHERE

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!