topic Multiple If Statements (Comparing Two Columns) in Splunk Search

Multiple If Statements (Comparing Two Columns)

dfrench151 — Tue, 29 Sep 2020 22:42:00 GMT

Hello,

I have information being indexed from a website that does constant ping tests. The information that I am retrieving is the name of the website, the date and time, and status (which is either up, down, unconfirmed_down). I was able to write a command to pull this information and sort by status so that I know how many responses per status. The problem with my code is that if either of the status fields up, down, unconfirmed_down don't have any information in it, my final results end up turning blank. I know this is occurring because when I try to create the total time of the status and any of those blanks then total is empty or null, which doesn't allow me to get the percentage. I have been trying to figure out the best way to calculate the total field. I have been trying to use case and if statements so that if any of those fields are empty then it will only add the other fields together... I know that might be a little confusing but here is the code.

index=pingdom Name="appointments.jennycraig.com/set-appointment" | timechart count(status) span=1mon by status | table _time, down, up, unconfirmed_down, total  | eval total = case(isnull(down+up+unconfirmed_down),up,up=if(isnull(unconfirmed_down),up+down,up+unconfirmed_down),1=1,up+down+unconfirmed_down) | eval down=down/total*100, down=round(down,3),up=if(isnull(unconfirmed_down),up,(up+unconfirmed_down))/total*100, up=round(up,3)

This is one of the variations. I have tried to use if statements within case, but I don' t think that is possible. Does anyone have a better solution to this?

Thanks in advance for your assistance in this. I have been putting many hours of thought into this before finally throwing in the towel for help.

Re: Multiple If Statements (Comparing Two Columns)

dfrench151 — Sat, 12 Jan 2019 13:49:47 GMT

I should also note, I have attempted to nest multiple if statements within an if statement, but I don't think I can do this either, that or I am not correctly writing it out... It would be preferable to have an if statement use the other field if one field has nothing into it, but I don't think you can create variables that update depending on the results

Re: Multiple If Statements (Comparing Two Columns)

renjith_nair — Sat, 12 Jan 2019 15:20:24 GMT

@dfrench151,

As you mentioned if you are getting the total as empty because one of the fields is null, then fill the null with 0 before calculation or use addtotals.

|fillnull value=0 down, up, unconfirmed_down

If this is not what you are looking for, then sample data and required output will be helpful.

Re: Multiple If Statements (Comparing Two Columns)

dfrench151 — Sat, 12 Jan 2019 15:26:03 GMT

I have actually figured it out. Instead of trying to have my formula count based on if fields are null for the totals I found that I can just use the addtotals command to get the total number that I need so I can get my percentage. I am still curious though if it is possible to have an If statement within an if statement (i.e. If(x, then Y, else If(x, then, Y, else If(x, then y, then z)))

Re: Multiple If Statements (Comparing Two Columns)

renjith_nair — Sat, 12 Jan 2019 15:36:00 GMT

Yes it's possible, try below example. YOu may change values of a,b,c and see the difference

|makeresults|eval a=10,b=5,c=1|eval x=if(a==10,if(b==5,if(c==1,"True","C FALSE"),"B FALSE"),"A FALSE")