How to use a lookup without exact match (between values)

[rheylen]

Hi all,

I have created a query that uses a couple of input lookups.

| inputlookup CSC_value | lookup CSC_posture_name _key as csc_posture_name_key output name as posture_name | lookup CSC_tree _key as csc_tree_key output name as tree_name

Output example:

    creationdate     csc_posture_name_key    csc_tree_key    posture_name      tree_name    value

    1510132678       59e9ec538cb36149        59e9e6728cb     Policy Defined    test1        19
    1510132888       59e9ec538cb36149        aee363bb0b1     Policy Impleme    test2        43
    1510132888       23a4cb4254bba123        aee363bb0b1     Policy Impleme    test3        49

The result I get is ok, but the next step is to do a lookup for each result from above query into another lookup (CSC_posture_value), where I have to match exact on the csc_posture_name_key (not the problem) but also to find the row where the "value" from above query is between min and max and to return the name.

Here is how the CSC_posture_value looks like

csc_posture_name_key    name          min      max

59e9ec538cb36149        A Low         0        19
59e9ec538cb36149        A Medium      20       39
59e9ec538cb36149        A OK          40       59
59e9ec538cb36149        A High        60       79
59e9ec538cb36149        A Critical    80       100
23a4cb4254bba123        B Low         0        19
23a4cb4254bba123        B Medium      20       39
23a4cb4254bba123        B OK          40       59
23a4cb4254bba123        B High        60       79
23a4cb4254bba123        B Critical    80       100

Next query works to match on the csc_posture_name_key, but It returns all names for that csc_posture_name_key.

| inputlookup CSC_value | lookup CSC_posture_name _key as csc_posture_name_key output name as posture_name | lookup CSC_tree _key as csc_tree_key output name as tree_name | lookup CSC_posture_value csc_posture_name_key output name

I want it to return only the name that matches the csc_posture_name_key AND where the value is between min and max.
I tried this, but that's not working, but it is to give you an idea what I need.

| inputlookup CSC_value | lookup CSC_posture_name _key as csc_posture_name_key output name as posture_name | lookup CSC_tree _key as csc_tree_key output name as tree_name | lookup CSC_posture_value csc_posture_name_key, value>=min, value<= max output name

Any suggestions?
Thanks in advance!

[elliotproebstel]

The only thing I can think of is rather slow to run, especially if you are looking up a lot of csc_posture_name_key values, but it ought to work. (If anyone has a working solution that doesn't use map, then go with that!) But try this:

| inputlookup CSC_value 
| lookup CSC_posture_name _key as csc_posture_name_key output name as posture_name 
| lookup CSC_tree _key as csc_tree_key output name as tree_name 
| map search="| inputlookup CSC_posture_value where csc_posture_name_key=$csc_posture_name_key$ 
              | where min<=$value$ AND $value$<=max"

[koshyk]

you could do after your Search?

 | inputlookup CSC_value | lookup CSC_posture_name _key as csc_posture_name_key output name as posture_name | lookup CSC_tree _key as csc_tree_key output name as tree_name | lookup CSC_posture_value csc_posture_name_key output value name| search (value>=min AND value<= max )