calculate time between every events

[ryastrebov]

Hello splunkers!

I need your help. I analyze transport accessibility between two groups of city district. First know as Departure Area (DA), second know as Arrival Area (AA). Users move from of DA to AA. Each area is divided into multiple districts. For each user in each district defined time of arrival to the district and time of departure from the district.
I need to calculate the time for each user between his time of departure from each district from DA the time of arrival into each district from AA. For example:

user_id   time_arrival  time_departure   district   AA or DA
user1     1391400000    1391410800       12         DA
user2     1391400000    1391418000       15         DA
user3     1391418000    1391436000       42         DA
user2     1391418500    1391428000       14         DA
user3     1391446800    1391486400       18         AA
user3     1391487000    1391489300       19         AA
user1     1391745600    1391774400       18         AA
user2     1391832000    1391835600       41         AA

I need calculate time:
- for user1 between district 18 (AA) and district 12 (DA)
- for user2 between district 41 (AA) and district 14 (DA) and between district 41 (DA) and district 15 (DA)
- for user3 between district 19 (AA) and district 42(DA) and between district 18 (AA) and district 42 (DA)

How can I do this? I would be very grateful for the help!

Best regards,
Roman

[sideview · Accepted solution]

Ordinarily this sort of thing is a job for the streamstats command.

However I'm not quite sure I understand the interaction of DA vs AA. It seems almost like they can be ignored. Here's a search that basically ignores the distinction between AA and DA, but gives you transit times for the various rows.

<your searchterms> | sort 0 time_arrival | streamstats current=f last(time_arrival) as time_departure last(district) as departed_from by user_id | eval time_travelled=time_arrival - time_departure 

The streamstats docs can help you understand how this search is doing what it does.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Streamstats

[sideview]

I'm afraid I'm still having trouble. Could you add to your question what the exact results should be for users 1 2 and 3?

[ryastrebov]

Hello sideview!

Thank for your help!

I need to determine transport accessibility for each district of AA from all known district of DA. In other words, not only for last district from DA. It is possible?