Splunk Search

Map/Join Search query with lookup, when field in null

agoyal
Builder

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
alt text

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.

alt text

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

1 Solution

elliotproebstel
Champion

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

View solution in original post

0 Karma

elliotproebstel
Champion

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

View solution in original post

0 Karma

agoyal
Builder
  1. Thanks for Reply, but above solution is not working.
  2. Sorry it was not clear before but even TYPE="some other values " it should add fields from lookup.

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.

elliotproebstel
Champion

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?

agoyal
Builder

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).

elliotproebstel
Champion

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.

0 Karma

agoyal
Builder

Thank you!! I'll give it a try and update you.

agoyal
Builder

Thank you !! slight changes worked for me.

elliotproebstel
Champion

Glad to hear it!

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!