IF statement inside EVAL

[tonahoyos]

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

index="x"
| bin _time span=1d
| stats count(Number) as CountEvents by _time, sourcetype
| chart avg(CountEvents) as AverageCount by sourcetype
| append
[search sourcetype=TotalTru
| eval Total=if(sourcetype==SAT,
....

Thanks!!

[skoelpin]

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

[tonahoyos]

Hello,

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

Best.

[skoelpin]

No subsearch needed. Just use what I provided you

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

[tonahoyos]

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.

[skoelpin]

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

[skoelpin]

@tonahoyos did this solve your question?

[tonahoyos]

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.

[skoelpin]

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

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

[tonahoyos]

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

index="jamlog"
| timechart count(Number) as CountEvents by sourcetype
| appendcols
[search sourcetype="totaltru"
| timechart sum(Total) as Total by Site ]
| eval Ratio=CountEvents/Total
| stats avg(Ratio) by sourcetype

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?

[skoelpin]

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

[tonahoyos]

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.

[tonahoyos]

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