Combining 3 data sources with rolling ID

[ManfredGrill]

Hi,

various tables from a database are read by Splunk. I need to combine fields from all 3 datasources. The ID-fields contain the same value, but is rolled over after a fixed number of entries. This happens approx. every 3 month. The _time values are close together (within seconds or minutes), but they are not the same.

datasource dsa
_time, ID-A, field-a1, field-a2

datasource dsb
_time, ID-B, field-b1, field-b2

datasource dsc
_time, ID-C, field-c1, field-c2

Any suggestions on how to achive this?

regards

Manfred

[ITWhisperer]

You could track when the rollover occurs and increment a generation count and use that to uniquely identify the ids

(datasource="dsa" OR datasource="dsb" OR datasource="dsc")
| eval commonid=coalesce(ID-A,ID-B,ID-C)
| sort 0 _time
| streamstats range(commonid) as range window=2 global=f by datasource
| eval nextgen=if(range>1,1,0)
| streamstats sum(nextgen) as generation by datasource
| stats values(*) as * by generation commonid

One issue with this approach is if one of your datasources has an id at the beginning of your time window that rolls over immediately and the other datasources had already rolled over.

[ManfredGrill]

Thank you very much for your help.

The database is not allowed to be changed, no chance to add a generation column.

But coalesce helps to get further

(datasource="dsa" OR datasource="dsb" OR datasource="dsc")
| eval commonid=coalesce(ID-A,ID-B,ID-C)
| transaction commonid maxspan=1h

for most entries this returns the correct data, but a some are missing and I don't understand why.

e.g.

(datasource="dsa" OR datasource="dsb" OR datasource="dsc")
| eval commonid=coalesce(ID-A,ID-B,ID-C)
| table commonid, ID-A, ID-B, ID-C | search commonid="12345"

running this query for values of the last 30 days returns 3 rows, one from each datasource. These results are fine.

commonid	ID-A	ID-B	ID-C
12345	12345
12345		12345
12345			12345

but

(datasource="dsa" OR datasource="dsb" OR datasource="dsc")
| eval commonid=coalesce(ID-A,ID-B,ID-C)
| table commonid, ID-A, ID-B, ID-C | search commonid="12345" 
| transaction commonid maxspan=1h

returns nothing. All timestamps are within a few seconds. Bigger values for maxspan make no difference.

Any idea?