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
| 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.
@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
@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
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.
@imran1386, I add the answer, can you test it please
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
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.
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
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.