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

[bmendez0428]

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.

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.

[to4kawa · Accepted solution]

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"

[bmendez0428]

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.

[to4kawa]

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