Sankey Diagram - Custom Visualization: One event in multiple transactions

[sosapes]

I have events by this pattern:
time id state

e.g.:
2017-03-10 10:30:00 123 A
2017-03-10 10:30:01 123 B
2017-03-10 10:30:02 123 C
2017-03-10 10:30:03 123 D

What i can get with "| transaction id"
A B duration
C D duration

For Sankey diagram need to transform them into pattern

e.g.:
A B duration
B C duration
C D duration

In shortcut I need one event to be in two transactions. Is there some trick to achieve this?

[Richfez]

The streamstats command should do what you need here.

Specifically, you'll want to use it with a window=2 and use the first and last stats function to pull out your times ( _time) or values to create a duration.

You'll want something like

... my search here ...
| streamstats window=2 first(_time) AS first_time, last(_time) AS last_time, first(<fieldname>) as first_val, last(<fieldname>) as last_val
| eval duration = first_time - last_time

Where you replace with whatever gave you the field with values "A", "B", and so on.

Here's a run-anywhere as an example. In it I create a fake "nums" that I use - that's the first 4 lines. You'll want to start with the streamstats right after you have your data.

| makeresults 
| eval nums="3 7 8 12 15 19" 
| makemv delim=" " nums 
| mvexpand nums 
| streamstats window=2 last(nums) AS oldest first(nums) AS newest 
| eval duration=oldest-newest

The resulting output will hopefully be easily adaptable to your own needs.

[sosapes]

Thank you it helped find me a way!

[Richfez]

Excellent!

It would be very useful to those who stumble across this answer later if you could provide the search you ended up with here and mark the question as accepted.

Second best (and good enough) is just clicking "Accept" so everyone knows there's a valid answer!

Thanks! And glad we could help!

-Rich