Error in 'eval' command: The expression is malformed. Expected IN.

[Nafees]

Hello People,

I am trying to run below splunk query,

base search | rename msg.message as "message", msg.customer as "customer" | eval Total_Count = 1, Total_Success = if(where isnull( msg.errorCode),"1","0"), Total_Error = if(where isnotnull( msg.errorCode),"1","0") | fields Total_Count,Total_Success,Total_Error,message,customer | stats sum(Total_Count) as Total, sum(Total_Success) as Success, sum(Total_Error) as Error | eval successRate = ((Success/Total)*100)."%" | stats Total, Success, successRate by customer

and I am getting below error

Error in 'eval' command: The expression is malformed. Expected IN.

Can anyone please let me know what am I doing wrong here? Thanks !!!

[Nafees]

Sure, Thank you @gcusello  for the help !!!

[Nafees]

Thank You so much @gcusello  for the response.

Actually what I am trying to do is, when the base search matches, the Total_Count should always get a value, basically here I want to count the total number of events and then the Total_Success and Total_Error should only be counted if their respective conditions are true. And then I want to calculate the rate of success by putting the event counts. Basically what I want to do it something like this

(total success events/total events)*100

that is the reason I gave "1"s to all the events and then sum it up at the last to get the total event count. That is the reason I gave Total_Count a "1" for each event, and then I applied a sum for that to get the total events count.

But I think this approach is not correct right?

I think the solution which you provided at the last answers this question and I can write my query as follows

base search 
| rename 
   msg.message AS "message", 
   msg.customer AS "customer"  
| stats 
   count as Total, 
   count(eval(isnull(msg.errorCode)) AS Success, 
   count(eval(isnotnull(msg.errorCode)) AS Error 
   BY customer
| eval successRate = ((Success/Total)*100)."%" 

Please let me know if my understanding is correct?

also, for success events, the msg.errorCode field will not come in the logs and it will come only for the error event logs. So I want to count an event as Success if the msg.errorCode field is not coming and as Error if its coming.

Hopelully, my logic of using "isnull" and "isnotnull" is correct in this case?

Please clarify and Thank you so much again for response and clearing my doubts. 🙂

[gcusello]

Hi @Nafees,

it seems to be correct, but you are the only that can validate results.

Tell me if I can help you more, otherwise, please accept my anser for the other people of Community.

Ciao.

Giuseppe

P.S.: Karma Points are appreciated 😉

[PickleRick]

The error is not about logic but about syntax.

if() needs a function returning a boolean value or a composite condition using boolean operators. If you just want to do if(isnull(something), something, something), you don't insert additional where.

[Nafees]

Sure @PickleRick , thank you for your help !!!

[gcusello · Accepted solution]

Hi @Nafees,

probably the error is in 

Total_Success = if(where isnull( msg.errorCode),"1","0"), 
Total_Error = if(where isnotnull( msg.errorCode),"1","0") 

you cannot use where isnull, 

please try this:

base search 
| rename 
   msg.message as "message", 
   msg.customer as "customer" 
| eval 
   Total_Count = 1, 
   Total_Success = if(isnull(msg.errorCode),"1","0"), 
   Total_Error = if(isnotnull(msg.errorCode),"1","0") 
| fields 
   Total_Count,
   Total_Success,
   Total_Error,
   message,
   customer 
| stats 
   sum(Total_Count) as Total, 
   sum(Total_Success) as Success, 
   sum(Total_Error) as Error 
| eval successRate = ((Success/Total)*100)."%" 
| stats 
   Total, 
   Success, 
   successRate 
   BY customer

then isn't so clear the sense of Total_Count=1 and then sum(Total_Count) that's always 1, for this reason I leaved it, but I don't understand it!

then you could put the the conditions in the stats command:

base search 
| rename 
   msg.message AS "message", 
   msg.customer AS "customer"
   msg.errorCode AS errorCode 
| eval 
   Total_Count = 1, 
| stats 
   sum(Total_Count) as Total, 
   count(eval(isnull(msg.errorCode)) AS Success, 
   count(eval(isnotnull(msg.errorCode)) AS Error 
   BY customer
| eval successRate = ((Success/Total)*100)."%" 

 Ciao.

Giuseppe