topic Re: Where command not filtering the data in Splunk Search

Where command not filtering the data

appu — Wed, 23 Sep 2020 17:28:55 GMT

Hi all ..I need a help on a query ...

My query looks like this

Index=* ......... | Eventstats count as total_count | where log!=error | eventstats count as success_count ....

Issue is that , where command doesn't filter properly .. and I get both total and success count as same even though there is log=error events(it doesn’t remove log=error events) ... I tried using search command ..tried match statement ... Still everything gives both total and success count as same . I’m finding this weird.

Is there anyway I can try to filter ?

Re: Where command not filtering the data

vikramyadav — Wed, 23 Sep 2020 17:35:50 GMT

Can you try

index=* ......... | search log!=error | eventstats count as total_count | eventstats count as success_count ....

-----------------------------------------------------------
If this helps, your like will be appreciated. 😀

Re: Where command not filtering the data

appu — Wed, 23 Sep 2020 20:08:52 GMT

Hi Vikram,

Thanks for the reply. Still total count will be same as success count. Isn't it ? (I tried, both values are same)

My intention is, I wanted to run a raw query, consider it as total count and then look for log!=error which is success count and find the failure count by subtracting success count from total count as i want to print all three values.

But, in my case that's not happening.

Re: Where command not filtering the data

bowesmana — Wed, 23 Sep 2020 22:01:39 GMT

@appu

The where clause is used differently to the search clause, for example, consider these

1. where log!=error 2. where log!="error" 3. where !match(log, "error") 4. search log!=error 5. search log!=ERROR 6. search log=*error*

1. Will compare whether the field 'log' and the field 'error' are not an exact match

2. Will compare whether the field 'log' does not match exactly the string "error"

3. Will compare whether the field 'log' contains the lower case string 'error"

4-6. Using search is a simple string comparison and is case insensitive and wildcards are just * and it is always about comparing the log field against a string value, whether quoted or not.

So, if your log field CONTAINS the word "error" , then you should use example 3.

However, eventstats can be expensive as it is not aggregating data, so you can actually evaluate this in a better way

index=* ......... | stats count as total_count sum(eval(if(match(log,"error"),0,1))) as success_count

If you need to retain all the data, then you can use eventstats rather than stats, but using the sum(eval...) will give you both counts with a single operation.

Re: Where command not filtering the data

appu — Wed, 23 Sep 2020 22:38:14 GMT

Hi @bowesmana :

Thanks for the reply and also for the interesting explanations. I never knew the behaviour of where command with and without quotes. thanks for that 🙂

Below is My actual query. I tried all the possibilities you mentioned except for point 3. But still not able to get the results. I'm not able to figure out what exactly is wrong.

I'm using eventstats so that I can pass the total count value to calculate percentage.

index=* ...
| eventstats dc(UserId) as totalUserId_Count dc(Users) as totalUsers_Count
| where log!="error"
| eventstats dc(UserId) as successUserId_Count dc(Users) as successUsers_Count
| eval failUserId_count=(totalUserId_Count-successUserId_Count), failUsers_Count=(totalUsers_Count-successUsers_Count), success_percent = round ( ((successUserId_Count) / totalUserId_Count) * 100, 2)."%",
| dedup successUserId_Count failUserId_count successUsers_Count failUsers_Count success_percent
| table successUserId_Count failUserId_count successUsers_Count failUsers_Count success_percent

Re: Where command not filtering the data

bowesmana — Thu, 24 Sep 2020 04:53:07 GMT

@appu

You are over complicating things. All you need is a single stats statement, but your problem is that your field 'log' is probably not containing what you want.

index=* ... | stats dc(UserId) as totalUserId_Count dc(Users) as totalUsers_Count dc(eval(if(log="error", null(), UserId)))) as successUserId_Count dc(eval(if(log="error", null(), Users))) successUsers_Count | eval failUserId_count=(totalUserId_Count-successUserId_Count), failUsers_Count=(totalUsers_Count-successUsers_Count), success_percent = round ( ((successUserId_Count) / totalUserId_Count) * 100, 2)."%", | table successUserId_Count failUserId_count successUsers_Count failUsers_Count success_percent

You need to check exactly what your log field contains. Can you post an example of the log field

Re: Where command not filtering the data

appu — Thu, 24 Sep 2020 07:27:53 GMT

HI @bowesmana :

Below is the log (redacted remaining lines):

timeStamp="2020/09/15 03:16:38.670 UTC" -- log="ERROR" -- threadId="" -- className="" -- handler=""

The above is the log values I've.

I tried using rex to grep the log value, to make sure the log doesn't have any special characters but result was still the same.

Re: Where command not filtering the data

bowesmana — Thu, 24 Sep 2020 08:40:56 GMT

OK, so ERROR is in caps, which means

index=* ... | stats dc(UserId) as totalUserId_Count dc(Users) as totalUsers_Count dc(eval(if(log="ERROR", null(), UserId)))) as successUserId_Count dc(eval(if(log="ERROR", null(), Users))) successUsers_Count | eval failUserId_count=(totalUserId_Count-successUserId_Count), failUsers_Count=(totalUsers_Count-successUsers_Count), success_percent = round ( ((successUserId_Count) / totalUserId_Count) * 100, 2)."%", | table successUserId_Count failUserId_count successUsers_Count failUsers_Count success_percent

you can see the if(log="ERROR"... there, but you could also do if(match(log,"ERROR"...

This should give you the results

Re: Where command not filtering the data

isoutamo — Thu, 24 Sep 2020 08:43:27 GMT

When you are writing:
where log != error
you are looking events which content of field log is not equal tuo content of field error.
With search you are looking where content of field log is not value "error".

Re: Where command not filtering the data

appu — Thu, 24 Sep 2020 08:59:28 GMT

Hi @isoutamo :

Yes, Thank you. 🙂

Re: Where command not filtering the data

appu — Thu, 24 Sep 2020 09:13:52 GMT

Hi @bowesmana :

I tried the above method with little tweak. Instead of calculating the success and then calculating failure, (sadly, it was not giving any results) so I went ahead and checked for failures and from that I calculated success count.

I interchanged null() and Users/UserId. And instead of success I made it as failUsers_Count and failUserId_Count. Which is now giving the required result. Now My only worry is that why it was not giving the result the other way. But, thank you so much for the guidance.

dc(eval(if(log="ERROR", UserId, null())))) as failUserId_Count dc(eval(if(log="ERROR", Users, null()))) failUsers_Count