Splunk Search

How can gaps be detected in data?

sarit_s
Communicator

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?

1 Solution

DavidHourani
Super Champion

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

View solution in original post

DavidHourani
Super Champion

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

View solution in original post

sarit_s
Communicator

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

0 Karma

DavidHourani
Super Champion

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.

0 Karma

sarit_s
Communicator

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
0 Karma

DavidHourani
Super Champion

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
0 Karma

sarit_s
Communicator

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

0 Karma

DavidHourani
Super Champion

can you share what's in the macro please ?

0 Karma

sarit_s
Communicator
eval SerialNumber = mvindex(split(source,"/") ,7)
0 Karma

DavidHourani
Super Champion

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
0 Karma

sarit_s
Communicator

it is also very slow

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

0 Karma

DavidHourani
Super Champion

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 ?

0 Karma

sarit_s
Communicator

it is also very slow 😞

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

0 Karma

sarit_s
Communicator

hey @DavidHourani
do you have any idea ?

0 Karma

DavidHourani
Super Champion

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

0 Karma

sarit_s
Communicator

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

0 Karma

DavidHourani
Super Champion

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
0 Karma

DavidHourani
Super Champion

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

0 Karma

sarit_s
Communicator

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

0 Karma

DavidHourani
Super Champion

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.

0 Karma

sarit_s
Communicator

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

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.