Splunk Search

Combining 3 data sources with rolling ID

ManfredGrill
Explorer

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

Labels (3)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

 

0 Karma

ManfredGrill
Explorer

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.

commonidID-AID-BID-C
1234512345  
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?

Tags (1)
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...