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).
we need to tie card_id with plate numbers. To achieve this, we see 2 options:
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.
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?
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
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.
OK got it. Let me work on this and get back to you.