Splunk Search

multiple eval and count in the pipe

milande
Path Finder

Hi,

I have a table with header line like:

stepName stepStatus time

additional_sub_4 PASS today

additional_sub_3 FAIL today
wrong_sub_4 FAIL today
wrong_sub_3 PASS today
...
additional_sub_4 FAIL yesterday
additional_sub_3 FAIL yesterday
wrong_sub_4 PASS yesterday
wrong_sub_3 PASS yesterday
...

I need to draw a chart with two lines Add_Status, Wrg_Status which would have values of PASS or FAIL over the time. So, Add_Status would have value PASS is at least one stepName(additional_sub_4 or additional_sub_3) is PASS, otherwise FAIL. The same holds for the chart of values Wrg_Status over the time. My search string looks like:

... | eval testLogic=case(
LIKE(stepName,"additional_sub_%") AND stepStatus="PASS", "ADD_PASS",
LIKE(stepName,"wrong_sub_%") AND stepStatus="PASS", "WRG_PASS") |
eval Add_Count = count(testLogic="ADD_PASS") , Wrg_Count = count(testLogic="WRG_PASS") |
eval Add_Status=if(Add_Count>0,"PASS","FAIL"), Wrg_Status=if(Add_Count>0,"PASS","FAIL") |
chart Add_Status, Wrg_Status by _time

to describe step by step what I am doing:
... | eval testLogic=case(
LIKE(stepName,"additional_sub_%") AND stepStatus="PASS", "ADD_PASS",
LIKE(stepName,"wrong_sub_%") AND stepStatus="PASS", "WRG_PASS")

(here I get into field testLogic all events which are PASS and belongs to one of two stepName)

(here I count number of "PASS"es, max is 2 min is 0 )

(here I check if count is more then zero or not)

(here I expect to get over the time chart if at least one stepName additional_sub_? or wrong_sub_? was having stepStatus PASS or not)

But it produce a lot of errors starting with:
Error in 'eval' command: The operator at ', Wrg_Count = count(testLogic="WRG_PASS")' is invalid.
so I am wondering what I am all doing wrong ?

regards,
Milan

Tags (2)
0 Karma
1 Solution

aweitzman
Motivator

Simply, count is not an eval function. eval is for creating or modifying fields in each record. If you want to actually count things, you need to use something like stats.

So something like:

... 
| eval testLogic=...your logic above...
| stats count by testLogic time
| eval Add_Status=if(testLogic="ADD_PASS",if(count > 0,"PASS","FAIL"),"")
| eval Wrg_Status=if(testLogic="WRG_PASS",if(count > 0,"PASS","FAIL"),"")
| chart first(Add_Status) first(Wrg_Status) by time

You have a time field already, so you should use that instead of whatever time Splunk is assigning to your event. Also, you can't use chart with just values, you have to apply a function to the grouping that you generate with the by clause; you can use first since your grouping should guarantee a unique value there.

View solution in original post

acharlieh
Influencer

The issue at hand I think is an understanding of the differences between eval and chart. eval lets you assign a value to a new field on each result (row / record) based on values of other fields in each result and functions applied to the same. Because eval works on a row by row basis, attempting to count the number of times a field is a certain value across all records isn't possible with the eval function. Additionally, eval only sets the value of a single field at a time. If you want to set multiple values you need multiple eval statements

Stats (and other functions) on the other hand lets you apply statistical functions across all records in your record set, including but not limited to count(eval(testLogic=="ADD_PASS")) as Add_Count for example. You can calculate these statistics across the record set as a whole (the default) or you can add a by clause to group over a set of other fields with the same corresponding value set for those fields allowing you to answer questions that require such division. chart is the same as stats but it let's you group by only two fields instead of arbitrarily many. The reason for this is to help you setup a visual chart with multiple series of statistics over a field containing the x-axis values. As bucketed time windows is often the preferred x-axis when it comes to data in Splunk, the timechart command is the chart command where the x-axis is simply the _time field, divided into buckets (every day, hour, minute, etc).

Now with the basics out of the way let's look at your data. For this, I'm assuming that everything before the first underscore is a parent job identifier and that time is discrete strings as is in your question. So if we do base search to retrieve data | rex field=stepName "^(?[^_]+)_" | stats count(eval(stepStatus=="PASS")) as nPass by time,parentId | eval nPass=if(nPass>0,1,0) | chart max(nPass) by parentId over time this begins to get us an approximation of what you are looking for. If time is actually _time and a Unix time stamp value instead of a discrete string, the above will change as you'll need to solve bucketing issues (for example do I have 1 or multiple runs of my overall job in my bucket,if multiple pass is that 1 or potentially 2?). also think should a partial success be counted differently or not. But I leave that as an exercise to you dear asker, and hope this early morning explanation helps

aweitzman
Motivator

Simply, count is not an eval function. eval is for creating or modifying fields in each record. If you want to actually count things, you need to use something like stats.

So something like:

... 
| eval testLogic=...your logic above...
| stats count by testLogic time
| eval Add_Status=if(testLogic="ADD_PASS",if(count > 0,"PASS","FAIL"),"")
| eval Wrg_Status=if(testLogic="WRG_PASS",if(count > 0,"PASS","FAIL"),"")
| chart first(Add_Status) first(Wrg_Status) by time

You have a time field already, so you should use that instead of whatever time Splunk is assigning to your event. Also, you can't use chart with just values, you have to apply a function to the grouping that you generate with the by clause; you can use first since your grouping should guarantee a unique value there.

View solution in original post

milande
Path Finder

Hi aweitzman,
your suggestion seems to work up to the last line:
| chart first(Add_Status) first(Wrg_Status) by time
as the Add_Status and Wrg_Status are strings chart seems to have problem in drawing "PASS/FAIL" points on Y axis. How to make chart draw "PASS/FAIL" values ?

0 Karma

aweitzman
Motivator

In this case, chart will get you a useful table, but because your values are not numeric, you cannot turn your result into a graph.

0 Karma

milande
Path Finder

so having diagrams with some other categories (beside the numbers) is not possible at all in SPLUNK ?

0 Karma

milande
Path Finder

so is there any other option to bring to the Y axis non numerical values ?

0 Karma

gyslainlatsa
Motivator

hi milande,
trying to write this Wrg_Count = count( eval(testLogic="WRG_PASS") )' to see if y 'will always be an error message

0 Karma

acharlieh
Influencer

That will always be an error. In an eval like command, count is not a function, in a stats like command you use an as clause to rename the field not =

0 Karma

milande
Path Finder

so how many faces does have "count" ?
a) in case:
| eval count (Field1) as X

X will have count number of of Field1, right ?

b) in case:
| eval count (eval(Field1="something")) as X
will this work ? Is having eval-count-eval order allowed ?

c) in case:
| stats count by Field1
no new field will be produced right ? But we could in the next pipe use "count" as variable, right ?
e.g.
| stats count by Field1 | eval Add_Status=if(count > 0,"PASS","FAIL")

0 Karma

aweitzman
Motivator

As @acharlieh and I explained, cases (a) and (b) are syntax errors. count is not a function you can use with eval.

If you want a new field X to be added to each event that contains the total count of whatever you're counting, you need to use eventstats for that. Read here: http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/Eventstats

Case (c) returns a table with two fields, Field1 and count. All your other data is gone at that point. You can use count in the next pipe, though.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!