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
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
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
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 .
Hi vikas_gopal,
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.
Ciao.
Giuseppe
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 ?
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