eval searchmatch command OR if command

[joseag]

Hi, I need some help. I have two fields that mark the status alert, PROBLEM and OK, I'm trying to compare them with the id_field
To identify the open alerts. I have a scenario like the following:

field_1      field_2    field_status
 id1          null        problem
 null         id1         ok
 id2          null        problem
 id3          null        problem
 null         id2         ok
 null         id3         ok
 id4          null        problem
 id5          null        problem

And so on do not have a defined standard arrival time.
 
I've tried with index = "main" | table fielf_1, field_2, field_status | eval x = if (searchmatch(field_1 LIKE field_2)1,0)
I do not know if I am omitting something in the eval function or if I should consider any other function.

Expected Result

field_1      field_2    field_status         x
 id1          null        problem           1
 null         id1         ok
 id2          null        problem           1
 id3          null        problem           1
 null         id2         ok
 null         id3         ok
 id4          null        problem           0
 id5          null        problem           0

Thanks for the support, regards.

[DalJeanis]

This sets up your test data...

| makeresults
| eval mydata="id1,NULL,problem id1,NULL,problem NULL,id1,ok id2,NULL,problem id3,NULL,problem NULL,id2,ok NULL,id3,ok id4,NULL,problem id5,NULL,problem"
| makemv mydata | mvexpand mydata 
| rex field=mydata "(?<field_1>[^,]*),(?<field_2>[^,]*),(?<field_status>.*)"
| eval field_1 = if(field_1="NULL",null(),field_1)
| eval field_2 = if(field_2="NULL",null(),field_2)
| streamstats count
| eval _time = _time +count
| table _time field_1 field_2 field_status

This gets you your desired value for x...

| eval myid=coalesce(field_1,field_2)
| eventstats count(field_2) as x by myid
| eval x = if(isnull(field_1),null(),x)
| table _time field_1 field_2 field_status  x 

---

Assumptions the sample code is based on...

When an alert occurs, a record is created with the ID in field_1 and "problem" in field_status.

When an alert is cleared, a record is created with the ID in field_2 and "ok" in field_status.

For any record with "problem" in field_status, you want to flag the record with 1 if it has been later cleared, and 0 if it has not.

For purposes of the above code, we've assumed that each ID can only appear once in the file in field_1 and once in field_2. The _time in the above code (an the record order) has no effect on the outcome.

On the other hand, if the same ID can appear multiple times and you want to know if there has been an ok record after this particular problem record, then _time is required and a different version is needed, as below

The sample data generator, with a few cases added...

| makeresults
| eval mydata="id1,NULL,problem id1,NULL,problem NULL,id1,ok id2,NULL,problem id3,NULL,problem NULL,id2,ok NULL,id3,ok id4,NULL,problem id5,NULL,problem id2,NULL,problem NULL,id5,ok"
| makemv mydata | mvexpand mydata 
| rex field=mydata "(?<field_1>[^,]*),(?<field_2>[^,]*),(?<field_status>.*)"
| eval field_1 = if(field_1="NULL",null(),field_1)
| eval field_2 = if(field_2="NULL",null(),field_2)
| streamstats count
| eval _time = _time +count
| table _time field_1 field_2 field_status

The _time based code...

| eval myid=coalesce(field_1,field_2)
| eval probtime=if(field_status="problem",_time,null())
| eval oktime=if(field_status="ok",_time,null())
| eventstats max(*time) as max*time by myid
| eval x = case(isnull(probtime),null(),probtime<maxoktime,1,true(),0)
| table _time field_1 field_2 field_status  x 

[DalJeanis]

@joseag - if your problem has been solved, please accept a solution. If you solved it a different way, please post your version of the solution as an answer, and accept your own answer.

[somesoni2]

The searchmatch is useful for matching against raw data (_raw field). Depending upon how you want to compare (they should be equal OR field_1 contains field_2 etc), you can use field compare functions like like or match or just equal sign.