Splunk Search

How to search what field values are missing using a lookup table per period of time? - follow up

christopheryu
Communicator

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"
| append
[| 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:

Date Router
3/21/19 RouterA
RouterB
3/1/19 RouterC
2/11/19 Router D

Thank you in advance!

Tags (1)
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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 
0 Karma

christopheryu
Communicator

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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?

0 Karma

christopheryu
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...