topic Re: add fields to the query based on the lookup file in Splunk Search

add fields to the query based on the lookup file

spyme72 — Mon, 28 Sep 2020 15:13:30 GMT

i have got json data like below. i have a lookup file defined with technology and fields which i would want to display.
it would help even if i can replace the fields from the lookup table in the query
basically i want to add fields to the query based on the lookup file
Query :
index= tech sourcetype=json technology=os [|inputlookup techtable| technology= OS |fields fields]

lookup table:
technology fields
OS host
OS hostname
SAP gw/cpic_timeout
SAP gw/max_conn

Json data:

{
_technology : "OS",
architecture : "x86_64",
domain : "monsanto.com",
facterversion : "1.5.2",
fqdn : "std08a00.monsanto.com",
hardwaremodel : "x86_64",
hostname : "std08a00",
interfaces : "eth1",
ipaddress : "10.30.16.104",
ipaddress_eth1 : "10.30.16.104",
kernel : "Linux",
kernelrelease : "2.6.32.59-0.7-default",
kernelversion : "2.6.32.59",
lsbdistcodename : "n/a",
lsbdistdescription : "SUSE Linux Enterprise Server 11 (x86_64)",
lsbdistid : "SUSE LINUX",
lsbdistrelease : "11",
lsbrelease : "core-2.0-noarch:core-3.2-noarch:core-4.0-noarch:core-2.0-x86_64:core-3.2-x86_64:core-4.0-x86_64:desktop-4.0-amd64:desktop-4.0-noarch:graphics-2.0-amd64:graphics-2.0-noarch:graphics-3.2-amd64:graphics-3.2-noarch:graphics-4.0-amd64:graphics-4.0-noarch",
macaddress_eth1 : "00:0C:29:BC:FD:88",
memoryfree : "26.34 GB",
memorysize : "31.49 GB",
netmask : "255.255.252.0",
netmask_eth1 : "255.255.252.0",
operatingsystem : "SLES",
operatingsystemrelease : "11.1",
processor0 : "Intel(R) Xeon(R) CPU E7- 2830 @ 2.13GHz",
processor1 : "Intel(R) Xeon(R) CPU E7- 2830 @ 2.13GHz",
processor2 : "Intel(R) Xeon(R) CPU E7- 2830 @ 2.13GHz",
processor3 : "Intel(R) Xeon(R) CPU E7- 2830 @ 2.13GHz",
processor4 : "Intel(R) Xeon(R) CPU E7- 2830 @ 2.13GHz",
processor5 : "Intel(R) Xeon(R) CPU E7- 2830 @ 2.13GHz",
processorcount : "6",
ps : "ps -ef",
puppetversion : "2.6.17",
rubysitedir : "/usr/lib64/ruby/site_ruby/1.8",
rubyversion : "1.8.7",
sshdsakey : "AAAAB3NzaC1kc3MAAACBAN3D818gre6+WfSiH6YJlxExf/Tmq/f2KLY8oN5bV40on31qt/8VA4ShsARgKSLX2HpoOINU3vv5ENqhpUs3W9pZotOTLaxTUmF4eYgaMsLVCJFYNGfOtDVvKVjxT9vybBkaJ/Ppq4VXajimMt9pGQ0oetx3C3V0drffzM5/gSdRAAAAFQDVVrQM10pD8YX++fuxvxYxZe1UpQAAAIEAuAmCYe1NXqLN9fk7zaXf9eCjW6oGFkNbpnk3rSXX7tlOjuibYQt1xL8jpVc0la/W5Cbt3dwyLB57ZauBoW/6agJTqIoU1X8PYy1qW1q2QsgNLGco9ZDBhKHX3Mo8i/gR7sW6sN3zaAoFWd3T4LF8vebFGyWRoQgmRQlIiFmfMmIAAACBAM4ClRwgdvuZN8JmiYgaf/RcWRcpBJLmhgCS2gZjb2Wu41Msb9ain4lJmEQu5T+hA/9lKo0EG6Sp+vzKIReNOFikEIQwl0Ttj/s9P1wZkba9NplQyZqqR0v/F2Md70SUg+7RiQJ/xTX0IVssW+YMsR3uGuC8Wy+V4V6rp7WeuJRv",
sshrsakey : "AAAAB3NzaC1yc2EAAAABIwAAAIEAtxsvJhJifxCqSKGjYQtXqc29iql+CAS9OK8r+zEM0q+MXEOFikoa2CvbXahwDdXmcWYbq6N11tCNu3q8NYdTpzSA94FPptPfT5akCSzvKcKzPXQ/xmnbnbd28Vh6eRVbeknikjQMvgYhwObDr1AzGvkv1YMd+rBPZTD5Ixj5vwk=",
swapfree : "31.99 GB",
swapsize : "31.99 GB",
virtual : "vmware"
}

Re: add fields to the query based on the lookup file

lguinn2 — Wed, 06 Nov 2013 19:17:33 GMT

When you say "Add fields to the query", do you mean

1 - to add the matching data from the lookup table to the results

index= tech sourcetype=json technology=os [|inputlookup techtable| where technology= OS | fields fields]
| lookup techTable technology OUTPUT fields

Although I think you could simplify to this

index= tech sourcetype=json technology=os
| lookup techTable technology OUTPUT fields

or 2 - to add the matching data from the lookup table to the search criteria

I think you are almost doing this already

index= tech sourcetype=json technology=os [|inputlookup techtable| where technology= OS | fields fields]

Important Note: field matches are case-sensitive by default. I think that will be okay in your case.

Re: add fields to the query based on the lookup file

spyme72 — Wed, 06 Nov 2013 20:29:59 GMT

i want to add lookup table to the search criteria.

for ex:
index = tech sourcetype=json technology=os | fields ************
i want the fields to be extracted from the lookuptable.i am trying to implement properties file kind of functionality , where when i add fields to the lookup table the search query in the app automaticaly detects it and uses it in its query.

Re: add fields to the query based on the lookup file

lguinn2 — Wed, 06 Nov 2013 21:18:48 GMT

Answer #2 above should do that. Note that you need to add the where command to the original search string, as illustrated in answer #2. Your original subsearch should return an error for invalid syntax.

Re: add fields to the query based on the lookup file

spyme72 — Mon, 11 Nov 2013 14:54:16 GMT

this query too jus extracts the fields and not the values along with the fields..
is it possible to extract the values too from the JSON along with the fields and display it in a table.

Re: add fields to the query based on the lookup file

spyme72 — Mon, 11 Nov 2013 19:10:41 GMT

i think i have not posted my question clearly..

inputlookup techtable| where technology= OS | fields fields would give the result of the fields i would want to use in my second query

my second query is
index=equivalency sourcetype=json | spath | stats list(*) as *| transpose
which would result all the fields in the JSON.

i want to pass the fields from the first query to the second query to filter the JSON data.

Re: add fields to the query based on the lookup file

spyme72 — Mon, 11 Nov 2013 19:11:35 GMT

i think i have not posted my question clearly..

inputlookup techtable| where technology= OS | fields fields would give the result of the fields i would want to use in my second query

my second query is
index=equivalency sourcetype=json | spath | stats list(*) as *| transpose
which would result all the fields in the JSON.

i want to pass the fields from the first query to the second query to filter the JSON data.