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
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.
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
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.
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 |
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