topic How to find the missing number sequence from a table? in Splunk Search

How to find the missing number sequence from a table?

rajeswarir — Tue, 10 Jul 2018 09:10:23 GMT

For eg: i am having the following table after search in splunk

IDS          Time
  1                30
  3                15
  4                05
  6                10
  7                08

How to find the missing IDS 2,5 in a separate table.

NOTE: These IDS 2,5 are not present in logs. whatever present in logs is displayed in the table.

Re: How to find the missing number sequence from a table?

rajeswarir — Tue, 10 Jul 2018 11:05:19 GMT

I am currently using excel to get the missing number sequence. Is it possible to get in splunk the missing number sequence of IDS and display in different table.

Re: How to find the missing number sequence from a table?

niketn — Tue, 10 Jul 2018 11:45:33 GMT

@rajeswarir, try the following run anywhere search. Query from | makeresults till | mvexpand IDS generate dummy data as per your question.

| makeresults
| fields - _time
| eval IDS="1,3,4,6,7,8"
| makemv IDS delim=","
| mvexpand IDS
| stats max(IDS) as maxID values(IDS) as IDS
| eval allIDs=mvrange(1,maxID+1)
| fields - maxID
| nomv IDS
| eval IDS=replace(IDS,"\s",",")
| mvexpand allIDs
| eval is_found=if(match(IDS,allIDs),1,0)
| search is_found=0
| table allIDs

Re: How to find the missing number sequence from a table?

FloSwiip — Wed, 11 Jul 2018 21:40:00 GMT

Same answer but a bit more robust with negative numbers and numbers (>10)

 | makeresults | fields - _time | eval IDS="-4,3,4,6,7,13" | makemv IDS delim="," | mvexpand IDS
 | stats max(IDS) as maxID  min(IDS) as minID values(IDS) as IDS
 | eval allIDs=mvrange(minID,maxID+1)
 | fields - minID maxID
 | nomv IDS
 | eval IDS=replace(IDS,"^|\s|$",",")
 | mvexpand allIDs
 | eval is_found=if(match(IDS,",".allIDs.","),1,0)
 | search is_found=0
 | table allIDs

Re: How to find the missing number sequence from a table?

FloSwiip — Thu, 12 Jul 2018 07:40:54 GMT

Re: How to find the missing number sequence from a table?

rajeswarir — Thu, 12 Jul 2018 12:26:37 GMT

This query is giving me the expected result for the above example that i have posted. But what if the IDS are more than 1lakhs and that contains missing IDS in between? is there a way to calculate for more than 1lakh records the missing values?

Re: How to find the missing number sequence from a table?

rajeswarir — Fri, 13 Jul 2018 04:34:41 GMT

This is just an example i have posted. But i need to get the missing Ids of more than 3lakh records. so how can i get the missing IDS for the particular source.

Re: How to find the missing number sequence from a table?

jlemley — Tue, 22 Sep 2020 13:53:56 GMT

This is an old topic, but I took the answer posted by @FloSwiip combined with https://community.splunk.com/t5/Splunk-Search/search-to-find-gaps-in-data/td-p/68644 and came up with this:

Data generation is up through mvexpand - you can take the rest after that to apply to your own data.

This works with a much larger data set that would otherwise run into memory limits when using mvexpand on "allIDs".