topic Re: Map/Join Search query with lookup, when field in null in Splunk Search

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

AKG1_old1 — Fri, 09 Mar 2018 17:02:57 GMT

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

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

elliotproebstel — Fri, 09 Mar 2018 17:17:28 GMT

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

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

AKG1_old1 — Fri, 09 Mar 2018 17:55:47 GMT

Thanks for Reply, but above solution is not working.
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.

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

elliotproebstel — Fri, 09 Mar 2018 18:13:15 GMT

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?

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

AKG1_old1 — Fri, 09 Mar 2018 18:21:04 GMT

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

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

elliotproebstel — Fri, 09 Mar 2018 18:42:52 GMT

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.

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

AKG1_old1 — Mon, 12 Mar 2018 11:31:29 GMT

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

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

AKG1_old1 — Thu, 15 Mar 2018 09:22:47 GMT

Thank you !! slight changes worked for me.

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

elliotproebstel — Thu, 15 Mar 2018 13:44:07 GMT

Glad to hear it!