Splunk Search

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

pulkit1997
Engager

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

0 Karma

acfecondo75
Path Finder
| 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!

pulkit1997
Engager

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.

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...