topic Re: Remove the repetative values from row in Splunk Search

Remove the repetative values from row

harshal_chakran — Mon, 28 Sep 2020 15:34:12 GMT

Hi,

I have written a search query which gives the result as below:

my search query is:

sourcetype=csv| search 409,1| rex field=_raw "(\d+,){2}(?\d+)"|rex field=_raw "(\d+,){3}(?\d+)"|rex field=_raw "(\d+,){44}(?\d+)"| stats list(fieldid) as field_ID, list(headid) as head_ID,list(head_value) as Value|eval Parameter = "parameter_1"|eval hex= "112"|eval bell_id= "33"|fields Parameter hex bell_ID field_ID head_ID Value

what I want to do is to merge the rows of field_ID and head_ID, if they repeats and sum the "Value" field and show it as single row instead.
as below:-

Please Help...!!!

Re: Remove the repetative values from row

linu1988 — Fri, 03 Jan 2014 10:24:17 GMT

Hello ,
If you just add a sum command with all the distinct fields you will get the desired result.

sourcetype=csv| search 409,1| rex field=_raw "(d+,){2}(?<fieldid>d+)"|rex field=_raw "(d+,){3}(?<headid>d+)"|rex field=_raw "(d+,){44}(?<head_value>d+)"| stats sum(head_value) as Value by fieldid,headid |eval Parameter = "parameter_1"|eval hex= "112"|eval bell_id= "33"|fields Parameter hex bell_ID fieldid headid Value|stats list(fieldid) as field_ID,list(headid) as HeadID,list(Value) as Value by Parameter,hex,bell_ID

Check if this works for you.

Re: Remove the repetative values from row

harshal_chakran — Mon, 28 Sep 2020 15:34:56 GMT

No, it doesn't show any output.
No result is seen even after shorting the query as:

sourcetype=csv| search 409,1| rex field=_raw "(d+,){2}(?d+)"|rex field=_raw "(d+,){3}(?d+)"|rex field=_raw "(d+,){44}(?d+)"| stats sum(head_value) as Value by fieldid,headid

Re: Remove the repetative values from row

linu1988 — Mon, 28 Sep 2020 15:34:59 GMT

Could you check if the data is extracted from the rex before doing the stats? Could you post the result

sourcetype=csv| search 409,1| rex field=_raw "(d+,){2}(?d+)"|rex field=_raw "(d+,){3}(?d+)"|rex field=_raw "(d+,){44}(?d+)"|table headid,fieldid,head_value

Re: Remove the repetative values from row

lguinn2 — Mon, 06 Jan 2014 06:05:02 GMT

I would use values instead of list in my stats command:

sourcetype=csv 409,1 
| rex field=_raw "(d+,){2}(?<fieldid>d+)"
| rex field=_raw "(d+,){3}(?<headid>d+)"
| rex field=_raw "(d+,){44}(?<head_value>d+)"
| stats values(fieldid) as field_ID, values(headid) as head_ID, sum(head_value) as Value
| eval Parameter = "parameter_1"
| eval hex= "112"
| eval bell_id= "33"
| fields Parameter hex bell_ID field_ID head_ID Value

Also, I don't see a need for the search command - you can put that as part of the first search

But on closer reading, I think this may be what you want:

sourcetype=csv 409,1 
| rex field=_raw "(d+,){2}(?<fieldid>d+)"
| rex field=_raw "(d+,){3}(?<headid>d+)"
| rex field=_raw "(d+,){44}(?<head_value>d+)"
| stats sum(head_value) as sumValue by fieldid headid
| stats list(fieldid) as field_ID, list(headid) as head_ID, list(sumValue) as Value
| eval Parameter = "parameter_1"
| eval hex= "112"
| eval bell_id= "33"
| fields Parameter hex bell_ID field_ID head_ID Value