topic How to calculate difference between two rows of a table if another field on row is same for both? in Splunk Search

How to calculate difference between two rows of a table if another field on row is same for both?

pulkit1997 — Mon, 18 May 2020 09:36:00 GMT

Hello Everyone,
I have a table like this:
DVN. Region Name Count
201 SAM Shapes 20010
201 SAM Points 24218
202 SAM Shapes 20102
202 SAM Points 23231

I want to calculate difference between count values for rows whose Name is same but DVN is different. For ex.-- For Shapes name, difference between 3rd row and 1st row should be taken.

My existing query to show this table looks like this:
index=**| rex field=_raw "{.?(?{(.?})).}" | eval trimVal = trim(replace(ps, "\\", "")) | spath input=trimVal | where region=$region$ | where inputFeatureName="Shapes" OR inputFeatureName ="Points"| rename partitionName AS PartitionName, inputFeatureName AS FeatureName, inputFeatureCount AS FeatureCount, dvn AS DVN, region AS Region| where isnotnull(PartitionName) | table PartitionName,DVN,Region, FeatureName, FeatureCount | stats sum(FeatureCount) as Count by DVN,Region,FeatureName | sort Region

Any help is appreciated,
Thanks

Re: How to calculate difference between two rows of a table if another field on row is same for both?

acfecondo75 — Mon, 18 May 2020 14:42:31 GMT

| makeresults 
| eval DVN="201",Region="SAM",Name="Shapes",Count="20010" 
| append 
    [| makeresults 
    | eval DVN="201",Region="SAM",Name="Points",Count="24218"] 
| append 
    [| makeresults 
    | eval DVN="202",Region="SAM",Name="Shapes",Count="20102"] 
| append 
    [| makeresults 
    | eval DVN="202",Region="SAM",Name="Points",Count="23231"] 
| table DVN, Region, Name, Count 
| stats dc(DVN) as dc_DVN values(*) as * by Name 
| search dc_DVN>1
| eval count1=mvindex(Count,0), count2=mvindex(Count,1), diff=abs('count1'-'count2')

Everything before the table command, was just to give me the same result set to work with as you . Then I merged the results that shared a name and took a distinct count of DVN so I could eliminate results that weren't duplicates (although there weren't any in this data set). My search dc_DVN>1 eliminates items that don't have multiple DVNs.

Then my evals were just for doing the math to find the difference. Hopefully this works for your use case or gives you a good starting point!

Re: How to calculate difference between two rows of a table if another field on row is same for both?

pulkit1997 — Tue, 19 May 2020 05:31:24 GMT

Thanks for your response!!
My search query will not return any kind of duplicates, be it DVN or Name. So no worry about duplicates. The eval function is not working for my case because I will

For my use case, I need to calculate degradation in Count for a particular Name and region. This degradation difference will be between count of latest DVN and the DVN before it for a particular Name and region.
And I am having multiple regions and two Names only. So I need to do this for every region.
For Ex. For region= "SAM" and Name="Points", I need to calculate percentage drop/rise in counts i.e. (23231-24218)/24218.

I am providing region as input method which will return a table like above.
Any help is appreciated
Thanks a lot.