How to edit my search to remove duplicates from a table?

[dsiob]

hi, I am using table which shows up duplicates, shown below. Here some track has multiple status (eg: Yellow and Red). In this case, row having 'Yellow' status for that track should appear.

[dineshraj9 · Accepted solution]

Try setting a priority field and using the same to get desired results -

<your search>  | eval priority=case(Status=="GREEN", 1, Status=="Yellow", 2, Status=="Red", 3) | stats min(priority) as priority by Track_Name | eval Status=case(priority==1, "GREEN", priority==2,"Yellow", priority==3,"Red") | table Track_Name Status

[kmorris_splunk]

What is the reasoning behind showing the row with "Yellow". Is this the latest status?

If so, you could try:

[YOUR CURRENT SEARCH]
| sort -Status
| dedup Track_Name

[dsiob]

Red and Yellow represents Priority level. I have to pick least priority that is Yellow.

[aakwah]

Hello,

Generally you can filter out results in your search query as per the following:

Status!=Red

Regards

[dsiob]

Red and Yellow shows the priority level. So if there is multiple priority for one track, row with least priority (Yellow) should be selected.
If any track having only single status (Red or Yellow), it should show as it is.

[aakwah]

Thanks for the clarification.

Then you need to use transaction command to create one big event that contains all the statuses for single track.

Now Status filed became multivalue filed as it may contains Red and Yellow at the same time, then we count the values with Status_count=mvcount(Status).

Finally we use case statements to determine the count of status, if it equals 2, then Status will be Yellow if not it will have the original value.

The complete query:

Search query | transaction Track_Name | eval Status_count=mvcount(Status) | eval Status=case(Status_count == 2, "Yellow", Status_count ==1 , Status)  | table Track_Name, Status

Hope this helps.

Regards

[dsiob]

thanks a lot for your working solution!!

[aakwah]

It is my pleasure !