topic Re: using a lookup file to populate a search query in Splunk Search

using a lookup file to populate a search query

vincenp2 — Thu, 26 Jul 2018 10:23:08 GMT

I have a lookup table containing a list of building names - which I think may be useful in creating the query I need

Buildings 'call in' on a regular basis - and events are created to show this.
I want to use the lookup table to run a query to show me if any buildings have NOT 'called in' in the past 7 days

so basically I need a query that searches for these 'call in' events from each building, and alert if a building has NOT 'called in' in the past 7 days

I can run a query looking back over the last 7 days
index=xyz | table building_name
which will report out any buildings that have reported in, but I need to know of the buildings that have NOT reported in

How can I create a report that does this, using the existing lookup table I have which contains all building names?

Re: using a lookup file to populate a search query

adonio — Thu, 26 Jul 2018 11:12:19 GMT

maybe something like that:

| inputlookup your_building_lookup.csv | search NOT [search index=xyz building_name=* | dedup building_name | fields building_name]

hope it helps

Re: using a lookup file to populate a search query

vincenp2 — Thu, 26 Jul 2018 11:38:54 GMT

Hi thanks for replying so quickly - when I run this query it basically produces the whole content of the 'building_names' lookup file (5800 rows) - regardless of the time period I use?

I would expect to see a small number of building names, perhaps just 10 or so

I kinda understand the logic of the query you sent, and would have thought it might just produce a list of those buildings in the lookup table but had not produced any events in the time period queried