Can an alert take variables?

[Pip9ball]

Hello All -

Is it possible to create a search or alert that is based on dynamic variables?

The end goal I'm trying to achieve is to send an email if any of the tests exceed a 10% increase in run time.  I have the following search query which generates a table I want, however I want this to run every night on between versionN and versionN-1.

index="fe" source="regress_rpt" pipeline="soc" version IN("23ww10b","23ww11a") dut="*" (testlist="*") (testName="*") status="*" earliest=-1mon latest=now() | eval lastTestPathElement=replace(testPath, ".*/" ,"") |search lastTestPathElement="**" | chart
     max(cyclesPerCpuSec) AS max:cyclesPerCpuSec
  BY version lastTestPathElement | transpose header_field=version column_name=test_run
| eval cycles_version_delta=('23ww11a' - '23ww10b')
,diff_percentage=round('cycles_version_delta'/'23ww11a' * 100, 1)
,status=if(diff_percentage < 10, "PASS", "FAIL")

Results Table

test_run	23ww10b	23ww11a	cycles_version_delta	diff_percentage	status
basic_test	631.68	663.80	32.12	4.80	PASS
basic_test.1	457.48	742.98	285.50	38.40	FAIL
basic_test.2	730.04	691.25	-.38.79	-5.60	PASS

This search is hard-coded to the version 23ww10b and 23ww11a.  I'd like to be able to automatically run this search on the latest version and latest version - 1 as well as send an email if there is any FAIL in the status column.

What is the best way to do this, if even possible.

Thanks,

Phil

[yeahnah]

Hi @Pip9ball 

You really have three different problems and yes, they all likely solvable.

1. Find the last 2 latest versions - you should be able to create a search for this from the existing data)
2. Pass to a search query - use the map command to pass variables into search
3. Send an email notification if threshold passed  (simply create an Alert)

#1 - see if you can work out a search query to find the latest two versions

#2 here's a run anywhere example using the map command to pass in variable values into a search

| makeresults
| eval v1="23ww10b"
,v2="23ww11a"
| map search="
| makeresults
| eval $v1$="5555"
,$v2$="9999"
,diff=('$v2$' - '$v1$')
,pct_diff=(diff/'$v2$' * 100)
| table $v1$ $v2$ diff pct_diff"

Note: map is a looping command so you only want to pass it 1 row with the result field values you pass in, i.e. the versions you find in step #1

#3 Once you have figured out 1 and 2 simply add a where clause, e.g.

...
| where pct_diff >= 10

and save the search as a scheduled Alert.

With a trigger action to email out if threshold meet.

Hopefully this is clear and there is enough general information to keep you going.

[Pip9ball]

I have written a search to find the last 2 versions, however I'm not sure how to store these results as a variable that I can then use.

Here is my search:

index="fe" source="regress_rpt" pipeline="soc" version IN(*) earliest=-1mon latest=now() |dedup version| table version | sort 2 -_time

This returns a table resembling:

version

23ww11a

23ww10b

I've saved this search as a report named "last2versons"

I'm not sure how to create a variable and extract the table cell data.  Ideally I want to assign $versionB to 23ww11a and $versionA to 23ww10b.  If I had these set, I could then use the following search.

|savedsearch last2versions SET VARS HERE| search= " index="fe" source="regress_rpt" pipeline="$pipeline$" version IN("$versionA$","$versionB$") dut="*" (testlist="*") (testName="*") status="*" ... "

Thanks,

Phil

[yeahnah]

Hi @Pip9ball 

Here's how I'd do it so that the result was a single row with field names versionA and versionB.

index="fe" source="regress_rpt" pipeline="soc" version IN(*) earliest=-1mon latest=now()
| stats count BY _time version ```_time will be in ascending order - newest to oldest ``` 
| head 2
| transpose
| rename "row 1" AS versionB "row 2" AS versionA
| where column="version"

Note, using stats command is a far more efficient way to deduplicate fields, as the work will be offloaded to the indexers, especially good if trawling through lots of data.

Now, you can think of a column header name as a field name, which can be passed to the map command.  So, stitching it all together, something like this should work.

index="fe" source="regress_rpt" pipeline="soc" version IN(*) earliest=-1mon latest=now()
| stats count BY _time version  ```_time is ascending order - newest to oldest ``` 
| head 2
| transpose
| rename "row 1" AS versionB "row 2" AS versionA
| where column="version"
| map search="index="fe" source="regress_rpt" pipeline="soc" version IN("$versionA$","$versionB$") dut="*" (testlist="*") (testName="*") status="*" earliest=-1mon latest=now()
  | eval lastTestPathElement=replace(testPath, ".*/" ,"")
  | search lastTestPathElement="**"
  | chart max(cyclesPerCpuSec) AS max:cyclesPerCpuSec
    BY version lastTestPathElement
  | transpose header_field=version column_name=test_run
  | eval cycles_version_delta=('$versionB$' - '$versionA$')
        ,diff_percentage=round('cycles_version_delta'/'$versionB$' * 100, 1)
        ,status=if(diff_percentage < 10, "PASS", "FAIL")
  "
| where status="FAIL"

Hopefully,  no typos above but can't validate without data.

I don't think the savedsearch command and passing variables in really works in this case - you'll still have to use map somewhere. 

If I was going to improve it, I'd look to have a lookup file listing the different versions in time order.  A scheduled search could update it once a day, or at the required frequency to keep it accurate.  Then the lookup could be used to quickly find the two latest version and pass it to the map search.  That would likely speed up the search.

Hope this helps

[Pip9ball]

Thanks for the help, I'm getting closer but still not quite there.

I took your advice and created a lookup csv to add the versions based on time and I'm not looking them up to grab the latest 2 versions.  

In my search, I'm getting a warning saying the query couldn't be run and I don't know how to go about debugging what is wrong.  Strangely enough, if I copy/paste the exact query in a new search it runs just fine.

|inputlookup gk_versions.csv | head 2 | transpose
| rename "row 1" AS versionB "row 2" AS versionA
| where column="version" | map search="source=\"regress_rpt\" pipeline=\"soc-fshbd-a0\" version IN(\"$versionA$\",\"$versionB$\") dut=\"*\" testlist=\"*\" testName=\"*\" status=\"*\" earliest=-1mon latest=now()
  | eval lastTestPathElement=replace(testPath, \".*/\" ,\"\")
  | search lastTestPathElement=\"**\"
  | chart max(cyclesPerCpuSec) AS max:cyclesPerCpuSec
    BY version lastTestPathElement
  | transpose header_field=version column_name=test_run
  | eval cycles_version_delta=('$versionB$' - '$versionA$')
        ,diff_percentage=round('cycles_version_delta'/'$versionB$' * 100, 1)
        ,status=if(diff_percentage < 10, \"PASS\", \"FAIL\")
  "

Any idea what could be my problem?

Thanks,

Phil

[yeahnah · Accepted solution]

Hi @Pip9ball 

Ah sorry, I think you simply need to specify the search command at the start of the map command search string, as the map search is not the implied by being the first SPL command 

https://docs.splunk.com/Documentation/SplunkCloud/9.0.2209/SearchReference/Search

So...

|inputlookup gk_versions.csv | head 2 | transpose
| rename "row 1" AS versionB "row 2" AS versionA
| where column="version"
| map search="search source=\"regress_rpt\" pipeline=\"soc-fshbd-a0\" version IN(\"$versionA$\",\"$versionB$\") dut=\"*\" testlist=\"*\" testName=\"*\" status=\"*\" earliest=-1mon latest=now()
  | eval lastTestPathElement=replace(testPath, \".*/\" ,\"\")
  | search lastTestPathElement=\"**\"
  | chart max(cyclesPerCpuSec) AS max:cyclesPerCpuSec
    BY version lastTestPathElement
  | transpose header_field=version column_name=test_run
  | eval cycles_version_delta=('$versionB$' - '$versionA$')
        ,diff_percentage=round('cycles_version_delta'/'$versionB$' * 100, 1)
        ,status=if(diff_percentage < 10, \"PASS\", \"FAIL\")
  "

That seemed to fix it for me when I tried it.

[Pip9ball]

Yes, this is working now.

Thanks!

-Phil