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.
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
@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.
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.