Use string stored in field to assign value using if

[ErikaE]

I am using a search of real-time data and a lookup to check whether certain problems exist based on the data.

For example:

SensorA             SensorB            SensorC        ifStringfromLookup 
3                   5                  10             SensorC<3 AND SensorB<1 
3                   5                  10             SensorA>1 AND SensorC<12 

What I would like to be able to do is check to see if the current sensor values match any of the conditions of interest.

I've tried

| eval Problem=if(ifStringfromLookup, 1, 0)
| eval Problem=if($ifStringfromLookup$, 1, 0) 
| eval Problem=if(tostring(ifStringfromLookup), 1, 0)  

And none of these work. I would like the string stored in the field ifStringfromLookup to be used to evaluate the if function. I've tried searching splunk answers and the help but haven't found anything that works.

[DalJeanis · Accepted solution]

woodcock's method is cool, but for YOUR need, there's a much simpler method.

I'm going to assume the following...

sensorconditionstest.csv contains records with two fields - testname and conditions, populated as follows...

| makeresults 
| eval conditions="SensorC<3 AND SensorB<1!!!!SensorA>1 AND SensorC<12" 
| makemv delim="!!!!" conditions 
| mvexpand conditions 
| streamstats count 
| eval testname="TestX".count 
| table testname conditions
| outputcsv sensorconditionstest.csv

sensorreadingtest.csv contains four fields, _time, SensorA, SensorB, SensorC, populated as follows...

| makeresults 
| eval sensors="A=3,B=5,C=10 A=0,B=0,C=2 A=1,B=5,C=3" | makemv sensors | mvexpand sensors 
| streamstats count as recno 
| makemv delim="," sensors | mvexpand sensors 
| table recno sensors 
| rex field=sensors "(?<sensor>[^,=]+)=(?<reading>[^,]+)" 
| eval {sensor}=reading
| fields - sensor sensors reading 
| stats values(*) as Sensor* by recno
| eval _time =relative_time(now(),"-10m@h") + 60*recno
| table _time Sensor*
| outputcsv sensorreadingtest.csv

Given the above, this allows you to step through the conditions file and use each record to pass the readings file.

| inputcsv sensorconditionstest.csv
| map maxsearches=10 search="| inputcsv sensorreadingtest.csv | search [| inputcsv sensorconditionstest.csv | where testname = \"$testname$\" | table conditions | rename conditions as search ] | eval testname = \"$testname$\""

It would be possible to pass it only once, but you would not know which test or tests had been triggered, and a reading record that triggered multiple tests would only appear once.

[ErikaE]

I'll have to do some testing to understand if this works.

Would it change your answer if the sensor readings are coming from an index, not from a csv file? With the way our instance is configured I'm not sure I have rights to output a csv file, I've never tested it.

sensorconditionstest.csv has several additional columns which provide further information which I would like to use in the final output of the search. I'd like to use an uploaded file - it has several text columns. Some of the columns are formatted such that, when combined with the sensor data in the index, will either satisfy or not satisfy the if statement. I'd like an output something like this:

 SensorA             SensorB            SensorC        ifStringfromLookup AddtlLookupField Bool
 3                   5                  10             SensorC<3 AND SensorB<1  "fielddata " 0
 3                   5                  10             SensorA>1 AND SensorC<12 "fielddata" 1

Do I need to make the sensor readings a csv file for this to work?

[hsesterhenn_spl]

@DalJeanis That is really cool 🙂 I hope I get all details right.
Keep me honest if I am telling nonsense...

@ErikaE The magic is the subsearch [ | ... ] by using the "rename" command, the result of the subsearch is in fact the condition as a searchstring. So you would run all conditions one after the other with every input row of the CSV. If the condition matches you will get the values of SensorA, SensorB and SensorC as a result.

There used to be a better explanation in older versions of the Docs:
http://docs.splunk.com/Documentation/Splunk/4.3.7/User/HowSubsearchesWork
See this great Answers article:
https://answers.splunk.com/answering/7477/view.html

Well... it’s explained in “format”...
http://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/format

Try:
| inputcsv sensorconditionstest.csv | where testname = "TestX2" | table conditions | rename conditions as search | format

To answer your first question: Yes, instead of "| inputcsv sensorreading" you can use any search, especially if the data is stored in a Splunk Index.

And for every column in sensorconditionstest.csv you just have to add an eval like " | eval testname = \"$testname$\" ". e.g. ' | eval conditions=\"$conditions$\" ' which would show the filter condition in addition to the name of the condition.

Does it make sense?

Holger

[DalJeanis]

@hsesterhenn_splunk - Well said.

@ErikaE - You can replace each csv with a more complete search, as long as you replace it with something which effectively returns the values from the same search in each place the csv appears. It's just easier to read (and to explain) in the simple csv form.

The first input sensorconditionstest.csv is currently using only the value of testname, but if you need additional data from that test, it will need to come through the query from that side. The second input sensorconditionstest.csv is using the testname and the conditions, and you would need to keep it that way for it to operate as designed.

[woodcock]

I answered this in another Q&A with a slight twist in that the match strings are coming from a lookup file, not inside of events (it pulls the data into events in the beginning so it should be easy for you to track and adjust):

https://answers.splunk.com/answers/386488/regex-in-lookuptable.html

[ErikaE]

I'm not familiar with many of the commands you're using in that example, hope you'll forgive the follow up. So I need an if statement that looks something like:

| Problem=if(match(ifStringfromLookup, REGEX goes here), "yes", "no") 

Where match will return a bool that if can use based on the string provided and the regular expression supplied.

I'm not sure how to get write the REGEX goes here part based on looking at your example. I also don't understand if map is required, and if so, what its function is.