Dashboards & Visualizations
Highlighted

Conditionally Append Rows to Stats Table

Path Finder

Hello,

I am building a dashboard for system performance monitoring. There are 14 system calls that I need to keep track of. Some days, some of those calls are never made. The dashboard is created using HTML and SplunkJS, and specifically uses an UnderscoreJS data template view. The data template only renders a view for each result that is returned (this is important for our team, because we want this dashboard to be reusable and modular for other teams). So, if say only 10 of the 14 services have been called today, then only 10 boxes get rendered. We want all 14 boxes to be rendered, with the uncalled services being gray boxes with values of zero in their display.

I know that I can append rows to a stats table using the append command. Here is an example. What I need to do is conditionally perform this append, if some of those services have not been called.

In essence:

search ... | stats ... | IF ( service=serviceName is missing ) THEN ( append [ eval service=serviceName | eval count=0 | eval mean=0 | eval perc95=0 ] )

I would end up writing 14 of these conditionals in total, one for each service name.

I greatly appreciate any help!

0 Karma
Highlighted

Re: Conditionally Append Rows to Stats Table

SplunkTrust
SplunkTrust

Is there any way you can have list of all 14 services available (though lookup or any other way)? If yes then it would be easier that writing 14 conditional appends.

0 Karma
Highlighted

Re: Conditionally Append Rows to Stats Table

Path Finder

My team's goal is to have the dash be able to take a query from any of our other teams and then just work. We want to put as much of the logic into the query itself. A lookup could work, but that would add to the work other teams have to do to use it.
Do you know how to write a conditional append? That's primarily what I'm looking for here.

0 Karma
Highlighted

Re: Conditionally Append Rows to Stats Table

SplunkTrust
SplunkTrust

Give this a try

your base search ..| stats... 
| eventstats values(service) as CurServices
| appendpipe [|stats values(CurServices) as CurServices 
| eval service="Your,All,14,Services,list" | makemv delim="," service | mvexpand service 
| eval shouldInclude= if(isnotnull(mvfind(CurServices,service)),"no","yes") 
| eval count=0 | eval mean=0 | eval perc95=0 
| where shouldInclude="yes" | table service, count,mean, perc95 ] 
| fields - CurServices

Updated

Try this

|gentimes start=-1 | eval service="Your,All,14,Services,list" | table service| makemv delim="," service | mvexpand service | eval count=0 | eval mean=0 | eval perc95=0  | join type=left max=0 service [search your base search ..| stats...giving fields service,count, mean,perc95]
Highlighted

Re: Conditionally Append Rows to Stats Table

Path Finder

Nice work! This is really close. Since this query does "|stats first(CurServices) as CurServices" it only checks for the first service in the list of current services. Is there a good way to have it check for all of the services (some kind of for-each loop)? Or will I just need to write out this appendpipe for every service?
I tried removing "|stats first(CurServices) as CurServices" to see if the entire list would work, but it ended up appending far too many lines (about 5X as many as without it).

0 Karma
Highlighted

Re: Conditionally Append Rows to Stats Table

SplunkTrust
SplunkTrust

Just change the "| stats first(" with "|stats values(" and it should work.

0 Karma
Highlighted

Re: Conditionally Append Rows to Stats Table

Path Finder

Nice work! This still ends up appending services which are present, since it is only comparing it to the first(CurServices). It does so successfully though, the first one is always left out of the append.

What I have done for now to make this work is run a dedup on service after the append, which gets me where I need to be. Still curious if there's a better way to do this though. Thanks so much for your help!

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.