topic Re: How can gaps be detected in data? in Splunk Search

How can gaps be detected in data?

sarit_s — Thu, 30 May 2019 06:56:43 GMT

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?

Re: How can gaps be detected in data?

sarit_s — Thu, 30 May 2019 10:08:57 GMT

i will give an example

i have logs from 07/05/19 15:23:57 for some SerialNumber
and the next time i have log is 08/05/19 09:19:02
i want to catch such stuff
is it possible ?

Re: How can gaps be detected in data?

DavidHourani — Thu, 30 May 2019 10:39:10 GMT

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

Re: How can gaps be detected in data?

sarit_s — Thu, 30 May 2019 10:45:50 GMT

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

Re: How can gaps be detected in data?

DavidHourani — Thu, 30 May 2019 10:48:17 GMT

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.

Re: How can gaps be detected in data?

sarit_s — Thu, 30 May 2019 10:51:00 GMT

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

Re: How can gaps be detected in data?

DavidHourani — Thu, 30 May 2019 11:34:39 GMT

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

Re: How can gaps be detected in data?

sarit_s — Thu, 30 May 2019 11:39:53 GMT

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

Re: How can gaps be detected in data?

DavidHourani — Thu, 30 May 2019 11:43:29 GMT

can you share what's in the macro please ?

Re: How can gaps be detected in data?

sarit_s — Thu, 30 May 2019 11:45:40 GMT

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

Re: How can gaps be detected in data?

DavidHourani — Thu, 30 May 2019 12:28:53 GMT

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

Re: How can gaps be detected in data?

sarit_s — Thu, 30 May 2019 12:35:42 GMT

it is also very slow

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

Re: How can gaps be detected in data?

DavidHourani — Thu, 30 May 2019 13:03:14 GMT

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 ?

Re: How can gaps be detected in data?

sarit_s — Thu, 30 May 2019 16:02:02 GMT

it is also very slow 😞

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

Re: How can gaps be detected in data?

sarit_s — Wed, 05 Jun 2019 05:49:59 GMT

hey @DavidHourani
do you have any idea ?

Re: How can gaps be detected in data?

DavidHourani — Wed, 05 Jun 2019 07:48:44 GMT

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

Re: How can gaps be detected in data?

sarit_s — Wed, 30 Sep 2020 00:47:46 GMT

unfortunately no 😞

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

Re: How can gaps be detected in data?

DavidHourani — Wed, 05 Jun 2019 08:27:18 GMT

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

Re: How can gaps be detected in data?

DavidHourani — Wed, 05 Jun 2019 08:36:11 GMT

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

Re: How can gaps be detected in data?

sarit_s — Wed, 05 Jun 2019 08:46:45 GMT

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