Splunk Search

How to find the missing number sequence from a table?

rajeswarir
New Member

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.

Tags (2)
0 Karma

jlemley
Path Finder

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

0 Karma

rajeswarir
New Member

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.

0 Karma

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

FloSwiip
Path Finder

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
New Member

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?

0 Karma

FloSwiip
Path Finder

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

0 Karma

rajeswarir
New Member

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...