Hello all,
I have a list of hostnames in a text file that need to be in Splunk. Some of them are already in splunk as Deployment Clients, but not all of them. Some of the deployment clients have no logs in Splunk yet, either.
I am looking for a way to use this text file (or the contents) to make an adhoc search to find the missing hosts/clients. So far I've tried a big search of index=* host=<name> OR host=<name> OR host=<name> ... | dedup host
which works for listing all the hosts, but I manually have to go through and find which are missing.
I did the same thing with the deployment clients using the search | rest /services/deployment/server/clients | table clientName hostname | rename hostname as host | dedup host
This is a lot of manual searching and checking, ideally I'd like to be able to run one search to find the missing fields. I've done some digging and have not found a solution that fits my need.
As a bonus, it would be awesome to have a way to compare the list of deployment clients and the hosts. I have about 40 more clients than hosts in my data and I need to find which hosts are the culprits. I'm playing with the set diff
search, but it's a tricky one.
Edit: My set diff
search is working with | set diff [ | rest /services/deployment/server/clients | table hostname | rename hostname as host | dedup host ] [ search index=* | dedup host | table host ]
The only caveat here being that it's listing all values missing from one table or the other, so it's including things that are not deployment clients, like my search heads.
Thanks
Okay, jkat54's answer meets the question asked, but also here's a tip...which the OP probably already knows by now, two years down the road, but hey, someone revived the thread and maybe someone else will need to know...:
The original poster talked about manually creating lists and manually preparing. This is unnecessary.
If you put the table into splunk in a subsearch in brackets []
, then when it hits the end of that subsearch, it is implicitly reformatted the way you want.
(See the format command for details at http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Format).
So...
[ |inputlookup filename.csv | table host]
...turns into...
( ( host="value1") OR ( host="value2" ) OR ... ( host="lastvalue" ) )
and
[ |inputlookup filename.csv | table host foo]
...turns into...
( ( host="value1" AND foo="foovalue1" ) OR ( host="value2" AND foo="foovalue2" ) OR ...
( host="lastvalue" AND foo="lastfoovalue" ) )
|metadata type=hosts will give you all the hosts you have in your environment and then join it with your list of hosts.
For eg: you can create a inputlookup with your list of hosts and then outer join with metadata results
|inputlookup filename|fields host|join host type=outer [|metadata type=hosts|fileds host,lastTime]|search NOT lastTime=*
Or
If you have the text file indexed, then just replace the inputlookup part with your search terms.
Please check the syntaxes
You have some options:
http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/inputlookup
Then you can use a search like this
| inputlookup filename.csv
and it will return columName=host1, columnName=host2... etc... basically for every host it will create a field called columnName with the value on the line in the csv file.
So thereafter you can do joins, etc.
|inputlookup filename.csv | eval host=columnName |eval a="a"| table host a | join host [ search index=_internal | dedup host | eval b="b"| table host, b] | table host, a, b
This should make a table with host, a, b... and ...
when a=a, but b=null this is host that is in lookup but not the _internal index
when a=null, but b=b this is host that is in _internal index but not the lookup
when a=a, and b=b this is a host that is in both the _internal index and the lookup. You're expecting most of your hosts to be in this state.
2 Indexed Option
Convert your text file to CSV file with "host" on first line, and "expected" host names on lines thereafter and index the file by using the add-data option in settings of the ui. Add the file as csv with proper extractions. Then join on the newly created index.
Then do the same sort of searches above but join the two indexes instead
index=knownhostsindex |eval a="a"| table host a | join host [ search index=_internal | dedup host | eval b="b"| table host, b] | table host, a, b
This should make a table with host, a, b... and ...
when a=a, but b=null this is host that is in the knownhostsindex but not the _internal index
when a=null, but b=b this is host that is in _internal index but not the knownhostsindex
when a=a, and b=b this is a host that is in both the _internal index and the knownhostsindex. You're expecting most of your hosts to be in this state.
Question here,
does it work if we don't specify the join type=outer.
I tried it in my query and it didn't returned any results
Absolutely !!!
We do a similar thing at Finding forwarders that are not reporting in but have in the past
@maniishpawar if this answered your question, please mark it as an answer.