Calculate durations within a transaction

[Jeremiah]

I have a transaction that crosses multiple applications. I have a eventguid that I use with the transaction command to calculate the duration of the transaction. But now, I need to be able to calculate the durations within each of the applications as well, and build a table with that information. For example:

Event1 app=1 eventguid=123 some message text
Event2 app=1 eventguid=123 some more text
Event3 app=2 eventguid=123 other stuff
Event4 app=2 eventguid=123 more stuff

and so on..

And then the output would look something like this, or similar:

EventGUID app1_duration app2_duration total_duration
123       10            20            30
456       15            7             22

I've thought about first creating transactions based on the app and the eventguid, which would allow me to calculate the individual durations within each app, then do something like | stats sum(duration) by EventGUID which would give me the total. Any other thoughts?

[jonuwz]

Given this input :

_time,app,eventguid,text
1346449414,1,123,some more text
1346449424,1,456,other stuff
1346449434,2,123,some more text
1346449436,2,456,other stuff
1346449436,3,123,some more text
1346449439,3,456,other stuff

This

 | inputlookup example.csv 
 | streamstats global=f current=f window=1 first(_time) as ptime first(app) as papp by eventguid 
 | where isnotnull(ptime) 
 | eval dur=_time-ptime 
 | eval papp="app".papp."_duration"
 | chart first(dur) over eventguid by papp

Produces this

eventguid   app1_duration   app2_duration
123         20                 2
456         12                 3

I'm making a few assumtions

1. you have to use the time of the event to workout the duration as the transaction moves through the apps.
   
2. The event is logged when the transaction hits the app, not when it leaves

In this case, you can only work out 2 durations if there's 3 apps, and the time between the events for app1 and app2 is the duration spent in app1

Adding totals is simple (or at least it is if your eventguid isn't numeric !)

Just add

| addtotals

or if your eventguids really are numeric

| eval eventguid=" ".eventguid | addtotals | eval eventguid=ltrim(eventguid)