How to add field from a lookup in an index search and displaying the result

[darphboubou]

Hello,

I have a lookup on which we have two columns, one with the computer name and the other with the OS version.

When I do a search in the windows index via splunk (event logs) I want to use this lookup to add the OS version in the result

In fact, I want to display the information in my lookup in the result field of my index search.

Greetings

[gcusello]

Hi @darphboubou,

yes, it's possible using the lookup command (https://docs.splunk.com/Documentation/Splunk/9.0.0/SearchReference/Lookup).

You need only to find the field in your search matching the computername field in the lookup, so if the fieldname in search is e.g. host and the field in lookup is computername, you could run something like this:

index=your_index
| lookup your_lookup computername AS host OUTPUT OS
| table host OS

 Ciao.

Giuseppe

[darphboubou]

Hi Giuseppe,

Firstable thanks for your quick answer, I tried oyour solution but something works wrong.

Below the column in my lookup the two what I a interrested for (there is some more column in reality in my lookup)

So I have a Server column and an OS column.

I tried to applicate what you explained to me (see print screen below) by renaming Server by Workstation_Name (the name of the fileld in the index search)

The return show a lot of workstations that doesn't belong to my lookup and the OS field stay empty.

Any other Idea ?

Regards

[gcusello]

Hi @darphboubou,

as you can see in the above link the syntax for the lookup command is:

| inputlookup lookup_name key_field_in_lookup AS key_field_in_search OUTPUT fields_from_lookup

if you don't see any value in the lookup fields,this means that there isn't any match between the key fields in lookup and search.

So, check at first the field names and then the field values.

Ciao.

Giuseppe

[darphboubou]

hi @gcusello ,

As you can see in the print screen below I have the column Server (key_field_in_lookup) an Workstation_Name (key_field_in_search)

And as you can see this field exist in index.

I don't find out where I'm wrong.

[gcusello]

Hi @darphboubou,

check if there's a match between Server and Workstation_Name values.

maybe there's some little difference.

Also try to converta both of them in lowercase or uppercase.

Ciao.

Giuseppe

[darphboubou]

My mistake, the request working. but the issue is that displaying the computer that are not in my lookupt too (see my print screen)

The two ones frame in green are in my lookup (and it's ok to display them) but the one named -ppd is not in my look up and i don't want it dispayed in the search result

here my search request

index=windows EventCode=4624
| lookup damtest2.csv Server AS Workstation_Name OUTPUT os
| table Workstation_Name os Package_Name__NTLM_only_ | dedup Workstation_Name | sort Workstation_Name

[gcusello · Accepted solution]

Hi @darphboubou,

you have two solutions: filter at the beggining (I hint because it's quicker!) or at the end.

at the beginning:

index=windows EventCode=4624 [ | inputlookup damtest2.csv | rename Server AS Workstation_Name | fields Workstation_Name ]
| lookup damtest2.csv Server AS Workstation_Name OUTPUT os
| table Workstation_Name os Package_Name__NTLM_only_ 
| dedup Workstation_Name 
| sort Workstation_Name

filtering at he end

index=windows EventCode=4624
| lookup damtest2.csv Server AS Workstation_Name OUTPUT os
| search os=*
| table Workstation_Name os Package_Name__NTLM_only_ 
| dedup Workstation_Name 
| sort Workstation_N

Ciao.

Giuseppe

[darphboubou]

thanks for all @gcusello ,

The solution that you gave me works at the perfection.

Have a good day

[gcusello]

Hi @darphboubou ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

[darphboubou]

done :).