After calculations in a search, why am I unable to display results using the table command?

[chrisboy68]

Hi,

This search below is working great....

index=logs AND (sourcetype=eMetrics)  |  JOIN type=outer OrderNumber [ search index=logs AND  LogLevel=ERROR AND ErrorType="BAD_ORDER"  ]  
| stats count(eval(Name!="SUCCESS")) as bad_orders, count(eval(Name=="SUCCESS")) as good_orders  | eval percent_bad=100*bad_orders/good_orders | where percent_bad > 30 

However, adding the table command does not work.

index=logs AND (sourcetype=eMetrics)  |  JOIN type=outer OrderNumber [ search index=logs AND  LogLevel=ERROR AND ErrorType="BAD_ORDER"  ]  
| stats count(eval(Name!="SUCCESS")) as bad_orders, count(eval(Name=="SUCCESS")) as good_orders  | eval percent_bad=100*bad_orders/good_orders | where percent_bad > 30 | table OrderNumber, ErrorMessage

Any ideas? I'm trying to use this as an alert and it will display a table of bad_orders with a few Event fields.

Thank you!

Chris

[chimell]

Hi
Try with

..................|fields OrderNumber  ErrorMessage

[somesoni2]

Your stats command is limiting the fields to only the bad_orders and good_orders. The field that you want to show are not available there.

If you can add some sample events and/or describe fields available in both your sources, we can probably suggest you better options (then join).

You can give this a try (just for fun)

index=logs (sourcetype=eMetrics) OR (LogLevel=ERROR AND ErrorType="BAD_ORDER" ) | stats values(Name) as Name, values(ErrorMessage) as ErrorMessage by OrderNumber | eventstats count(eval(Name!="SUCCESS")) as bad_orders, count(eval(Name=="SUCCESS")) as good_orders  | eval percent_bad=100*bad_orders/good_orders | where percent_bad > 30 | table OrderNumber, ErrorMessage

[chimell]

hi
in your query i can not see ErrorMessage field where it is?

[somesoni2]

Its the second field in the stats.

[chrisboy68]

Thanks for your response. Ok, so I got rid of the Join and saw you used Eventstats so I gave the below a try.

index=logs  AND (sourcetype=Metrics  OR  (LogLevel=ERROR AND ErrorType="BAD_ORDER" ))  
| eventstats count(eval(Name!="SUCCESS")) as bad_orders, count(eval(Name=="SUCCESS")) as good_orders  | eval percent_bad=100*bad_orders/good_orders |  where percent_bad > 20 |search ErrorType="BAD_ORDER", OrderNumber != NULL | table percent_bad, _time ,OrderNumber, ErrorMessage, ErrorMessage2

Seems like its working, I'll need to analyse more to see if the calculations are correct. If there is a better way, please share!

Thanks for your help!

Chris