Multiple If Statements (Comparing Two Columns)

[dfrench151]

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.

[renjith_nair]

@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.

[dfrench151]

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)))

[renjith_nair]

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")

[dfrench151]

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