Splunk Search

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

darphboubou
Explorer

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

Labels (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

darphboubou
Explorer

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)

 

 

splookup.png

 

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)

 

resquest.png

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

Any other Idea ?

Regards

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

darphboubou
Explorer

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)

lookup2.png

And as you can see this field exist in index.

 

index2.png

 

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

 

 

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

darphboubou
Explorer

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

request2.png

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

darphboubou
Explorer

thanks for all @gcusello ,

 

The solution that you gave me works at the perfection.

 

Have a good day

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @darphboubou ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

darphboubou
Explorer

done :).

 

 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...