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" a...
See more...
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".