Use lookup table to populate otherwise empty rows

[JDukeSplunk]

Not something I've ever been asked to do, but management wants a dashboard that contains a row for all of possible values for an app, whether or not that application has produced any events in the search span or not.

I've generated a lookup table, contains them all. I just need to fold it into a stats table without always having anything in that stats table to match it up to. Basically, when the search runs, if there are no other stats for that application, an otherwise blank row with the application name is expected.

Here's what I've got. The mule_serverinfo_lookup works fine, it matches up host with it's know environments and clusternodes. Its the mule4_appnames.csv that contains column "application" that needs to fill in the "empty" rows.

index=someindex host=somehost sourcetype="mule-app"  mule4_appname=enterworks-web-content-digital-assets OR mule4_appname=ecc-po-data-hubspan 
| lookup mule_serverinfo_lookup hostname AS host OUTPUTNEW  ServerType version ClusterNode
| stats count(mule4_appname) as Total by mule4_appname ServerType 
| appendpipe 
    [| inputlookup  mule4_appnames.csv 
    | table application ServerType ]
    | search application=ecc-po-data-hubspan OR application=enterworks-web-content-digital-assets OR mule4_appname=enterworks-web-content-digital-assets OR mule4_appname=ecc-po-data-hubspan 
    | table mule4_appname application ServerType Total

In this example one of the apps, enterworks, exists in the index and the lookup. The other does not.

The result of the search looks like this.

[rmmiller · Accepted solution]

I think this does what you're looking for. Replace your appendpipe command and everything down from there with this.

| appendpipe
    [| inputlookup mess.csv
    | table application ServerType]
| eval matchname=coalesce(mule4_appname,application)
| search application=ecc-po-data-hubspan OR application=enterworks-web-content-digital-assets OR mule4_appname=enterworks-web-content-digital-assets OR mule4_appname=ecc-po-data-hubspan
| stats values(mule4_appname) AS mule4_appname values(application) AS application  values(ServerType) AS ServerType values(Total) AS Total BY matchname
| eval ServerType=if(isnull(mule4_appname),null(),ServerType)
| fields - matchname

I wasn't sure whether you were trying to narrow your search down to these 2 application names with your last search command (after the appendpipe), or if that was just part of your sanity check on the SPL before turning it loose on the complete list.

I also wasn't sure whether your lookup CSV contained any values for the ServerType column. Just in case it does, I blank it out if the Total (count) is null to meet your requirement of a basically blank line with just the application field. You can remove that eval statement if your application lookup doesn't contain any ServerType column.

This combines everything with a temporary field with stats, then dropped that field to marry up the original stats with the application lookup, thereby providing your pseudo-join with (possibly) nothing to match up with.

---

Sample query for testing logic:

| makeresults
| eval _raw="mule4_appname,ServerType
enterworks-web-content-digital-assets,Batch_Low
enterworks-web-content-digital-assets,Batch_Low
enterworks-web-content-digital-assets,Batch_Low
enterworks-web-content-digital-assets,Batch_Low
enterworks-web-content-digital-assets,Batch_Low
enterworks-web-content-digital-assets,Batch_Low
enterworks-web-content-digital-assets,Batch_Low
enterworks-web-content-digital-assets,Batch_Low
enterworks-web-content-digital-assets,Batch_Low
enterworks-web-content-digital-assets,Batch_Low
enterworks-web-content-digital-assets,Batch_Low
someother-app-name,WhoCares
enterworks-web-content-digital-assets,Batch_Low"
| multikv forceheader=1
| stats count AS Total,values(ServerType) AS ServerType BY mule4_appname
`comment("Everything above this line mimics data from your search")`
| appendpipe
    [| inputlookup mess.csv
    | table application ServerType]
| eval matchname=coalesce(mule4_appname,application)
| stats values(mule4_appname) AS mule4_appname values(application) AS application  values(ServerType) AS ServerType values(Total) AS Total BY matchname
| eval ServerType=if(isnull(mule4_appname),null(),ServerType)
| fields - matchname

With sample lookup mess.csv:

application,ServerType
another-name-here,NotImportant
enterworks-web-content-digital-assets,Batch_Low
ecc-po-data-hubspan,WhoKnows
someother-app-name,WhoCares

Hope that helps!
rmmiller

[rmmiller]

@JDukeSplunk how does this line up with your requirements? Does it need tweaking or did it completely miss the mark? --rmmiller

[JDukeSplunk]

I was able to come back to this one today, and with some variations of what was posted here got something to do what I was looking for.

To clarify what I'm trying to do a little better. Simply put, I have a 30 day job that populates all possible application names, about 100 of them. So lets say the indexed results for the last 60 minutes only 20 of those applications reported something to Splunk, Splunk should use lookup file to populate the other 80 rows in the stats table with the names of the applications in column1, even if the other columns are fill-nulled.

Even though I don't fully understand what the "Eval servertype" line is doing yet..And I still have to go back to the bigger search that the dashboard uses and try and get all of the other columns back for mule4_appname...

This seems to work for me so far.. as a base search to build on to.

index=something host=something mule4_application=*
| appendpipe 
    [| inputlookup mule4_appnames.csv 
    | fields application]
| eval matchname=coalesce(mule4_appname,application)
 | lookup mule_serverinfo_lookup hostname AS host OUTPUTNEW  ServerType version ClusterNode
 | eventstats values(mule4_appname) AS mule4_appname values(application) AS application  values(ServerType) AS ServerType values(Total) AS Total values(ClusterNode) AS ClusterNode BY matchname
  | eval ServerType=if(isnull(application),null(),ServerType)
  | fillnull 
  | stats count(host) as Total by matchname ServerType

[rmmiller]

OK, so the gist is correct.

The "eval servertype" line is there only because your original post made it look like your mule4_appnames.csv lookup file contained a ServerType column. If that were the case, yet there weren't any events for that application, the ServerType column would be blanked out to meet the requirement of a blank line with just the application name on it. You can completely drop this line after looking at the revised search in your comment above, since it's now clear you aren't bringing a ServerType field out of that lookup file. It's not needed.

[JDukeSplunk]

Just the final that seems to work. Again, this takes existing mule4_appname entries and combines them with the "list" of all apps as rows for "quiet" applications that havent reported in the timespan of the search. What a pain this was..

index=mule4 environment=Dev sourcetype="mule-app" mule4_appname=*  source!="/opt/mule/logs/mule_ee.log"  
| appendpipe 
    [| inputlookup mule4_appnames_environment.csv 
    | fields application environment]
| eval mule4_appname=coalesce(mule4_appname,application)
| stats count(sourcetype) as Total count(eval(mule_messageStatus=="Complete")) AS Complete count(eval(mule_messageStatus=="Completed")) AS Completed count(eval(mule_messageStatus=="Received")) AS Received count(eval(mule_messageStatus=="Failed")) AS Failed count(eval(mule_messageStatus=="Success")) AS Success  count(eval(mule_messageStatus=="success")) AS success latest(_time) as lastHappenTime values(ServerType) AS ServerType count(eval((ClusterNode=="Node1"))) AS Node1 count(eval((ClusterNode=="Node2"))) AS Node2  values(environment) as environment by mule4_appname  
| eval Success=Success+success
| eval CompleteDontNeed=Completed+Complete
| eval Diff=Received-Success-CompleteDontNeed, Diff=abs(Diff)
| eval node_diff=Node1-Node2, node_diff=abs(node_diff)
| eval ClusterTotal=Node1+Node2
| eval Node1Percent=round((Node1/ClusterTotal)*100,1) , Node2Percent=round((Node2/ClusterTotal)*100,1), NodePercentDiff=Node1Percent-Node2Percent, NodePercentDiff=abs(NodePercentDiff)
| eval lastHappenTime=strftime(lastHappenTime,"%H:%M:%S") 
| fields Row mule4_appname environment ServerType lastHappenTime Total ClusterTotal Node1 Node2 Node1Percent Node2Percent NodePercentDiff node_diff  Received CompleteDontNeed Success Diff Failed 
| rename mule4_appname As "Mule 4 App Name"
| rename ServerType As "Mule Cluster"
| rename node_diff AS "ClusterNode Diff"
| sort  - Diff
| eval Row=1 
| accum Row

[rmmiller]

Nice! Always good to see the finished product! --rmmiller

[fk319]

I have read this a could of times, and not really sure of what you want to do.
I can suggest reversing the order of your searches.

|inputlookup

|join app [search to get data]