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