How do you link transactions with other sourcetypes based on timestamp?

[DenysB]

Splunk fellows your help is needed,

In our project (license plate recognition on gas stations) - we have 2 sourcetypes.

Sourcetype= plate_recognition
Where events look like:

1. 15:00, direction=in, plate_number=xx11
2. 15:01, direction=in, plate_number=xx22
3. 15:09, direction=out, plate_number=xx11
4. 15:10, direction=in, plate_number=xx33
5. 15:12, direction=out, plate_number=xx22
6. 15:15, direction=out, plate_number=xx33
…

Event Description:
15:00 car with plate number xx11 has entered the gas station, the same car has left by 15:09.

We have used command Transaction:
| transaction plate_number startwith=”direction=in” endwith=”direction=out”
And received following results:

1. 15:00, plate_number=xx11, duration=9minutes
2. 15:01, plate_number=xx22, duration=11minutes
3. 15:10, plate_number=xx33, duration=5minutes
...

Sourcetype=payment
Events in this sourcetype represent payment transactions on the gas stations.

1. 15:03, card_id=AAAA
2. 15:07, card_id=BBBB
3. 15:11, card_id=CCCC
…

Event Description:
On 15:03 payment has proceeded with loyalty card AAAA (we do not have information about plate number owning this card).

OUR TASK

we need to tie card_id with plate numbers. To achieve this, we see 2 options:

• Create a statistics table using timestamps for each card_id.

table:

card_id | plate_number
        |
AAAA    |    xx11
        |    xx22
----------------------
BBBB    |    xx11
        |    xx22
----------------------
CCCC    |    xx22
        |    xx33
----------------------

From the data, we can see that when card AAAA was used, at that time cars xx11 and xx22 have been at the gas station, therefore we link those 2 with that card_id.

• For each plate number

Table:

plate_number | card_id
             |
xx11         |  AAAA
             |  BBBB
----------------------
xx22         |  AAAA
             |  BBBB
             |  CCCC
----------------------
CCCC         |  CCCC
----------------------

From the data, we can see that when car xx11 was at the station, two cards have been used: AAAA and BBBB, therefore, we link these cards to that car.

Do you have an idea how to get those tables?

[Justinboucher0]

sourcetype= plate_recognition | transaction plate_number startwith=”direction=in” endwith=”direction=out” | join time [ search sourcetype=payment] | stats values(plate_number) by card_id

Assuming you extracted that time field as a field and aren't using _time. If the times don't quite line up then you can use the streamstats command to join on the time field.

EDIT: Whoops. Edited to join on the correct field

[DenysB]

Hi Justin,

As I understand, when we use join command, only events that match are joined. So in our case, values of "time" field must be the same in both sourcetype, but it isn't.

[Justinboucher0]

OK got it. Let me work on this and get back to you.