R002 Advanced ELK Queries
Learnings
Kibana Query Language (KQL)
KQL is a simple text-based query language for filtering data.
<field>:*
Filter events when a value exists for the specified field
http.request.method:*
<field>:<value>
Filter events with a specific value in the specified field
http.request.method:GET
<field>:<phrase>
Filter events with keywords mentioned in the specific field. Order of the keywords does not matter
comment: this is a phrase
<field>:"<phrase>"
Filter events with keywords mentioned in the specific field. Order of the keywords does matter
comment: "this is a phrase"
\
Escape character. +,-,=,&&,||,&,|,! should be escaped in KQL
http.request.url:https\://example.com
AND, OR, NOT
Boolean operators
comment: negative AND user: xyz
>, <, >=, <=
Range parameters
time>=2024-01-30 AND time<=2024-02-02
*
Wildcard, any number of characters
http.request.method:*
?
Wildcard, a single character
http.response.status_code:40?
Lucene Query Language
Lucene query is an advanced query language that is supported in Kibana. In order to use Lucene language, it needs to enabled before querying. This can be done as below:
Lucene language is used to perform fuzzy, proximity and regular expression based searches. It does not support nested field based searches.
<field>:<search term>~<fuzziness value>
To search for events where a field value is similar but not identical. One particular use case can be to look for misspelt words.
http.request:HEAD~4 (Look for the events where the HTTP request is HEAD and is misspelt if so)
<field>:<search term>~<slop value>
To match an event with field value containing 2 or more terms within a specific distance. The syntax is same but the proximity search is used for matching phrases.
log_message:"server error"~1 (Look for the events where keywords server and error are 1 word distance apart)
<field>:/regex expression/
Filter events with keywords matching the expression
comment: /(True|False).*/ (Look for the events where the comment starts with True or False keyword)
NOTE: Regex used in Kibana matches the expression based on the data type of the field. For example: if data type is text, then it matches individual word but if the data type is keyword, then it matches that entire keyword.
THM Questions
Q1. How do you escape the text "password:Me&Try=Hack!" (Not including the double quotes)
password:Me\&Try\=Hack!
Q2. Using wildcards, what will your query be if you want to search for all documents that contain the words "hacking" and "hack" in the "activity" field?
activity:hack*
Q3. How many incidents exist where the affected file is "marketing_strategy_2023_07_23.pptx"?
4 (affected_systems.affected_files.file_name : "marketing_strategy_2023_07_23.pptx")
Q4. How many incidents exist where the affected files in file servers are titled "marketing_strategy"?
135 (affected_systems.system_type:"File Server" AND affected_systems.affected_files.file_name : marketing_strategy*)
Q5. There is a true positive alert on a webserver where the admin and it users were logged on. What is the name of the webserver?
web-server-77 (incident_comments:"true positive" AND affected_systems.system_type:"Web Server" AND affected_systems.logged_on_users:"admin")
Q6. How many "Data Leak" incidents have a severity level of 9 and up?
52 (incident_type : "Data Leak" AND severity_level >=9)
Q7. How many incidents before December 1st, 2022 has AJohnston investigated where the affected system is either an Email or Web server?
63 (team_members.name : "AJohnston" and affected_systems.system_type : ("Email Server" or "Web Server") and incident_date < "2022-12-01")
Q8. From the incident IDs 1 to 500, what is the email address of the SOC Analyst that left a comment on an incident that the data leak on file-server-65 is a false positive?
[email protected] (incident_id <= 500 AND affected_systems.system_name : "file-server-65" AND incident_comments : "False Positive")
Q9. Including the misspellings, how many incidents has JLim handled where he misspelt the word “true”?
110 (team_members.name : "JLim" AND incident_comments : true~4)
Q10. How many incidents has JLim handled where he misspelt the word “negative”?
4 (First run team_members.name : "JLim" AND incident_comments : negative~8, second team_members.name : "JLim" AND incident_comments : "negative". Now, subtract the values)
Q11. How many incidents are there when you want to look for the words "data leak" and "true negative" in the comments that are at least 3 words in between them?
33 (incident_comments : "data leak"~0 AND incident_comments : "true negative"~0 AND incident_comments : "leak true"~3)
Q12. How many incidents has AJohnston investigated that have the words "detected" and "negative" in the comments that are two words apart?
40 (team_members.name:"AJohnston" AND incident_comments : "detected negative"~2)
Q13. How many incidents are there where a "client_list" file was affected by ransomware?
70 (incident_type:"Ransomware" AND affected_systems.affected_files.file_name:/.*client_list.*/)
Q14. What is the name of the affected system at the earliest incident date that EVenis investigated with a filename containing the word "project"?
file-server-78 (team_members.name:"EVenis" AND affected_systems.affected_files.file_name:/.*project.*/)
Last updated