Splunk Search

Lookup command not functioning properly

ayushmaan_22
Explorer

Hi all,

I have the following command:-

| savedsearch issue_with_lookup team="$token$" team_from_roster="$token$" team_roster_count="$token$"
| eval team="$token$"
| eval current_owner = if(current_owner = "","NA", current_owner)
| eval ID = current_owner
| where current_owner != "NA"
| lookup example.csv name as current_owner OUTPUT sys_id as user_sys_id
| table ticket_number, system_id, current_owner, assigneeID, team, reassignment, user_sys_id
| rename ticket_number as ticketName, system_id as ticketID, current_owner as assigneeName, reassignment as reassignment_flag
| search NOT
[search index=abc earliest=-6m latest=now
| dedup ticketName | table ticketName ]

Now the issue is when I run this query, all the fields occur except user_sys_id whose value is definitely present in the lookup and should reflect but is not. This is a scheduled query which runs every 6 mins.

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

Okay, the way you debug something like this is to start one item at a time. Let's say that your lookup and your data supposedly include a team called "Rockets" with an owner called "Jim-Bob".

STEP ONE

| savedsearch issue_with_lookup team="$token$" team_from_roster="$token$" team_roster_count="$token$"
| eval team="$token$"
| where current_owner = "Jim-Bob"

Is the record there? Then proceed. If not, then figure out if the field name is wrong or whatever.

STEP TWO

| savedsearch issue_with_lookup team="$token$" team_from_roster="$token$" team_roster_count="$token$"
| eval team="$token$"
| where current_owner = "Jim-Bob"
| lookup example.csv name as current_owner OUTPUT sys_id as user_sys_id

Has the user_sys_id been added? if not, then check the exact spelling, or run this alternate way ...

| savedsearch issue_with_lookup team="$token$" team_from_roster="$token$" team_roster_count="$token$"
| eval team="$token$"
| where current_owner = "Jim-Bob"

| append [| inputlookup example.csv
      | table  name sys_id 
      | rename name as current_owner sys_id as user_sys_id
      | eval rectype = "lookup"
      ]
 | eventstats values(user_sys_id) as user_sys_id by current_owner
 | sort 0 current_owner

Look down in the order where Jim-Bob is supposed to be. Is it there now? is it on a single row? if there are two rows there, then there may be special characters in your lookup.

If you get this far and haven't solved it, or get any odd results in the above, then let us know and we can help debug further.

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

Okay, the way you debug something like this is to start one item at a time. Let's say that your lookup and your data supposedly include a team called "Rockets" with an owner called "Jim-Bob".

STEP ONE

| savedsearch issue_with_lookup team="$token$" team_from_roster="$token$" team_roster_count="$token$"
| eval team="$token$"
| where current_owner = "Jim-Bob"

Is the record there? Then proceed. If not, then figure out if the field name is wrong or whatever.

STEP TWO

| savedsearch issue_with_lookup team="$token$" team_from_roster="$token$" team_roster_count="$token$"
| eval team="$token$"
| where current_owner = "Jim-Bob"
| lookup example.csv name as current_owner OUTPUT sys_id as user_sys_id

Has the user_sys_id been added? if not, then check the exact spelling, or run this alternate way ...

| savedsearch issue_with_lookup team="$token$" team_from_roster="$token$" team_roster_count="$token$"
| eval team="$token$"
| where current_owner = "Jim-Bob"

| append [| inputlookup example.csv
      | table  name sys_id 
      | rename name as current_owner sys_id as user_sys_id
      | eval rectype = "lookup"
      ]
 | eventstats values(user_sys_id) as user_sys_id by current_owner
 | sort 0 current_owner

Look down in the order where Jim-Bob is supposed to be. Is it there now? is it on a single row? if there are two rows there, then there may be special characters in your lookup.

If you get this far and haven't solved it, or get any odd results in the above, then let us know and we can help debug further.

ayushmaan_22
Explorer

Hi, actually there wasn't an issue with the lookup, there was another file with which the name is matched with this lookup (defined in the savedsearch at the 1st line) and that name was inserted wrong( a "space error") .

Thank you for your response. I will definitely try this method as well as it looks more organised.

DalJeanis
SplunkTrust
SplunkTrust

Great, glad you got what you needed.

With this kind of issue, you always have to just keep cutting the problem in half and finding which half the error is in. Eventually, you run out of things that could be it.

0 Karma

memarshall63
Communicator

Are you saying that the field is blank or that the column is gone in your results?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...