Splunk Search
Highlighted

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

New Member

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?

0 Karma
Highlighted

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

Path Finder
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

0 Karma
Highlighted

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

New Member

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.

0 Karma
Highlighted

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

Path Finder

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

0 Karma