Splunk Search

How to use fillnull or similar before an eval?

MScottFoley
Path Finder

As I write this I realize that what I want is likely not possible using this method.  I want a fillnull (or similar) to happen before an eval.  The eval is likely not even called if there are no events in the timechart span I am looking at.  I want the eval it to return a 1 when there are no events in that span.      

This works, but is missing the eval.  

index=main sourcetype=iis  cs_host="site1.mysite.com"
| timechart span=10s  Max(time_taken)
| fillnull value=1

This is what I am using.  It works, except for when no events happen.

index=main sourcetype=iis  cs_host="site1.mysite.com"
| eval site1_up=if(sc_status=200,1,0)
| timechart span=10s  Max(site1_up)

This charts a 1 if there was at least one 200 response from site1.mysite.com in the 10s span.  It charts a 0 if there were responses, but none were 200.  If there are no matching events it is probably not even looked at and returns nothing and the chart looks like a 0.  I want a 1 charted if there are no events in that 10s span.       

Adding | fillnull value=200 sc_status after the timechart simply shows an extra column of sc_status at 200 in every span (column in the chart).  Putting this before the eval does not work since I believe nothing is done without an event.  It should also only use fillnull (or similar) if no events are in that 10 second span.   I have also tried | append [| makeresults ] without success, but don't completely know how that would work.   

Logically this is what I want.  The reasoning for the up/down status is not important since this is simply an example.  

For each 10s span in the timechart
|eval Site1_up=1 if cs_host=A and at least one sc_status=200
|eval Site1_up=0 if cs_host=A and at no sc_status=200
|eval Site1_up=1 if there are no events matching cs_host=A

|eval Site2_up =1 if cs_host=B and at least one cs_method=POST
|eval Site2_up =0 if cs_host=B and at no cs_method=POST
|eval Site2_up =1 if there are no events matching cs_host=B

|eval Site3_up =1 if cs_host=C AND cs_User_Agent=Mozilla and at least one cs_uri_stem=check.asmx
|eval Site3_up =0 if cs_host=C AND cs_User_Agent=Mozilla and no cs_uri_stem=check.asmx
|eval Site3_up =1 if there are no events matching cs_host=C

I am trying to make a chart of the up(1)/down(0) status of various components, some of which are determined by the IIS logs. 

Thanks      

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

If you have 

| eval site1_up=if(sc_status=200 AND Type="Release",1,0)

then only status/200+Release will be 1, everything else is 0, so fillnull will not have an impact unless there is no data at all for the time span. Removing 'Type' condition will just mean possibly more results will have value 1 than zero - the only chance something can be null is if there are no events at all during the period. Everything else will have a zero or 1 value to start with.

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

I'm not sure I get how fillnull does not work.

timechart will produce a 10 second interval table with empty values in the Max(site1_up) field where there is no data, so fillnull should work in your second example, as in the first.

This general case

|eval SiteX_up=1 if there are no events matching cs_host=X

can naturally not be tested in the eval with no data, so the fillnull should work in all cases.

Can you give an example of how fillnull is NOT filling in any gaps in the data?

0 Karma

MScottFoley
Path Finder

I checked the fillnull again and It does work using the basic format.  I am looking at multiple things and charting more than one value using a case statement with the eval based on the case.  

(index=main OR index=xxx) sourcetype=iis
| eval Type=case(
   cs_host="site1.mysites.com" AND like(SOAPAction,"%release%"), "Release",
   cs_host="site2.mysites.com" AND like(SOAPAction,"%verify%"), "Verify",
   1=1,"Nothing")
| eval site1_up=if(sc_status=200 AND Type="Release",1,0)
| eval site2_up=if(sc_status=202 AND Type="Verify",1,0)
| timechart Max(site1_up) as Site1 Max(site2_up) as Site2
| fillnull value=1 // Site1 Site2

If I remove the AND Type="" from one of the evals the fillnull will fix that one.  I need to use the Type on the eval to do it correctly and I think that is the problem.  I have thought of counting the number of events in the time span that match each Type and setting the site_up=1 if it is zero.  That may involve time buckets and have not looked into that.  I also thought of appending each unique search instead of using case.  I think that would work if it does not cause another problem.        

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

If you have 

| eval site1_up=if(sc_status=200 AND Type="Release",1,0)

then only status/200+Release will be 1, everything else is 0, so fillnull will not have an impact unless there is no data at all for the time span. Removing 'Type' condition will just mean possibly more results will have value 1 than zero - the only chance something can be null is if there are no events at all during the period. Everything else will have a zero or 1 value to start with.

0 Karma

MScottFoley
Path Finder

I ended up realizing this when I was replying to your first comment.  I'll simply use one chart per item now.   

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...