- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Filter duplicate rows based on a condition
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Filter duplicate rows based on a condition
Hello Everyone!
I have an output in the below format and would like to filter the duplicate ids with 'fieldA' value as zero then calculate 'fieldA' in the place of zeros
Current Output: filtered Outpt: Required Output
id fieldA id fieldA id fieldA
101 54236.0100 101 54236.0100 101 54236.0100
101 4526441.012 101 4526441.012 101 4526441.012
102 0 102 0 102 fieldB-fieldC
103 4479652.2456 103 4479652.2456 103 4479652.2456
103 0 103 0 103 fieldB-fieldC
104 476526 104 476526 104 476526
105 0 105 0 105 fieldB-fieldC
105 0
I have tried various stats commands but was not able to achieve this.
Can you please help me on how to get the required output?
Thank you in advance
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello All,
May I please whether we have a way to achieve the required output?
Current Output: filtered Outpt: Required Output
id fieldA id fieldA id fieldA
101 54236.0100 101 54236.0100 101 54236.0100
101 4526441.012 101 4526441.012 101 4526441.012
102 0 102 0 102 fieldB-fieldC
103 4479652.2456 103 4479652.2456 103 4479652.2456
103 0 103 0 104 476526
104 476526 104 476526 105 fieldB-fieldC
105 0 105 0
105 0
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
Please try this.
| makeresults
| eval _raw = "id, fieldA
101,54236.0100
101,4526441.012
102,0
103,4479652.2456
103,0,0
104,476526
105,0
105,0"
| multikv forceheader=1
| rename COMMENT as "Previous generate sampe, next is the logic"
| dedup id, fieldA
| eval fieldA = if (like(fieldA, "0"), "fieldB-fieldC", fieldA)
| table field id fieldA
And if those fields B and C are numeric then remove " around those in eval + if.
Also like(fieldA.... should replaced to normal numeric comparison if it is numeric instead of character field.
r. Ismo
r. Ismo
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @isoutamo ,
Thank you:) This worked perfectly for my requirement:) The 'fieldB' and 'feildC' are numeric.
Can you please let me know how to modify for the below required Output?
Current Output: filtered Outpt: Required Output
id fieldA id fieldA id fieldA
101 54236.0100 101 54236.0100 101 54236.0100
101 4526441.012 101 4526441.012 101 4526441.012
102 0 102 0 102 fieldB-fieldC
103 4479652.2456 103 4479652.2456 103 4479652.2456
103 0 103 0 104 476526
104 476526 104 476526 105 fieldB-fieldC
105 0 105 0
105 0
Thank you in advance
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
-- your search
| stats values(fieldA) as fieldA values(fieldB) as fieldB values(fieldC) as fieldC by Id
| mvexpand fieldA
| eval fieldA=if(fieldA=0, fieldB-fieldC, fieldA)This only works if fieldB and fieldC are unique for Id i.e. the stats doesn't create a multi-value field
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @ITWhisperer ,
Thank you:) This snippet works correctly. But I have a different required output now.
Current Output: filtered Outpt: Required Output
id fieldA id fieldA id fieldA
101 54236.0100 101 54236.0100 101 54236.0100
101 4526441.012 101 4526441.012 101 4526441.012
102 0 102 0 102 fieldB-fieldC
103 4479652.2456 103 4479652.2456 103 4479652.2456
103 0 103 0 104 476526
104 476526 104 476526 105 fieldB-fieldC
105 0 105 0
105 0
Can you please let me know how to get the Required Output?
Thank you in advance
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
From your very limited sample data it appears that for any given Id, fieldA is either a non-zero number or zero or both, with the assumption that for any given Id, there will be at most one non-zero number and any number of zeroes including none. Also, I assume fieldB and fieldC are unique for any given Id. If this is true, then try this
-- your search
| stats sum(fieldA) as fieldA values(fieldB) as fieldB values(fieldC) as fieldC by Id
| eval fieldA=if(fieldA=0, fieldB-fieldC, fieldA)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @ITWhisperer ,
This sums up the 'feildA' even when we have values greater than zero. But I want them separately.
For example : id 101 in the above example.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
-- your search
| eventstats max(fieldA) as maxA by Id
| eval fieldA=if(fieldA=0, if(maxA=0, fieldB-fieldC, null()), fieldA)
| where isnotnull(fieldA)
| fields - maxA
.conf24 | Registration Open!
ICYMI - Check out the latest releases of Splunk Edge Processor
Introducing the 2024 SplunkTrust!
