Timestamp in DSL query

[oalimerko (Oltion Alimerko)]

Dear all,

i have the query below for monitoring the logs of my application in case any issue with database connection occurs in the last 6min:

GET _search 
{
      "query": {
        "query_string": {
          "query": "message:\"com.microsoft.sqlserver.jdbc.SQLServerException\" AND @timestamp:(>=now-6m AND <now)"
        }
      },
      "aggs": {
        "application": {
          "terms": {
            "field": "kubernetes.labels.app.keyword"
            }
          }
        }
      }

My question is how can i define the timestamp like this:
@timestamp: (between 2022-11-16T10:00:00 and 2022-11-16T10:05:00)

The idea is to get some error for a particular timestamp and not for the last 6min

[carly.richmond]

Hi Oltion,

I assume you want to query for logs with timestamps between two dates? Have a look at the range query with date support. These examples should help:

1. Example range query on dates
2. Example range query with timezone

[oalimerko (Oltion Alimerko)]

hi,

i have some issue when i combine query_string with range

Here is my query:

GET _search
{
  "query": {
    "query_string": {
      "query": "message:\"com.microsoft.sqlserver.jdbc.SQLServerException\""
    }
  },
    "range": {
      "timestamp": {
        "time_zone": "+01:00",        
        "gte": "2022-11-11T11:00:00", 
        "lte": "now" 
      }
    }
  }

but i get the error:

{
  "error" : {
    "root_cause" : [
      {
        "type" : "parsing_exception",
        "reason" : "Unknown key for a START_OBJECT in [range].",
        "line" : 7,
        "col" : 14
      }
    ],
    "type" : "parsing_exception",
    "reason" : "Unknown key for a START_OBJECT in [range].",
    "line" : 7,
    "col" : 14
  },
  "status" : 400
}

[oalimerko (Oltion Alimerko)]

hi again,

as per doc i found out this solution wrapping timestamp with square brackets

GET _search
{
  "query": {
    "query_string": {
      "query": "message:\"com.microsoft.sqlserver.jdbc.SQLServerException\" AND @timestamp:[2022-11-17T01:00+01:00 TO 2022-11-17T01:30+01:00]"
    }
  },
  "aggs": {
        "application": {
          "terms": {
            "field": "kubernetes.labels.app.keyword"
          }
        }
  }
}