stats count issue

[krusovice]

Hello,

I hit a problem in the query below. I believed I'm not allow to form the stat count 2 times in the query. The result of this query result 0 which is not the right answer expected. How can I achieve to get the % failed from the result of the regex?

index=* 
 | rex field=_raw "Request Name\=(?<request_name>[\w+]+)" 
 | rex field=_raw "Request type.*Id\=(?<>[toy\w+]+).*Type\=Toy"
 | rex field=_raw "Request type.*Id\=(?<>[play\w+]+).*Type\=Play"
 | rex field=_raw "Failed.*FailId\=(?<fail_count>[\w+]+)"
 | stats count(request_name) as total, count(faild_count) as failed
 | eval percentage=round(((failed/total)*100),2)."%"
 | stats count(request_name) by "Total Request", count(toy) as "Toy Count", count(play) as "Play Count", count(failed_count) as "Failed Count", values(percentage) as "% Failed" by date_hour

My Expected table should look like this:

date_hour      Total Request      Toy Count     Play Count       Failed Count      % Failed
  1                 50               30            10               10                20%

[p_gurav · Accepted solution]

Can you try:

index=* 
  | rex field=_raw "Request Name\=(?<request_name>[\w+]+)" 
  | rex field=_raw "Request type.*Id\=(?<>[toy\w+]+).*Type\=Toy"
  | rex field=_raw "Request type.*Id\=(?<>[play\w+]+).*Type\=Play"
  | rex field=_raw "Failed.*FailId\=(?<fail_count>[\w+]+)"
  | stats count(request_name) as "Total Request", count(toy) as "Toy Count", count(play) as "Play Count", count(failed_count) as "Failed Count" by date_hour
  | eval percentage=round((('Failed Count'/'Total Request')*100),2)."%"

[krusovice]

It's works. Learning something new today.
Thanks 🙂

[niketn]

@krusovice , if possible you should also have single Regular Expression to fetch request_name, toy, play and failed_count from raw data, instead of performing rex extraction four times. Refer to .conf 2017 session on Beyond Regular Regular Expressions by @cpetterborg

Eventually you should convert regular expression based extractions as Fields Knowledge Object for easy maintenance and reuse.