How to check if values are incremental by 1 for a specific category

[Nikitha]

If the above displayed data is the result for my stats command [stats values(Values) as Values by Category], how can I use the search to check if the values for each category are incremental by 1 and output the values that have been missed.

 I want the result to look like this :

[thambisetty · Accepted solution]

try below: 

| stats max(value) as max_value min(value) as min_value values(value) as values by Category
| eval all_numbers=mvrange(min_value,max_value+1)
| fields - max_value,min_value
| nomv values
| eval values=replace(values,"\s",",")
| mvexpand all_numbers
| eval is_found=if(match(values,all_numbers),1,0)
| search is_found=0
| stats values(all_numbers) as missing_values by Category

reference: https://community.splunk.com/t5/Splunk-Search/How-to-find-the-missing-number-sequence-from-a-table/m...

[thambisetty]

It's possible to find before applying stats. after applying stats may be possible but it's not easy.

can you also confirm, if those values are in sequential with timestamp?

[ITWhisperer]

| makeresults | eval event="{\"Category\":\"Cat1\",\"Values\":[1,2,3,5,7]}\n{\"Category\":\"Cat2\",\"Values\":[6,8,9,10]}"
| eval event=split(event,"\n")
| mvexpand event
| spath input=event
| rename Values{} as Values
| fields Category, Values
| fields - _time
| eval low=tonumber(mvindex(Values,0))
| eval high=tonumber(mvindex(Values,mvcount(Values)-1))
| eval expected=mvrange(low, high + 1)
| eval missing=mvmap(expected,if(isnull(mvfind(Values,expected)),expected,NULL()))
| fields Category, missing
| rename missing as Values