Splunk Search

How to update existing lookup csv

vikas_gopal
Builder

I am sure someone must have achieved this
I have an existing lookup table .It has 4 columns and it has values like below for 20 servers , I want to update existing lookup table but only those workstation_name where active is yes . I want to achieve this via

workstation_name    ip          type        active
abc              x.x.x.x       web1         yes
bcd              y.y.y.y       web2         no
bla                            web3         yes
ttd                             web4         yes

So far this is what I did
1) Get the ip address from index , map it with lookup table where active is yes

index=abc |search [|inputlookup 20_servers where active=yes|fields Workstation_Name |rename Workstation_Name as dest_nt_host] |fields dest_nt_host,dest_ip|rename dest_nt_host as "Workstation_Name", dest_ip as ip |table Workstation_Name ,ip |dedup Workstation_Name

This gives me results as expected like below

 workstation_name    ip        
    abc              x.x.x.x       
    bla              z.z.z.z            
    ttd              t.t.t.t

From here onward how I can append/attach lookup remaining two fields/columns that is type and active and update the existing lookup with |outputlookup

I hope I was clear with my requirement if not please let me know I can explain further .

Regards
VG

0 Karma

woodcock
Esteemed Legend

Like this (assuming that everything is using Workstation_Name and not workstation_name😞

index=abc AND [|inputlookup 20_servers where active=yes|fields Workstation_Name |rename Workstation_Name as dest_nt_host]
| fields dest_nt_host dest_ip
| dedup dest_nt_host
| rename dest_nt_host AS "Workstation_Name", dest_ip AS ip
| table Workstation_Name ip
| eval active = "yes"
| inputlookup append=t 20_servers
| stats first(type) AS type first(ip) AS ip first(active) AS active BY Workstation_Name
| outputlookup 20_servers
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi vikas_gopal,
your process is correct, you have to add a active=yes to the output of your search and add the other lines:
Something like this:

index=abc [ | inputlookup 20_servers where active=yes | rename Workstation_Name AS dest_nt_host | fields Workstation_Name ] 
| rename dest_nt_host AS Workstation_Name  dest_ip AS ip 
| dedup Workstation_Name
| eval active="yes"
| append [ | inputlookup 20_servers where active=no ]
| table workstation_name ip type active
| outputlookup 20_servers 

Only one question: how do you manage other hosts that you could find in the main search that aren't in the lookup?

Ciao.
Giuseppe

0 Karma

vikas_gopal
Builder

Thanks for the reply , but I am still not clear what is the role of |eval active="yes" in your query . Actually I already have this column in lookup table with values like yes or no . So I just t need to update IP against only those wrkstation_names where active =yes , query should ignore where active=no, when I ran above query I saw splunk convert active to yes for all .

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi vikas_gopal,
we have to add active=yes because when we recreate the lookup with two components:

  • the results of the search that contains all the host present both in search and in lookup with active=yes;
  • all the hosts of lookup with active=no.

But if you take the values from the index, you haven't the value active=yes so you have to add.

Only twoe additional question to understand if I completely answered to you question:

  • is it possible that some hosts in lookup with active=yes aren't present in index?
  • can you have in search results that aren't in lookup (both active=yes or no)?

because with the above search these two kind of hosts are excluded from the results and so from the lookup.

Ciao.
Giuseppe

0 Karma

vikas_gopal
Builder

Well for a given time range both your questions can be true , like if I search for last 24 hours in an index there might be servers from lookup that did not show up in index however they did in last 48 hours , so can we achieve something like query will ignore those machines whos IP is being updated ?

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi vikas_gopal,
if it's possible that there are servers that are in lookup and not in search and in search an not in lookup, you could use a little different search:

index=abc 
| rename dest_nt_host AS Workstation_Name
| dedup Workstation_Name
| append [ | inputlookup 20_servers ] 
| stats values(dest_ip) AS dest_ip values(ip) AS ip values(active) AS active values(type) AS type BY Workstation_Name
| eval active=if(isnul(active),"not defined",active), ip=coalesce(dest_ip,ip)
| table workstation_name ip type active
| outputlookup 20_servers 

Ciao.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...