How to Query for length with elasticsearch (KQL)

Elasticnewbie1 · December 29, 2020, 4:58pm

Hey there,

im new to this elk world and im currently working on a threat hunting project with ELK/Zeek.
My problem is, that i want for example to query for DNS.Length > 75 within zeek data. I know that its easy to do in splunk, but how to do it with KQL ? I cant find this variable there with length. I just found some script answers, which im not sure about how to use and where to put scripts etc. Any help would be appreciated.

Best regards

aaron-nimocks · December 29, 2020, 5:06pm

I don't believe you can do this in KQL, maybe Lucene.

But here is how you can make a query to do this but it might not solve your issue depending on what you want to do with it after.

GET index-name/_search
{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": {
            "source": "doc['DNS'].value.length() > 75",
            "lang": "painless"
          }
        }
      }
    }
  }
}

Elasticnewbie1 · December 29, 2020, 5:19pm

Thanks for the fast response,

i thought KQL is based on Lucene? So is lucene "the standard"?
So for this script i need to change to lucene and just put it in there ? I cant see multiple lines there, just one for "Search".

So i changed from splunk to elk bc i thought it would be easier for my project. isnt there any chance to create a new value, based on the string length of dns querys ?

Best regards

aaron-nimocks · December 29, 2020, 5:26pm

You can change between KQL and Lucence by turning off KQL as seen below.

For that type of query you do that in Dev Tools. That is where you can run all your custom queries. So go to Dev Tools and paste in that query and run it.

ylasri · December 29, 2020, 5:34pm

You can add a custom DSL query as a filter

Elasticnewbie1 · December 29, 2020, 5:40pm

Thanks for responses,

so i change KQL to Lucene and then put:

{"script":{"source":doc['ifName'].value.length() > 75","lang":"painless"}}

or the script from aaron-nimocks there, or just put it to dev tools ?

Do i need to change the ['DNS'] to ['zeek.dns.query'] then ? im getting the following error in dev tools:

{
  "error" : {
    "root_cause" : [
      {
        "type" : "index_not_found_exception",
        "reason" : "no such index [index-name]",
        "resource.type" : "index_or_alias",
        "resource.id" : "index-name",
        "index_uuid" : "_na_",
        "index" : "index-name"
      }
    ],
    "type" : "index_not_found_exception",
    "reason" : "no such index [index-name]",
    "resource.type" : "index_or_alias",
    "resource.id" : "index-name",
    "index_uuid" : "_na_",
    "index" : "index-name"
  },
  "status" : 404
}

this is basically what i want to do in elk.

ylasri · December 29, 2020, 5:48pm

From Kibana UI :

Go to discover app and add anew filter

Click on "Edit as Query DSL"

Past the custom DSL query

{
    "script": {
          "script": {
            "source": "doc['zeek.dns.query'].value.length() > 75",
            "lang": "painless"
          }
        }
}

Click on save

ylasri · December 29, 2020, 5:52pm

You can also create a new scripted field and use it as normal field

Elasticnewbie1 · December 29, 2020, 5:56pm

aah thank you!

But now i get:

Error executing Painless script: 'doc['zeek.dns.query'].value.length() > 5'.

org.elasticsearch.index.fielddata.ScriptDocValues$Strings.get(ScriptDocValues.java:494)
org.elasticsearch.index.fielddata.ScriptDocValues$Strings.getValue(ScriptDocValues.java:508)
doc['zeek.dns.query'].value.length() > 5
                     ^---- HERE

"No results match your search criteria"

changed 75 to 5, so that there should at least be some value.. but none

ylasri · December 29, 2020, 6:19pm

OK i see, we need to check if field exist, try this

{
  "script": {
    "script": {
      "lang": "painless",
      "source": "if (!doc.containsKey('zeek.dns.query')) { return false;} else {return doc['zeek.dns.query'].value.length() > 5;}"
    }
  }
}

Elasticnewbie1 · December 29, 2020, 6:24pm

if i check zeek.dns.query i see values at least, so it shouldnt be empty

ylasri · December 29, 2020, 6:36pm

Painless is painfull try this

{
  "script": {
    "script": {
      "lang": "painless",
      "source": "if (doc['zeek.dns.query'].size() == 0) { return false;} else {return doc['zeek.dns.query'].value.length() > 5;}"
    }
  }
}

ylasri · December 29, 2020, 6:40pm

Reference is here :

doc['field'].value throws an exception if the field is missing in a document.
To check if a document is missing a value, you can call doc['field'].size() == 0 .

Elasticnewbie1 · December 29, 2020, 6:57pm

{
  "script": {
    "script": {
      "lang": "painless",
      "source": "if (doc['zeek.dns.query'].size() == 0) { return false;} else {return doc['zeek.dns.query'].value.length() > 5;}"
    }
  }
}

this seems to do the job, thank you very much for your time and help! getting results at least, will investigate further the next days.
Best regards

Elasticnewbie1 · January 12, 2021, 4:32pm

Hey again, so this script worked, but now im struggling with the next querys.
I need querys to find the 10 connections from and to a host who made the most traffic, sorted from big to small. Is this even manageable with this script language?

index=zeek sourcetype=zeek_conn 
| stats values(service) as Services sum(orig_bytes) as B by id.orig_h 
| sort -B 
| head 10 
| eval MB = round(B/1024/1024,2) 
| eval GB = round(MB/1024,2) 
| rename id.orig_h as Source 
| fields Source B MB GB Services

this is how it would look in splunk, but im not sure how to solve this with painless language..
Thanks again in advance for any help!

aaron-nimocks · January 12, 2021, 4:53pm

I am not familiar enough with Splunk to read/decipher that and would recommend making a new topic specifically for this question.

system · February 9, 2021, 4:53pm

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