My search is slow. I was wondering how should I convert my search into a macro?

[bmendez0428]

My search is running slow. I have a live dashboard and it is populated by a query in my search. I am new to Splunk but I managed to develop a dashboard project. I'm working on macros and I was wondering how should I develop a macro search to optimize my search, if that is even possible.

index="TEM_dashboard_main"|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 
|append [|dbxquery query="select distinct a.Application_Name, t.TestCase_Value, 'QA1','QA2','QA3','QA4','QA5','QA6','QA7','STG','STG2','PVE' from TEM_Application a left Outer Join Dashboard_TEM_Application d ON a.Application_Id = d.Application_Id left Outer Join TEM_TestCase t ON d.TestCase_Id = t.TestCase_Id left join [AZLIFEMazl6n2j].[Splunk]  V on t.TestCase_Value = V.TestCase_Value and V.TestCase_Value is null where a.Application_Name is not NULL AND d.Active = 1" connection="TEM_Database" timeout=600] 
|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)

[nickhills]

Macros don't speed up searches, they just simplify what you see in the search box, or allow you to easily reuse SPL in multiple queries.

A couple of things in your query can cause slowness though.

|dedup over long time ranges is quite expensive, often |stats latest() is faster.
A subsearch with dbxquery has a perf hit for two reasons. 1.) its a subsearch which means it has to execute first, 2.) its a DB query, so it introduces latency from the database tier.

Depending on how frequently your database updates, you may be better off using a dbinput to write the DB values to a Splunk index. Alternatively you can run a separate search just to run dbquery, and write the results to a lookup.

You could schedule something like:

|dbxquery query="select distinct a.Application_Name, t.TestCase_Value, 'QA1','QA2','QA3','QA4','QA5','QA6','QA7','STG','STG2','PVE' from TEM_Application a left Outer Join Dashboard_TEM_Application d ON a.Application_Id = d.Application_Id left Outer Join TEM_TestCase t ON d.TestCase_Id = t.TestCase_Id left join [AZLIFEMazl6n2j].[Splunk]  V on t.TestCase_Value = V.TestCase_Value and V.TestCase_Value is null where a.Application_Name is not NULL AND d.Active = 1" connection="TEM_Database" timeout=600
|outputlookup testresults.csv