Setting Variables on Data Load to feed Visualizations

[bpal]

I have my first query which creates a list of application names that are then displayed  in multiple single value fields.  This value fields are in the first column of a larger table.

| where count=1
| fields app

In the rest of the columns I need to put a single value field with the compliance rate of that application across multiple metrics.   What I'm looking to do is set a variable per low on data load that would allow me to ensure I pull the right compliance number for the application name.  

My original idea was to hard code the compliance visualization to search for a specific application name.  However if the list of applications is to change the metric will not match the name. 

So how does one set a variable on search load to be used by other visualization 

[ITWhisperer]

This is a bit theoretical, please can you give a concrete example of what your dashboard would look like?

[bpal]

@ITWhisperer fair enough.

The goal here is to have 3 queries

1. Gather a list of all application names  (Column 1)
2. Gather a list of compliance metric 1 for all applications (Column 2)
3. Gather a list of compliance metric 2 for all applications (Column 3)

For Column 1 I've been able to use a search for the first field to the 20th field for all rows.  My issue is that I need the application name to line up with compliance metric one and two.  In other words I can't select the first row and  second and then so on as if an application name gets added it will be out of order. 

What I'd like to do is take a variable that holds the result from column one and search for that exact application name in the compliance metrics to ensure I alway have the right one.

[ITWhisperer]

Where does the data for columns 2 and 3 come from? Please share your search(es)

[bpal]

Query For Application Names

| inputlookup `SmtOverride("")`
| search type=Platform
| eval app=appCode." (".appName. ")"
| stats count by app
| sort app
| streamstats count
| where count=1
| fields app

Query for Compliance Metric 1

`cce_container_summary_ds` type="platform" environment="*-prod" environment!="*-non-prod" scanner=*-prod
| stats values(temp) as temp by _time imageName registry appCode appCustodian l5 l4 l3ItHead environment scanner type assetType run  day p1s p2s p3s p4s p5s
| eventstats max(run) as max_run by imageName registry appCode environment scanner type day
| where run=max_run 
| eval temp=split(temp,"@#@#")
| eval due=mvmap(temp, mvindex(split(temp,"::"),4))
| where isnotnull(mvfilter(due>0)) OR isnull(due)
| lookup `SmtOverride("appCode OUTPUT appName")`
| eval p1p2=if(p1s>0 OR p2s>0, 1, 0)
| eval p3p4p5=if(p3s>0 OR p4s>0 OR p5s>0, 1, 0)
| stats sum(p1p2) as p1p2 sum(p3p4p5) as p3p4p5 by imageName registry appCode appCustodian type l5 appName
| eventstats dc(imageName) as total_image_count by appCode
| stats dc(eval(p1p2==1)) as p1p2 values(appCode) as appCode by imageName total_image_count appName
| stats sum(p1p2) as p1p2 by total_image_count appCode appName
| eval appCode=appCode. " - " .appName
| eval overall_perc=100-round((p1p2)*100/total_image_count,2)
| fields overall_perc appCode
| sort appCode
| streamstats count

Query for Compliance Metric 2

`cce_container_summary_ds` type="platform" environment="*-prod" environment!="*-non-prod" scanner=*-prod
| stats values(temp) as temp values(failingControls) as tss values(p1s) as p1s values(p2s) as p2s values(p3s) as p3s values(p4s) as p4s values(p5s) as p5s by _time imageName registry appCode type l5 environment scanner run day
| eventstats max(run) as max_run by imageName registry appCode environment scanner type day
| where run=max_run
| eval temp=split(temp,"@#@#")
| eval vSeverity=mvmap(temp, mvindex(split(temp,"::"),4))
| eval critical=mvcount(mvfilter(match(vSeverity,"critical")))
| eval high=mvcount(mvfilter(match(vSeverity,"high")))
| fillnull value="0" critical high
| eval tssStatus=if(critical=0 AND high=0, tssStatus, "Non-Compliant")
| stats count as totalAssets, count(eval(like(tssStatus, "Compliant"))) AS Compliant by appCode
| eval compliancePerc=round(((Compliant/totalAssets)*100),2)
| sort appCode
| streamstats count
| where count=1
| fields appCode compliancePerc

[ITWhisperer]

In order to line up the app name / code and the metrics, the format for the app code should be the same in all three searches (which currently it is not - you have three different ways of representing this). Also, on two of your searches, you have used 

| streamstats count
| where count=1

This is the same as

| head 1

That is, you will only get one event (possibly not what you were after?)

One way to get the results to line up is to append the three searches and then gather the results with a stats command

| stats values(metric1) as metric1 values(metric2) as metric2 by appcode

[bpal]

Is there no way to do with with a variable from the app name column to be used in the others?

I'd rather not merge all the queries into one as they are not the same sourcetype at the end of the day.

[ITWhisperer]

You could look at using the map command to pass the appcode from the first search to the other searches. However, I would hesitate to recommend this as it has performance and limits implications.

At the end of the day, the sourcetypes may be different in the initial searches (which is why they would be in separate searches which are appended to one another), but by the end they are similar, i.e. an app code and a metric (or two). Having said that, if you wanted to go down this route, you should still look at optimising the combined searches (but they are quite complex for someone who doesn't know your data to figure out what you are ultimately aiming to achieve).