topic Re: IF statement inside EVAL in Splunk Search

IF statement inside EVAL

tonahoyos — Mon, 20 Aug 2018 20:49:06 GMT

Hello,

I want to divide AverageCount by AverageTotal. The problem is that Average count is separated by Sourcetype and Average Total is separated by a Field. For example:

index=x Sourcetype: SAT --> I calculate Average Count using this search
index=x Sourcetype:TotalTru Site:SAT --> I calculate Average Total by day using this search

Is there a way that I can use an eval statement by specifying with an if statement what site to relate the average to. I was thinking:

If sourcetype: SAT, then eval by site when site: SAT

Thanks!!

Re: IF statement inside EVAL

skoelpin — Mon, 20 Aug 2018 21:48:27 GMT

Yeah, use single quotes to capture a fields value like this

index="x" 
| bin _time span=1d
| stats eval(if(sourcetype="SAT",'CountEvents',"null")) AS count_avg eval(if(sourcetype="<SOURCETYPE>",'AverageCount',"null")) AS count_total
| eval Total=count_avg/count_total

Or use eventstats

Re: IF statement inside EVAL

tonahoyos — Tue, 21 Aug 2018 18:38:13 GMT

Hello,

Thank you for your answer. Should I still use the subsearch? and enter the subsearch with your answer?

Best.

Re: IF statement inside EVAL

skoelpin — Tue, 21 Aug 2018 18:40:39 GMT

No subsearch needed. Just use what I provided you

Also don't forget to accept/upvote if this answered your question

Re: IF statement inside EVAL

tonahoyos — Tue, 29 Sep 2020 21:00:37 GMT

I am using what you have provided and it states that the eval argument is invalid.

| stats count(Number) as CountEvents, avg(CountEvents) as AverageCount by date_mday, sourcetype
| bin _time span=1d
| stats eval(if(sourcetype="SAT",'CountEvents',"null")) as count_avg, eval(if(sourcetype="",'AverageCount',"null")) as count_total
| eval Total=count_avg/count_total

I have tried separating it, but it comes up as invalid also.

Re: IF statement inside EVAL

skoelpin — Tue, 21 Aug 2018 19:04:10 GMT

You don't have a sourcetype specified in your count_total eval..

Re: IF statement inside EVAL

skoelpin — Wed, 22 Aug 2018 14:06:14 GMT

@tonahoyos did this solve your question?

Re: IF statement inside EVAL

tonahoyos — Thu, 23 Aug 2018 18:55:01 GMT

Hello @skoelpin,

I have not been able to solve the question. I keep trying to work around it and see if there is something missing, but it has not worked out yet.

Thank you for your time.

Re: IF statement inside EVAL

skoelpin — Thu, 23 Aug 2018 19:22:49 GMT

Did you put the sourcetype in? This will solve the problem..

What are the 2 sourcetypes? I will update the query with your sourcetypes

Re: IF statement inside EVAL

tonahoyos — Thu, 23 Aug 2018 19:55:20 GMT

This is the following Query I am using and it is not working:

I want to be able to use these two results and divide them to make a ratio:

Site Total
DFW 353233
SAT 491025

sourcetype CountEvents
BUF 2983
DFW 5318
HNL 3730
ORD 7446
SAT 9213

What do you think I am doing wrong?

Re: IF statement inside EVAL

skoelpin — Thu, 23 Aug 2018 20:36:21 GMT

Why do you keep using a subsearch after I said you don't need it? You also didn't give me both sourcetypes...

Once again, the below query WILL WORK if you simply follow my advice... What are your two sourcetypes? Is it totaltru and SAT? If so then the query below WILL WORK

 index="jamlog" 
 | bin _time span=1d
 | stats eval(if(sourcetype="SAT",'CountEvents',0)) AS count_avg eval(if(sourcetype="totaltru",'AverageCount',0)) AS count_total by _time
 | eval Total=count_avg/count_total

Re: IF statement inside EVAL

tonahoyos — Thu, 23 Aug 2018 20:49:12 GMT

Yes, those are the sourcetypes. Thank you for your help, but the query did not work, that is why I am using the subsearch.

Re: IF statement inside EVAL

tonahoyos — Thu, 23 Aug 2018 20:54:43 GMT

The individual search works:

| timechart count(Number) as CountEvents by sourcetype

sourcetype="totaltru"
| timechart sum(Total) as Total by Site

but trying to include them in the same search in order to create a ratio is the problem.