How can gaps be detected in data?

[sarit_s]

Hello, is there a way to detect gaps in data by some id?
As well as check if the gap is greater than 4 hours, then show the last event and the one before him?

[DavidHourani · Accepted solution]

Hi @sarit_s,

Yes you can !

Here's an easy command that you can apply on your data with 900 sec gap:

 ... | streamstats current=f last(_time) as last_time by host | eval gap = last_time - _time | where gap > 900 | convert ctime(last_time) as last_time | table _time last_time host

from here :
https://answers.splunk.com/answers/69728/search-to-find-gaps-in-data.html

Cheers,
David

[sarit_s]

Hi David,
thanks !
i saw this post but i have some issues with it:

what is 900? 900 seconds ?
also, i want to see the relevant events
so, if taking my last example:
then i want to the see the event from 07/05/19 15:23:57
and the event from 08/05/19 09:19:02

third thing is that this query running for very long time

[DavidHourani]

yeah 900 seconds, since time is in epoch, subtracting now and latest will be in seconds.

Give me your original query and ill give you the right part to add to it for time gap.

[sarit_s]

thanks !

index=ssys_*_pj 
| `SerialNumber`
| streamstats current=f last(_time) as last_time by SerialNumber
| eval gap = last_time - _time
| where gap > 14400 
| convert ctime(last_time) as last_time
| table _time last_time SerialNumber

[DavidHourani]

try this, itll give u the gap between the last time u saw the serialnumber and the time u ran the search :

index=ssys_*_pj 
 | `SerialNumber`
 | stats last(_time) as last_time by  SerialNumber
 | addinfo
 | eval gap = last_time -  info_search_time
 | where gap > 14400 
 | convert ctime(last_time) as last_time

[sarit_s]

Thanks David
but it is also very very slow

also, i think that the last time i saw the SerialNumber won't give the right results since the gap can be 7 days ago and i saw the SerialNumber today also

and the gap should not be related to the time i run the search, but the different time between two events of one Serialnumber

[DavidHourani]

can you share what's in the macro please ?

[sarit_s]

eval SerialNumber = mvindex(split(source,"/") ,7)

[DavidHourani]

okay, lets give delta a try then, could be faster :

 index=ssys_*_pj 
    | delta _time p=1 | rename delta(_time) AS timeDeltaS
    | stats last(timeDeltaS) as timeDeltaS by  SerialNumber
    | where timeDeltaS > 14400

[sarit_s]

it is also very slow

also, can it be in a table where i will see SerialNumber and the relevant events ?

[DavidHourani]

oh yeah haha, like this :

  index=ssys_*_pj 
     | delta _time p=1 | rename delta(_time) AS timeDeltaS
     | `SerialNumber`
     | stats last(timeDeltaS) as timeDeltaS by  SerialNumber
     | where timeDeltaS > 14400

Are you working on the source field only ?

[sarit_s]

it is also very slow 😞

im working only on the SerialNumber field which im taking from the source
and also _time field

[sarit_s]

hey @DavidHourani
do you have any idea ?

[DavidHourani]

Hey @sarit_s,

I was thinking maybe move the where clause to the beginning of the query like this :

index=ssys_*_pj 
      | delta _time p=1 | rename delta(_time) AS timeDeltaS
| where timeDeltaS > 14400
| `SerialNumber`
 | stats last(timeDeltaS) as timeDeltaS by  SerialNumber

Let me know if that's faster for u

[sarit_s]

unfortunately no 😞

i found that the first option :
index=ssys_*_pj
| SerialNumber
| streamstats current=f last(_time) as last_time by SerialNumber
| eval gap = last_time - _time
| where gap > 14400
| convert ctime(last_time) as last_time
| table _time last_time SerialNumber

its the fastest between them all , maybe we can find a way to improve it so it will be a little bit faster

[DavidHourani]

Okay, try this then based on a mix of all searches so far:

index=ssys_*_pj 
| dedup 2 source
| eval SerialNumber = mvindex(split(source,"/") ,7) 
| streamstats current=f last(_time) as last_time by SerialNumber
| eval timeDeltaS = last_time - _time
| stats last(timeDeltaS) as timeDeltaS by  SerialNumber

[DavidHourani]

Just modified the previous comment, have a look 🙂 over how much time is this search running ?

[sarit_s]

well.. more than 6 minutes later and it is still running with no result 😕

[DavidHourani]

updated last answer again, since i dont know how much volume you're using not sure how fast we can make it run, a good start would be also to specify the required sourcetype in addition to the change I added.

[sarit_s]

looks like its a little bit faster
timeDeltaS returns 0,1,2 values.. what is the meaning of that ?