Need help with search based on values that are duplicated but have different status levels.

[jiaqya]

In my data, there are duplicate rows for a server, but their status is "active" or "deleted".

Based on the field value I would like "Active" records to be returned at all times when both "active" and "deleted" are present.

If "active " is not present, then I want the "deleted" record to be shown.

Sample data result below. Please let me know how this can be done.

| makeresults
| eval server="one",column1="1",column2="2",column3="3",_time="09-02-2020",status="active"
| append [| makeresults | eval server="one",column1="1",column2="2",column3="3",_time="09-01-2020",status="deleted"]
| append [| makeresults | eval server="one",column1="1", column2="2",column3="3",_time="08-31-2020",status="deleted"]
| append [| makeresults | eval server="two",column1="3",column2= "2",column3="1",_time="09-02-2020",status="active"]
| append [| makeresults | eval server="three",column1="4", column2="5",column3="6",_time="09-01-2020",status="deleted"]
| table server,status,column1,column2,column3,_time

Output of the above makeresult is below:

server	status	column1	column2	column3	_time
one	active	1	2	3	9/2/2020
one	deleted	1	2	3	9/1/2020
one	deleted	1	2	3	8/31/2020
two	active	3	2	1	9/2/2020
three	deleted	4	5	6	9/1/2020

In this output, I would like to see the "active" record for server "one", active record for server "two", and "deleted" record for server "three".

[koshyk]

Please try below..

| makeresults
| eval server="one",column1="1",column2="2",column3="3",_time="09-02-2020",status="active"
| append [| makeresults | eval server="one",column1="1",column2="2",column3="3",_time="09-01-2020",status="deleted"]
| append [| makeresults | eval server="one",column1="1", column2="2",column3="3",_time="08-31-2020",status="deleted"]
| append [| makeresults | eval server="two",column1="3",column2= "2",column3="1",_time="09-02-2020",status="active"]
| append [| makeresults | eval server="three",column1="4", column2="5",column3="6",_time="09-01-2020",status="deleted"]
| table server,status,column1,column2,column3,_time
| eval priority=case(status=="active",2,status=="deleted",1)
| eventstats max(priority) as max_priority by server
| where max_priority=priority
| fields - priority,max_priority