Dashboards & Visualizations

How to edit my search to make sure all my data in my columns appear?

bmendez0428
Explorer

On my dashboard I have 20 different rows of data. I have my dashboard refreshing periodically. There are times during the day where my rows randomly drop of due to some columns showing a null value on my data table. No matter what I want my 20 rows to appear (20 application names) no matter what the other columns values are?

Here is when I run my search query within a 24 hour time frame with this search. I want all 20 columns like this to appear. I attached my search code below.
alt text

index="TEM_Availability_Dashboard"|append[search index=\"dashboard_tem_application\"|search Active=1] |eval displayValue=case(TestResult_Value == "PASSED", "low", TestResult_Value == "FAILED", "severe") 
            |dedup Application_Name, TestCase_Value, SwimLane_Value, TestResult_Value |sort Application_Name, TestCase_Value 
|eval QA1 = case(like(TestResult_Value,"PASSED") AND SwimLane_Value=="QA1","low",like(TestResult_Value,"FAILED") AND SwimLane_Value=="QA1","severe", like(TestResult_Value,"NA") AND SwimLane_Value=="QA1","NA") 
|eval QA2 = case(like(TestResult_Value,"PASSED") AND SwimLane_Value=="QA2","low",like(TestResult_Value,"FAILED") AND SwimLane_Value=="QA2","severe", like(TestResult_Value,"NA") AND SwimLane_Value=="QA2","NA") 
|eval QA3 = case(like(TestResult_Value,"PASSED") AND SwimLane_Value=="QA3","low",like(TestResult_Value,"FAILED") AND SwimLane_Value=="QA3","severe", like(TestResult_Value,"NA") AND SwimLane_Value=="QA3","NA") 
|eval QA4 = case(like(TestResult_Value,"PASSED") AND SwimLane_Value=="QA4","low",like(TestResult_Value,"FAILED") AND SwimLane_Value=="QA4","severe", like(TestResult_Value,"NA") AND SwimLane_Value=="QA4","NA") 
|eval QA5 = case(like(TestResult_Value,"PASSED") AND SwimLane_Value=="QA5","low",like(TestResult_Value,"FAILED") AND SwimLane_Value=="QA5","severe", like(TestResult_Value,"NA") AND SwimLane_Value=="QA5","NA") 
|eval QA6 = case(like(TestResult_Value,"PASSED") AND SwimLane_Value=="QA6","low",like(TestResult_Value,"FAILED") AND SwimLane_Value=="QA6","severe", like(TestResult_Value,"NA") AND SwimLane_Value=="QA6","NA") 
|eval QA7 = case(like(TestResult_Value,"PASSED") AND SwimLane_Value=="QA7","low",like(TestResult_Value,"FAILED") AND SwimLane_Value=="QA7","severe", like(TestResult_Value,"NA") AND SwimLane_Value=="QA7","NA") 
|eval STG = case(like(TestResult_Value,"PASSED") AND SwimLane_Value=="STG","low",like(TestResult_Value,"FAILED") AND SwimLane_Value=="STG","severe", like(TestResult_Value,"NA") AND SwimLane_Value=="STG","NA")  
|eval STG2 = case(like(TestResult_Value,"PASSED") AND SwimLane_Value=="STG2","low",like(TestResult_Value,"FAILED") AND SwimLane_Value=="STG2","severe", like(TestResult_Value,"NA") AND SwimLane_Value=="STG2","NA") 
|eval PVE = case(like(TestResult_Value,"PASSED") AND SwimLane_Value=="PVE","low",like(TestResult_Value,"FAILED") AND SwimLane_Value=="PVE","severe", like(TestResult_Value,"NA") AND SwimLane_Value=="PVE","NA") 
|table Application_Name, TestCase_Value, QA1,QA2,QA3,QA4,QA5,QA6,QA7,STG,STG2,PVE |rename TestCase_Value AS "Test Case" |rename Application_Name AS "Application Name" 
|stats values(QA1) as QA1, values(QA2) as QA2,values(QA3) as QA3,values(QA4) as QA4,values(QA5) as QA5,values(QA6) as QA6,values(QA7) as QA7,values(STG) as STG,values(STG2) as STG2,values(PVE) as PVE by "Application Name", "Test Case" 
|eval QA1 = if((mvjoin(QA1, ",") == "low,severe" OR mvjoin(QA1, ",") == "severe,low"), "elevated", QA1) 
|eval QA2 = if((mvjoin(QA2, ",") == "low,severe" OR mvjoin(QA2, ",") == "severe,low"), "elevated", QA2) 
|eval QA4 = if((mvjoin(QA4, ",") == "low,severe" OR mvjoin(QA4, ",") == "severe,low"), "elevated", QA4) 
|eval QA5 = if((mvjoin(QA5, ",") == "low,severe" OR mvjoin(QA5, ",") == "severe,low"), "elevated", QA5) 
|eval QA6 = if((mvjoin(QA6, ",") == "low,severe" OR mvjoin(QA6, ",") == "severe,low"), "elevated", QA6) 
|eval QA7 = if((mvjoin(QA7, ",") == "low,severe" OR mvjoin(QA7, ",") == "severe,low"), "elevated", QA7) 
|eval STG = if((mvjoin(STG, ",") == "low,severe" OR mvjoin(STG, ",") == "severe,low"), "elevated", STG) 
|eval STG2 = if((mvjoin(STG2, ",") == "low,severe" OR mvjoin(STG2, ",") == "severe,low"), "elevated", STG2) 
|eval PVE = if((mvjoin(PVE, ",") == "low,severe" OR mvjoin(PVE, ",") == "severe, low"), "elevated", PVE) 
|eval QA3 = if((mvjoin(QA3, ",") == "low,severe" OR mvjoin(QA3, ",") == "severe,low"), "elevated", QA3)

I performed the same search again but this time I checked the last 15 minutes instead. Only 8 rows of data appear? I'm confused by this. The other 12 rows should have appeared as well. How do I change my search to make sure all of it appears? I don't mind using placeholders.
alt text

0 Karma
1 Solution

to4kawa
Ultra Champion
index="TEM_Availability_Dashboard" 
| append 
    [ search index=\"dashboard_tem_application\" 
    | search Active=1] 
| eval displayValue=case(TestResult_Value == "PASSED", "low", TestResult_Value == "FAILED", "severe") 
| dedup Application_Name, TestCase_Value, SwimLane_Value, TestResult_Value 
| sort 0 Application_Name, TestCase_Value 
| eval QA1="",QA2="",QA3="",QA4="",QA5="",QA6="",QA7="",STG="",STG2="",PVE="" 
| foreach QA1,QA2,QA3,QA4,QA5,QA6,QA7,STG,STG2,PVE 
    [| eval {SwimLane_Value} = case(like(TestResult_Value,"PASSED"),"low",like(TestResult_Value,"FAILED"),"severe", like(TestResult_Value,"NA"),"NA") ] 
| table Application_Name, TestCase_Value, QA1,QA2,QA3,QA4,QA5,QA6,QA7,STG,STG2,PVE 
| rename TestCase_Value AS "Test Case" 
| rename Application_Name AS "Application Name" 
| stats values(*) as * by "Application Name", "Test Case" 
| foreach QA* STG* PVE 
    [| eval <<FIELD>> = if(match(<<FIELD>>,"low") AND match(<<FIELD>>,"severe"), "elevated", <<FIELD>>) ]
| fillnull value="NA"

Hi, @bmendez0428
simply, fields is nothing in this case. try fillnull value="NA"

View solution in original post

0 Karma

to4kawa
Ultra Champion
index="TEM_Availability_Dashboard" 
| append 
    [ search index=\"dashboard_tem_application\" 
    | search Active=1] 
| eval displayValue=case(TestResult_Value == "PASSED", "low", TestResult_Value == "FAILED", "severe") 
| dedup Application_Name, TestCase_Value, SwimLane_Value, TestResult_Value 
| sort 0 Application_Name, TestCase_Value 
| eval QA1="",QA2="",QA3="",QA4="",QA5="",QA6="",QA7="",STG="",STG2="",PVE="" 
| foreach QA1,QA2,QA3,QA4,QA5,QA6,QA7,STG,STG2,PVE 
    [| eval {SwimLane_Value} = case(like(TestResult_Value,"PASSED"),"low",like(TestResult_Value,"FAILED"),"severe", like(TestResult_Value,"NA"),"NA") ] 
| table Application_Name, TestCase_Value, QA1,QA2,QA3,QA4,QA5,QA6,QA7,STG,STG2,PVE 
| rename TestCase_Value AS "Test Case" 
| rename Application_Name AS "Application Name" 
| stats values(*) as * by "Application Name", "Test Case" 
| foreach QA* STG* PVE 
    [| eval <<FIELD>> = if(match(<<FIELD>>,"low") AND match(<<FIELD>>,"severe"), "elevated", <<FIELD>>) ]
| fillnull value="NA"

Hi, @bmendez0428
simply, fields is nothing in this case. try fillnull value="NA"

0 Karma

bmendez0428
Explorer

Thank you. The fillnull worked for identifying the null values in my data. I'm probably going to have to find a way to hard code the application names / test cases.

0 Karma

to4kawa
Ultra Champion

| inputlookup Application_name.csv append=T
| inputlookup TestCase_Value.csv append=T
create and add #5.

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...