How to find the missing number sequence from a table?

[rajeswarir]

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.

[jlemley]

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:

| makeresults 
| fields - _time 
| eval IDS="-7,-4,3,4,6,7,13" 
| makemv IDS delim="," 
| mvexpand IDS 
| sort 0 IDS
| streamstats current=f last(IDS) as last_id
| eval gap=IDS-last_id
| where gap>1
| eval allIDs=mvrange(last_id,IDS)
| mvexpand allIDs
| eval is_found=if(match(allIDs,last_id),1,0)
| where is_found=0
| table allIDs

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". 

[rajeswarir]

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.

[niketn]

@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

[FloSwiip]

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

[rajeswarir]

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?

[FloSwiip]

also in 7.0.X the part that is working in 7.1.X...
| eval IDS=replace(IDS,"^|\s|$",",")
have to be
| eval IDS=replace(IDS,"\s",",") | eval IDS=",".IDS.","

[rajeswarir]

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.