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!
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"
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 )