Lookup command not functioning properly

[ayushmaan_22]

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.

[DalJeanis · Accepted solution]

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]

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]

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.

[memarshall63]

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