topic Re: How to edit my search to remove duplicates from a table? in Splunk Search

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

dsiob — Mon, 15 May 2017 13:58:39 GMT

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.

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

aakwah — Mon, 15 May 2017 15:07:16 GMT

Hello,

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

Status!=Red

Regards

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

kmorris_splunk — Mon, 15 May 2017 15:14:04 GMT

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

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

dsiob — Tue, 16 May 2017 03:18:40 GMT

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

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

dsiob — Tue, 16 May 2017 03:22:30 GMT

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.

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

aakwah — Tue, 16 May 2017 10:15:11 GMT

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

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

dineshraj9 — Tue, 16 May 2017 11:48:37 GMT

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

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

dsiob — Tue, 16 May 2017 13:28:59 GMT

thanks a lot for your working solution!!

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

aakwah — Tue, 16 May 2017 13:44:23 GMT

It is my pleasure !