Splunk Search
Highlighted

Remove the repetative values from row

Hi,

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

alt text

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}(?<headvalue>\d+)"| stats list(fieldid) as fieldID, list(headid) as headID,list(headvalue) as Value|eval Parameter = "parameter1"|eval hex= "112"|eval bellid= "33"|fields Parameter hex bellID fieldID headID Value

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

alt text

Please Help...!!!

Tags (4)
0 Karma
Highlighted

Re: Remove the repetative values from row

Champion

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.

Highlighted

Re: Remove the repetative values from row

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}(?<headvalue>d+)"| stats sum(head_value) as Value by fieldid,headid

0 Karma
Highlighted

Re: Remove the repetative values from row

Champion

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}(?<headvalue>d+)"|table headid,fieldid,head_value

0 Karma
Highlighted

Re: Remove the repetative values from row

Legend

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
0 Karma