Query checking difference of two terms in a log


(tomer zaks) #1

Hi,

I have two terms in a log that we will call X and Y from date format.

I want to send a query that will show me all logs that the value of (X-Y) is greater than 3 (seconds).

How can I do this?


(Mark Harwood) #2

See How to calculate duration in aggregation?


(tomer zaks) #3

Hi,
Thanks for your reply, I am 10 minutes in the video and I see the video is about when X is on log A and Y is on log b. In my case, both X and Y are on the same log.

This should be simpler, no?


(Mark Harwood) #4

It is - normally when this question gets asked the timestamps are held on two different event records.
You should be able to use a script to do this at query time but it is normally more efficient to compute this at index-time.


(tomer zaks) #5

Cool thanks,
One last question regarding the scripts, does Kibana give me a script tool, or do I have to use an external common scripting language?
(I will see the video until the end and decide if to use script or index)


(Mark Harwood) #6

Can you afford to reindex your content? Queries will be faster/simpler if the duration field is pre-computed when indexed.
You can use an ingest-pipeline to derive the duration and also use the reindex api in conjunction with an ingest pipeline to patch any existing data


(tomer zaks) #7

Hi,

I am extremely new to the subject of data bases in general and ES in particular. So to make sure I understood, from your lecture I got to create a new index for every user with time intervals. After we do this we can query the logs with ease. I still didn't understand how after we have a new index we can query it much easier in my case where I anyway have the two ts in the same log?

Did I understand this correctly? (if not I will watch the start of the video again)

I will be happy for more insights


(Mark Harwood) #8

If your 2 related dates (start, end) are held on the same JSON document then your problem is simpler. Either

  1. use a script at query time to subtract (slower, harder for Kibana end users) or
  2. Reindex your content performing the subtraction at insert time (using custom app logic or ingest pipeline)

My video was for the more complex scenario where the start and end times are on two different JSON documents and need to be joined by a common key e.g. sessionID or transactionID. You said you don't have that problem?


(tomer zaks) #9

Hi,
How can I do this through Logstash?


(Mark Harwood) #10

Wrong forum :slight_smile:

I found this in the logstash forum that looks relevant: Logstash Ruby filter to subtract difference between two timestamps in single event


(system) #11

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.