Splunk Search

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

bmendez0428
Explorer

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)
0 Karma

nickhills
Ultra Champion

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
If my comment helps, please give it a thumbs up!
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...