I am using a search that was provided as an answer to a previously posted question - How to search what values are missing in my lookup table? https://answers.splunk.com/answers/612603/how-to-search-what-values-are-missing-in-my-lookup.html
I have a follow up question regarding the above and that is how do I check missing values per day over a period of time? Tried bin _time span=1d but did not work.
My query below (lookup file is probes.csv):
| fields Router
| eval from="feed"
[| inputlookup append=t probes.csv
| table Router
| eval from="lookup"]
| stats values(from) as from by Router
| where mvcount(from)=1 AND from="lookup"
| fields Router
| sort -Router
So expecting an output in table format like this:
2/11/19 Router D
Thank you in advance!
Give this a try
your base search | bucket span=1d _time | stats count by _time Router | fields _time Router | eval from="feed" | append [| inputlookup append=t probes.csv | table Router | eval from="lookup" | addinfo | eval dates=mvrange(relative_time(info_min_time,"@d"), relative_time(info_max_time,"+1d@d"),86400) | fields - info_* | mvexpand dates | rename dates as _time | table _time Router from ] | stats values(from) as from by _time Router | where mvcount(from)=1 AND from="lookup" | fields _time Router
Thanks but the search result shows a table of all the routers, with each row having a date and router. I'm thinking of just doing a dc(router) by day and drill down that will pull up results using the previously posted question I referenced.
When you want to validate what values from lookup doesn't show up day wise, the query would change significantly (previously one dataset from your data was compared with lookup, now multiple datasets [one dataset for each day ] has to be compared. What the maximum number of days you'll be comparing the data against lookup?
Thanks for the response. Was actually thinking of using a time picker but based on what you said, looks like search needs an overhaul. I guess 7 days would do.
As a background, the lookup is a list of probes that we have. So i would like to find out which probes did not report any event per day and hoping to look as far back as I would like to investigate, hence the time picker. There are 200+ probes that are in full mesh (21k links) reporting every five minutes (~37M events per day). Filtering using dc(Router) per day won't work either as probes are being added without me being informed.