topic Re: Mathematical calculations on rows based on grouping in Splunk Search

Mathematical calculations on rows based on grouping

imran1386 — Tue, 29 Sep 2020 19:16:41 GMT

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 :

This is the Output I am expecting:

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.

Re: Mathematical calculations on rows based on grouping

somesoni2 — Thu, 26 Apr 2018 20:45:47 GMT

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

Re: Mathematical calculations on rows based on grouping

TISKAR — Thu, 26 Apr 2018 21:33:45 GMT

@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

Re: Mathematical calculations on rows based on grouping

imran1386 — Thu, 26 Apr 2018 22:51:46 GMT

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.

Re: Mathematical calculations on rows based on grouping

imran1386 — Thu, 26 Apr 2018 22:56:47 GMT

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.

Re: Mathematical calculations on rows based on grouping

TISKAR — Fri, 27 Apr 2018 08:51:17 GMT

@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

Re: Mathematical calculations on rows based on grouping

FrankVl — Fri, 27 Apr 2018 09:18:41 GMT

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.

Re: Mathematical calculations on rows based on grouping

TISKAR — Fri, 27 Apr 2018 10:11:22 GMT

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

Re: Mathematical calculations on rows based on grouping

somesoni2 — Fri, 27 Apr 2018 14:47:08 GMT

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