topic Re: How to query data that has no value? in Splunk Search

How to query data that has no value?

dscott198 — Wed, 02 Jan 2019 23:29:03 GMT

Hi fellow Splunkers!

I'm hoping you can help my manager and I with a certain problem we're trying to solve. We have an internal system that's used by a number of employees and we're trying to find the pages that exist but employees never use.

You may already see the predicament we're running into -- When running a query with the 'rare' function, we're getting page hits that have a value of at least 1 -- as there's no log that's generated when a page is not accessed. We're looking for page hits with a value of 0.

We've thought about running a script periodically that accesses every page, therefore any value that has 1 would actually be 0. Though we don't want to do this as we're a large company and there's other organizations that very well could be thrown off by this fake data.

The best idea my manager and I have come with is finding a way to upload a table of every page that's available and comparing the logs with that table in Splunk. He and I are both novices with Splunk and don't know if this is actually possible, however. Splunk may not be the right tool for this task but we'd prefer to do it in Splunk so we can start generated regular reports on these pages that don't get accessed month over month.

Any insight you all may be able to provide would be amazing!

Re: How to query data that has no value?

valiquet — Thu, 03 Jan 2019 00:05:45 GMT

Unless you have Petabytes of txt this should be easy Please give more concrete logs.

Re: How to query data that has no value?

sdchakraborty — Fri, 04 Jan 2019 03:37:09 GMT

Hi,

I think the below idea of yours should work.

The best idea my manager and I have come with is finding a way to upload a table of every page that's available and comparing the logs with that table in Splunk.

you can store the page list in a lookup file then write a SPL query to compare the indexed data to lookup table data. If you need help on creating lookups and SPL query let me know I can help.

Also you can refer the below link for lookup creation,

https://docs.splunk.com/Documentation/Splunk/7.2.3/Knowledge/Usefieldlookupstoaddinformationtoyourevents

your SPL should look like below,

| inputlookup <your lookup name(lookup should contain the page title)>
| join type=left page_title
[| <your search which should return the page name as well> ]

Sid

Re: How to query data that has no value?

niketn — Sat, 05 Jan 2019 03:35:19 GMT

@dscott198 for the internal system that you have is there a repository of how many pages exist in the system. If there is one then after every system change (i.e. Addition or Removal of Pages during Upgrades) the list would need to be updated in Splunk. It could be through
1) Database (through Splunk DB Connect)
2) REST API (through Splunk Add On Builder)
or 3) Manually Scripted (through Splunk Add On Builder)

Based on your need update the master inventory list to Lookup or KV Store

Once you have master inventory in Splunk you can refer to several examples on Splunk Answers where Lookups can be used to find missing Data in the Index:

https://answers.splunk.com/answers/574406/how-to-show-hosts-with-no-entries.html
https://answers.splunk.com/answers/614029/how-to-alert-when-we-are-receiving-data-from-hosts.html

Re: How to query data that has no value?

dscott198 — Wed, 09 Jan 2019 14:56:17 GMT

This totally worked!

Thanks for the help, sdchakraborty. Also thank you for offering to help me with it in case I got stuck but I was able to struggle through it on my own.

Re: How to query data that has no value?

sdchakraborty — Wed, 09 Jan 2019 14:59:00 GMT

Cool...Have a nice day ☺️

Re: How to query data that has no value?

niketn — Wed, 09 Jan 2019 19:33:18 GMT

@dscott198 if your issue is resolved, please accept the answer by @sdchakraborty to mark this question as answered!