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 .
Like this (assuming that everything is using
Workstation_Name and not
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
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?
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 .
we have to add active=yes because when we recreate the lookup with two components:
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:
because with the above search these two kind of hosts are excluded from the results and so from the lookup.
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 ?
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