SOC: Orienting An Analyst

The purpose of this content is to step through basic, common queries that a Security Operations Center analyst might use when trying to orient themselves to their data sources. We will use a series of exploratory queries on tabular data that has been set up with an auto extractor in advance. It is intentionally basic but builds up some basic, important tooling that any analyst will love to have available. We will ultimately build up to a common use case of hunting down the results of a phishing email.

Data Sources https://github.com/kkneomis/kc7_data/tree/main/envolvelabs

Query 1:

Understanding the components of a query, who are the employees at our company?

 

tag=envolvelabs-Employees ax
| limit 10
| table

Query 2:

Using the count [Math Module] to determine how many unique employees we have

 

tag=envolvelabs-Employees ax
| count by name
| table count

Query 3:

Using the unique module to view distinct values of a column, we’ll do usernames this time and add an alphabetical sort

 

tag=envolvelabs-Employees ax
| unique username
| sort by username asc
| table username

Query 4:

What if we want to know the breakdown of peoples’ roles from the data we have available in our data source?

 

tag=envolvelabs-Employees ax
| count by role
| table role count

Query 5:

Charting - Wouldn’t it be neat to see that as a pie chart? *note: need to change visualization type, use “ visualization options ” cogwheel after the chart is rendered*

 

tag=envolvelabs-Employees ax
| count by role
| chart count by role

Query 6:

We aren’t sure what column a particular piece of data (the ip_addr) assigned to a user resides in, we’ll try out the words search processing module

 

tag=envolvelabs-Employees ax
| words "192.168.0.44"
| table

Query 7:

Lets query for something specific using a comparison, “ == ”, operator, we know the field we are looking for is the “ ip_addr ” field

 

tag=envolvelabs-Employees ax ip_addr == 192.168.0.44
| table

Query 8:

Another comparison operator “ ~ ”

 

tag=envolvelabs-Email ax recipient ~ "envolvelabs.com"
| table

Query 9:

Another comparison operator “ !~ ” to filter out fields with specific matching values

 

tag=envolvelabs-Email ax recipient ~ "envolvelabs.com" sender
!~ "envolvelabs.com"
| count by sender
| sort by count desc
| table sender count

Query 10:

Extracting data from a field to create a new field using the “ fields ” module

 

tag=envolvelabs-Email ax recipient ~ "envolvelabs.com" sender
!~ "envolvelabs.com"
| fields -e sender -d "@" [ 1 ] as sender_domain
| count by sender_domain
| sort by count desc
| table sender_domain count

Query 11:

Using “ eval ” to compare values of a field (in this case the result of count)

 

tag=envolvelabs-Email ax recipient ~ "envolvelabs.com" sender
!~ "envolvelabs.com"
| fields -e sender -d "@" [ 1 ] as sender_domain
| count by sender_domain
| sort by count desc
| eval count

Query 12:

Ensure recipients are only counted once as we want to answer how many different recipients received messages from a each sender_domain

 

 

tag=envolvelabs-Email ax recipient ~ "envolvelabs.com" sender
!~ "envolvelabs.com"
| fields -e sender -d "@" [ 1 ] as sender_domain
| unique sender_domain recipient
| count by sender_domain
| sort by count desc
| eval count

Query 13:

Use a partial match of the url field to find entries with our domain of interest

 

tag=envolvelabs-OutboundBrowsing ax url ~ "https://illness.med"
| table

Query 14:

Extract the domain out of a url and search for a particular pattern within the new enumerated field

 

tag=envolvelabs-OutboundBrowsing ax
| regex -e url "(?P^(?:https?:\/\/)?(?:[^@\/\n]+@)?(?:www\.)?([^:\/\n]+))"
| grep -e domain "https://illness.med"
| table

Query 15:

Adding a filter to see only users that issued an HTTP POST request

 

tag=envolvelabs-OutboundBrowsing ax method == "POST"
| regex -e url "(?P^(?:https?:\/\/)?(?:[^@\/\n]+@)?(?:www\.)?([^:\/\n]+))"
| grep -e domain "https://illness.med"
| table

Query 16:

Querying across tables to render a meaningful result: who got phished?

 

@usermap {
tag=envolvelabs-Employees ax ip_addr == "192.168.2.83"
| table -nt ip_addr name username
} ;
tag=envolvelabs-OutboundBrowsing ax method == "POST"
 
| lookup -s -r @usermap src_ip ip_addr ( name username )
| regex -e url "(?P^(?:https?:\/\/)?(?:[^@\/\n]+@)?(?:www\.)?([^:\/\n]+))"
| grep -e domain "https://illness.med"
| table timestamp src_ip name username method domain url user_agent

Query Bonus (17):

Search all tags for the given pattern

 

tag=envolvelabs-* grep "https://illness.med"
| table TAG DATA