Dealing with Arrays and IP address fields in elasticsearch SQL

Elastic Stack Elasticsearch
1

I'm using elastiflow by @rcowart [tagging for FYI]. Certain flow fields such as TCP flags return array. Other fields such as source / dest IP return IP address types.

If I try to query them using the Elasticsearch SQL approach on version 6.3.2, I end up with errors like below.

How do I get around this Is there a way to reformat them as strings in the query?

QUERY:

POST _xpack/sql?format=txt
{
  "query":"SELECT * FROM elastiflow*"
}

OUTPUT:

{
  "error": {
    "root_cause": [
      {
        "type": "sql_illegal_argument_exception",
        "reason": "Arrays (returned by [flow.tcp_flags]) are not supported"
      }
    ],
    "type": "sql_illegal_argument_exception",
    "reason": "Arrays (returned by [flow.tcp_flags]) are not supported"
  },
  "status": 500
}

QUERY:

POST _xpack/sql?format=txt
{
  "query":"SELECT flow.src_addr FROM elastiflow*"
}

OUTPUT:

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem(s)\nline 1:8: Cannot use field [flow.src_addr] type [ip] as is unsupported"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem(s)\nline 1:8: Cannot use field [flow.src_addr] type [ip] as is unsupported"
  },
  "status": 400
}

QUERY: HOW DO I QUERY A TIMESTAMP FIELD WHOSE NAME IS @timestamp?
Ideally, in SQL such a query would translate to
select [@timestamp] from elastiflow

POST _xpack/sql?format=txt
{
  "query":"SELECT flow.service_port, @timestamp FROM elastiflow* "
}


POST _xpack/sql?format=txt
    {
      "query":"SELECT flow.service_port, [@timestamp] FROM elastiflow* "
    }

OUTPUT:

{
  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "line 1:27: mismatched input '@timestamp' expecting {'(', 'ANALYZE', 'ANALYZED', 'CAST', 'CATALOGS', 'COLUMNS', 'DEBUG', 'EXECUTABLE', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'FALSE', 'FORMAT', 'FUNCTIONS', 'GRAPHVIZ', 'MAPPED', 'MATCH', 'NOT', 'NULL', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PLAN', 'RLIKE', 'QUERY', 'SCHEMAS', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TRUE', 'TYPE', 'TYPES', 'VERIFY', '+', '-', '*', '?', STRING, INTEGER_VALUE, DECIMAL_VALUE, IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}"
      }
    ],
    "type": "parsing_exception",
    "reason": "line 1:27: mismatched input '@timestamp' expecting {'(', 'ANALYZE', 'ANALYZED', 'CAST', 'CATALOGS', 'COLUMNS', 'DEBUG', 'EXECUTABLE', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'FALSE', 'FORMAT', 'FUNCTIONS', 'GRAPHVIZ', 'MAPPED', 'MATCH', 'NOT', 'NULL', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PLAN', 'RLIKE', 'QUERY', 'SCHEMAS', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TRUE', 'TYPE', 'TYPES', 'VERIFY', '+', '-', '*', '?', STRING, INTEGER_VALUE, DECIMAL_VALUE, IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}",
    "caused_by": {
      "type": "input_mismatch_exception",
      "reason": null
    }
  },
  "status": 400
}

UPDATE:

I'm able to query on the @timestamp field using double quotes. ('select "@timestamp" from tablename)
However, I'm still clueless on how to deal with IP fields.

Thanks.

Tagging @Dale_McDiarmid - based on the SQL Blog series

2

Hi @blueren,

IP fields are on our to-do list, but no ETA for it yet. Can you, please, create a github issue for this? I don't think there is one already.
Until then, I think the only option is to specifically mention in the list of fields to select only those that you are interested in (skipping the arrays and IP fields and not using *).

Or, for IP fields, you could do something like the following, but this requires a new field to be added and any already indexed data to be reindexed:

        "ip_addr": {
          "type": "ip",
          "copy_to": "keyword_ip_addr"
        },
        "keyword_ip_addr": {
          "type": "keyword"
        }

And then in the select you will select that keyword_ip_addr specifically.

3

Thanks!
I've opened an issue in github regarding the same.

4

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