Hello,
I am trying to Join/map Search query result with lookup table. I am close to perfect query, Just not be able to handle one case (When Type=NULL in lookup table)
Search Query Output and Lookup Table
Requirement:
To check in Lookup and match Context_Command(lookup) with Context+Command(query) AND TYPE (Query) with Type(Lookup). If both condition true it add all columns from lookup to search results.
I am able to merge search query and lookup except one condition. If Type=”” in lookup, It’s not doing anything. My requirement is if Type=”” in lookup, it still append values for Tags, Type, Threshold etc. to the search query output from lookup.
That means Last row of search query output should show data from last row in lookup. ( Alert = TRUE, Threshhold=2,Tags=TAG3 )
My Query:
<Base search query to produce data without lookup>
| lookup <Lookup Name> Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert
Note: If Type is not defined in lookup then its generic and should check only Context+Command and add fields to all matching events.
Could anyone please suggest any workaround ? Thanks for help
Thanks
I think you will get the results you want if you use fillnull
immediately before the lookup, because I believe the issue is that the field is NULL in the events returned from the query. Try this:
<base search>
| fillnull value="" TYPE
| lookup <Lookup Name> Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert
I think you will get the results you want if you use fillnull
immediately before the lookup, because I believe the issue is that the field is NULL in the events returned from the query. Try this:
<base search>
| fillnull value="" TYPE
| lookup <Lookup Name> Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert
Condition : If Type is not defined in lookup. In this case it should match only Context_Command(lookup) with Context+Command(search query) and append columns to all the matching events.
So if the query result has a match in the lookup file when comparing both fields, you want that result. And if the query result does not have a match when comparing both fields, you want to match on the lookup file event that has a null value for Type - yes?
yes !! Thats what I am looking for. Its like if user doen't specify Type in lookup, that means it's global and should apply to all matching fields based con Context+Command.
If empty doesn't work, we can assign some value instead of blank. Such as "*" (Type="*" in lookup).
Well, here's the approach I'm envisioning:
Step One
You run the search as you already have it:
<Base search query to produce data without lookup>
| lookup <Lookup Name> Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert
At this point, you'll have some events that found a match, and those lines will have values populated into the fields Tags, CC_Description, Threshold, and Alert. But you'll also have some events that contain a value for "Context+Command" and TYPE but not the others.
STEP TWO
Use an appendpipe command to deal with those events that aren't fully populated:
| appendpipe
[ | where isnull(Tags)
| rename TYPE AS BACKUP_TYPE
| eval TYPE=""
| lookup <Lookup Name> Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert
| rename BACKUP_TYPE AS TYPE ]
STEP THREE
Then filter out the events that weren't fully populated before step two, because they will be duplicated by the events returned from step two.
| where isnotnull(Tags)
But this will only work if you expect every event entry to wind up getting fully populated. If that's not the case, you may need to use some dedup or stats magic.
Thank you!! I'll give it a try and update you.
Thank you !! slight changes worked for me.
Glad to hear it!