Splunk Search

How to achieve proper search for timechart with where clause?

hartfoml
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

Labels (1)
Tags (2)
1 Solution

Damien_Dallimor
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

tedwroks
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

splunklearner12
Path Finder

This is exactly what I needed, thanks

0 Karma

thinhdinh
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

sowings
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

hartfoml
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

Damien_Dallimor
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

wjblazek
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

essklau
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

Damien_Dallimor
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

DT
New Member

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

0 Karma

cvajs
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

hartfoml
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

rajanala
Path Finder

Try using all uppercase WHERE

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...