Splunk Search

How to join events with lookuptable based on numerical values?


I want to join these two types of data:

  1. The following events have the recorded value for each step in a test.

    TestNum        StepNum       recordedValue  

    AFKE-232322 501 32.254
    AFKE-232322 508 1.0223
    AFKE-232322 842 1.0582
    AFKE-232322 805 -24.25
    AFKE-232322 503 22.0
    AFKE-232322 504 100.05
    The following table (lookuptable) has the instrument name used on each block of the test. (A test has multiple blocks, each block has multiple steps, and each block use an instrument.) The Instrument field has string values like "DMM_23s3".

    TestNum Block InstrumentUsed
    AFKE-232322 100 inst_1
    AFKE-232322 200 inst_2
    AFKE-232322 300 inst_3
    AFKE-232322 400 inst_4
    At the end, I would like to have something like this:

    TestNum StepNum recordedValue Instrument
    AFKE-232322 501 32.254 Inst_5
    AFKE-232322 508 1.0223 inst_5
    AFKE-232322 842 1.0582 inst_8
    AFKE-232322 805 -24.25 inst_8
    AFKE-232322 503 22.0 inst_5
    AFKE-232322 504 100.05 ints_5
    Note that the block number is always the first step on the block and it could be any positive integer. To find the block of a step, I need to look for the two blocks before and after a step.

Examples of blocks and their steps:
Block 502 { 503, 504, 550, ... ,634}
Block 650 { 651, 652, 653}
Block 655 { 656, 657, 658}

step 634 is part of block 502
step 652 is part of Block 650
step 657 is part of Block 655

0 Karma
Get Updates on the Splunk Community!

Data Preparation Made Easy: SPL2 for Edge Processor

By now, you may have heard the exciting news that Edge Processor, the easy-to-use Splunk data preparation tool ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Tips & Tricks When Using Ingest Actions

Tune in to learn about:Large scale architecture when using Ingest ActionsRegEx performance considerations ...