Where condition not working for a multiple value field

[neerajs_81]

Hello All,  
I have a search that uses stats command and displays the results as follows.  Note:  I have stripped out some columns.  

index=index1 sourceType=xxxx
| eventstats count(action) as Per_User_failures by user
| stats latest(_time) as _time, values(host), values(src_ip), dc(src_ip) as srcIpCount, values(user), values(Failure_Reason), dc(user) as userCount, values(Per_User_failures) as Per_User_failures  by Workstation_Name

neerajs_81_0-1636703489315.png

Now, if i further add  | where Per_User_failures > 10  condition, the search shows "No Results Found". 

index=index1 sourceType=xxxx
| eventstats count(action) as Per_User_failures by user
| stats latest(_time) as _time, values(host), values(src_ip), dc(src_ip) as srcIpCount, values(user), values(Failure_Reason), dc(user) as userCount, values(Per_User_failures) as Per_User_failures  by Workstation_Name
| where Per_User_failures >10

This is incorrect  as you can see there are some values where Per_user_Failures is greater than 10 such as 11,12,13, 1037 etc.  
How can i make the where clause check any of the values under the "Per_user_failures" column. 

[kamlesh_vaghela]

@neerajs_81 

Can you please replace this with your where condition?

| mvexpand Per_User_failures | where Per_User_failures>10 | stats values(*) as * by Workstation_Name

If it is works, you can redefine your search and make it possible with only one stats if possible.

Thanks
KV
▄︻̷̿┻̿═━一   😉

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated. 

[neerajs_81]

Hi Kamlesh, i tried  using | mvexpand Per_User_failures | where Per_User_failures>10 , 
it appears to be working obviously but the  results in the "Per_User_failures" column no longer shows the count of failures per user level.  It is now showing only a single value ( which i think is the total of all failures) .

I want the search results to continue displaying count of failures of every user in the "Per_User_failures" column as it was before which is why i am using evenstats to show me the data per user.

This is what i mean :
Before: ( as per my screenshot earlier) .  It shows failures of every user : 

User	userCount	Per_User_Failures
John Alice	2	5 10
Alex John Jamie Alicia	4	13 1037 12 1

After adding  | mvexpand Per_User_failures | where Per_User_failures>10

User	userCount	Per_User_Failures
John Alice	2	12
Alex John Jamie Alicia	4	868

[kamlesh_vaghela · Accepted solution]

@neerajs_81 

I hope you tried same I shared.

I'm sharing my sample search with you. I hope this will help you.

| makeresults 
| eval User="John,Alice|Alex,John,Jamie,Alicia",userCount="2|4",Per_User_Failures="5,10|13,1037,12,1"
    ,User=split(User,"|"),userCount=split(userCount,"|"),Per_User_Failures=split(Per_User_Failures,"|") 
| eval t=mvzip(mvzip(User,userCount,"|"),Per_User_Failures,"|") 
| mvexpand t 
| eval User=mvindex(split(t,"|"),0),userCount=mvindex(split(t,"|"),1),Per_User_Failures=mvindex(split(t,"|"),2) 
| eval User=split(User,","),Per_User_Failures=split(Per_User_Failures,",") 
| eval Workstation_Name=1 | accum Workstation_Name
| rename comment as "Upto now is for sample data only" 
| table User userCount Per_User_Failures Workstation_Name
| mvexpand Per_User_Failures 
| where Per_User_Failures>10
| stats values(*) as * by Workstation_Name

KV

[neerajs_81]

Thank you