how to query for regex string in kibana search

[_asp_]

Hi,

I have a message with a field called "payload" which holds following string:

Code: Select all

DB_TMMESSAGES: (NO/PARTIAL TIME RANGE SYSDATE-1/SYSDATE+1), XSQL-Filter: '(TKM_ID >= -1059235127 AND TKM_INBOUND = TRUE)', Alias-Sort: '+TKM_ID', #0...#49 (PageSize: 50), , Fetched: 0, 639 ms

Now I want (without changing the logstash parsing) to filter for events which where TKM_ID is positive. Also I don't know, if there are whitespaces or not.
So I want to show all messages which contains the the value "TKM_ID" and are NOT matching the following regex on field payload

Code: Select all

.*TKM_ID\s?[\<\>]=?\s?-\d+.*

How do I do that?

Thanks a lot, Andreas

[mcapra]

As of right now, users are unable to query using regex via the Kibana dashboard. There is a way to write a raw elasticsearch query that uses regex though:
https://www.elastic.co/guide/en/elastic ... query.html

Two caveats:

• We store most message fields as non-analyzed generic strings. This means that as far as querying is concerned, everything is lower-case.
• You will need to do additional escaping in your regex for it to play nice with elasticsearch.

The easiest way to tackle this would be a grok filter that parses out the value TKM_ID is being compared to. Then you could just filter on that field where the field is greater than 0. This violates the following condition you've mentioned though:

without changing the logstash parsing

I was able to match TKM_ID by itself like so:

Code: Select all

[root@localhost ~]# curl -XPOST "http://localhost:9200/_search?pretty" -d '{"query":{"regexp":{"message":".*tkm_id.*"}}}'
{
  "took" : 28,
  "timed_out" : false,
  "_shards" : {
    "total" : 135,
    "successful" : 135,
    "failed" : 0
  },
  "hits" : {
    "total" : 2,
    "max_score" : 1.0,
    "hits" : [ {
      "_index" : "logstash-2016.09.26",
      "_type" : "import_raw",
      "_id" : "AVdnaCI8S27C0bsfbjLN",
      "_score" : 1.0,
      "_source":{"message":"DB_TMMESSAGES: (NO/PARTIAL TIME RANGE SYSDATE-1/SYSDATE+1), XSQL-Filter: '(TKM_ID >= -1059235127 AND TKM_INBOUND = TRUE)', Alias-Sort: '+TKM_ID', #0...#49 (PageSize: 50), , Fetched: 0, 639 ms\n","@version":"1","@timestamp":"2016-09-26T16:51:00.263Z","type":"import_raw","tags":["import_raw"],"host":"192.168.67.97"}
    }, {
      "_index" : "logstash-2016.09.26",
      "_type" : "import_raw",
      "_id" : "AVdnaG-SS27C0bsfbjLu",
      "_score" : 1.0,
      "_source":{"message":"DB_TMMESSAGES: (NO/PARTIAL TIME RANGE SYSDATE-1/SYSDATE+1), XSQL-Filter: '(TKM_ID >= 3000 AND TKM_INBOUND = TRUE)', Alias-Sort: '+TKM_ID', #0...#49 (PageSize: 50), , Fetched: 0, 639 ms\n","@version":"1","@timestamp":"2016-09-26T16:51:19.636Z","type":"import_raw","tags":["import_raw"],"host":"192.168.67.97"}
    } ]
  }
}

Trying to match the < and > characters is proving difficult. I'll update if I figure it out.

[_asp_]

then other question, which may be a workaround:

Is there a way to search for following string in kibana? "TKM_ID >= -1*"
I mean searching for the string including the given whitespaces, including < > = and the - ?
Then I could create a nasty workaround by giving all combinations I need and combine then with OR condition


Searching seems to work word wise only but I need to search over more than one word.

[mcapra]

The easiest way to tackle this would be a grok filter that parses out the value TKM_ID is being compared to. Then you could just filter on that field where the field is greater than 0.

That's sort of what I was getting at here. I wrote the following grok filter to handle this (I was using import_raw as the identifier, yours may be different):

Code: Select all

if [type] == "import_raw" {
    grok{
         match => [ 'message', '(?:TKM_ID\s?[\<\>]=?)%{SPACE}%{NUMBER:tkm_id_value:int}' ]
    }
}

The grok filter will pull the value following TKM_ID >= or TKM_ID <= and store it in the tkm_id_value field as an int type.

2016_09_27_10_42_38_Dashboard_Nagios_Log_Server.png

Then, using the following query on the NLS dashboard, I can get all values for tkm_id_value greater than 0:

Code: Select all

tkm_id_value:[0 TO *]