Splunk Search

How to update existing lookup csv

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

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

Legend

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

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

Legend

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

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

Legend

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