Dashboards & Visualizations

Conditionally Append Rows to Stats Table

chrisdopuch
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

somesoni2
Revered Legend

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]

chrisdopuch
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

somesoni2
Revered Legend

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

0 Karma

chrisdopuch
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

chrisdopuch
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

somesoni2
Revered Legend

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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.