Splunk Search

Mathematical calculations on rows based on grouping

imran1386
New Member

This is my table that I have extracted with the help of this query:

index=auto_adv_txn_preprod source=cap ( alfaws OR IODS*DAOImpl OR prism OR service- ) ADV_AAOSF07
| rex "^(?:[^ \n]* ){3}(?P[^ ]+)(?:[^ \n]* ){5}(?P[^ ]+)(?:[^ \n]* ){7}(?P\d+)\s+-\s+(?P\d+)"
| eval ServiceCall1 = replace(ServiceCall, "/\d+,*","/{id}")
| eval ServiceCall = mvindex(split(ServiceCall1, ";"),0)
| eval Functionality = replace(TID, "_\d+","")
| table TID, ServiceCall, Functionality, ResTime

Here is the initial Output :

alt text

This is the Output I am expecting:

alt text

The Time Difference Column will have the Time difference of URLType1 with Sum of all other URLTypes for any particular TID and Functionality. Any kind of help is appreciated.

Tags (2)
0 Karma

TISKAR
Builder

@imran1386, can you try this please:

if URLType1 take always the first position:

 <YourBaseSearch> | streamstats count by Functionality | eval URL=if(count=1,URL,null) | filldown URL | stats sum(Time) as somme max(Time) as max by Functionality URL | eval Time=2*max-somme | table Functionality URL Time 

if URLType1 have always the max value of Time Try this:

<YourBaseSearch>| eventstats max(Time) as max by Functionality | eval URL=if(max=Time,URL,null) | filldown URL| stats sum(Time) as somme max(Time) as max by Functionality URL | eval Time=2*max-somme | table Functionality URL Time

Happy Splunking

0 Karma

TISKAR
Builder

@imran1386, can you try this please:

| eval URL="URLType1"| stats sum(Time) as somme max(Time) as max by Functionality URL | eval Time=2*max-somme | table Functionality URL Time

I test it, it work for me

0 Karma

imran1386
New Member

Thanks TISKAR for your time on this.
I have commented on Somesoni2's post the missing piece. Can you please revisit this and try to answer.

0 Karma

TISKAR
Builder

@imran1386, I add the answer, can you test it please

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

your current search giving fields TID Functionality URL Time
| eval Time1=if(URL="URLType1",Time,0)
| eval Time2=if(URL!="URLType1",Time,0)
| eval URL="URLType1"
| stats sum(Time1) as Time1 sum(Time2) as Time2 by Functionality URL
| eval Time=Time1-Time2 | table Functionality URL Time
0 Karma

imran1386
New Member

Thanks Somesoni2 for quick response.
The problem is "URLType1" is dynamics value and is the primary value from which rest of the URLs associated with the same TID(Unique Transaction ID) will be subtracted for any given functionality.

I have 40-50 different functionalities and each has different "URLType1" values.

The reason I have to calculate Time difference based off TID is because at the very end I need to calculate 90percentile grouped by functionality.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Your dynamic nature of URL field can be handled by making an assumption that your base search gives the "URLType1" as first value in the event, like your sample data is showing. If that can be assumed try this:

your current search giving fields TID Functionality URL Time
| streamstats count as sno by TID Functionality
 | eval Time1=if(sno=1,Time,0)
 | eval Time2=if(sno>1,Time,0)
 | stats sum(Time1) as Time1 sum(Time2) as Time2 first(URL) as URL by Functionality
 | eval Time=Time1-Time2 | table Functionality URL Time
0 Karma

FrankVl
Ultra Champion

That doesn't change the conceptual solution, you then just need to come up with a different condition for the if statements on lines 2 and 3 of @somesoni2's solution.

And if you want any help with that, you'll have to provide more insight into what those URL values are and how to find the 'URLType1'.

Alternatively, if the Time for 'URLType1' is always the sum of the others + something extra. You could filter by finding the largest time:

your current search giving fields TID Functionality URL Time
 | eventstats max(Time) as max_time by Functionality
 | eval Time1=if(Time=max_time,Time,0)
 | eval Time2=if(Time!=max_time,Time,0)
 | eval URL="URLType1"
 | stats sum(Time1) as Time1 sum(Time2) as Time2 by Functionality URL
 | eval Time=Time1-Time2 
 | table Functionality URL Time

If the URLType1 is always the first url type that comes in chronologically, then the solution using streamstats count... from @TISKAR would be a better alternative.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...