Please note that all queries should be qualified with partition columns (year, month, day, hour) for performance reasons.
This is often one of the first questions asked in an investigation. Given there is some known bad indicator such as an IP address, then if there is related activity in your network/systems, a detailed investigation will be needed. Panther makes asking such questions easy using the 'all_logs' Athena view which will search all data for the indicator of interest. Since most often the answers to these question are negative, making this a fast and efficient operation reduces investigation time. In this example the Panther field
p_any_ip_addresses is used. Panther extracts a number of common indicator fields over all data sources into standard fields (see Panther Fields).
SELECTp_log_type, count(1) AS row_countFROM panther_views.all_logsWHERE year=2020 AND month=1 AND day=31 AND contains(p_any_ip_addresses, '220.127.116.11')GROUP BY p_log_type
Ranking activity (top or bottom) is a useful technique to gain visibility into a network. High ranking activity might help locate IP addresses involved in a DDOS attack while low ranking (change ORDER BY to ASC) might highlight sneaky activity.
SELECTip,count(1) as total_rowsFROM panther_views.all_logsCROSS JOIN UNNEST(p_any_ip_addresses) AS t(ip)WHERE year=2020 AND month=1 AND day=23GROUP BY ipORDER BY total_rows DESCLIMIT 10
This is a variant of the above query where we are ranking the IPs by how many data sources they show activity. This shows the degree of "reach" the IP address has over all your systems.
SELECTip,count(distinct p_log_type) as datasetsFROM(SELECTp_log_type,ipFROM panther_views.all_logsCROSS JOIN UNNEST(p_any_ip_addresses) AS t(ip)WHERE year=2020 AND month=1 AND day=23GROUP BY ip, p_log_type)GROUP BY ipORDER BY datasets DESCLIMIT 10
Remote shells typically have a human at one end. During an investigation, isolating sessions from SSH and RDP is often a standard procedure to identify specific actor activity.
SELECT*FROM panther_tables.aws_vpcflowWHEREyear=2020 AND month=1 AND day=23ANDsrcport IN (22, 3389) or dstport IN (22, 3389)ORDER BY p_event_time ASC
During an investigation often particular IP addresses are identified as being of interest (e.g, a known command and control node). Once the role of an IP address is identified, isolating and explaining that activity is of interest. This can indicate which resources are likely to be compromised.
SELECT*FROM panther_tables.aws_vpcflowWHERE year=2020 AND month=1 AND day=31 AND contains(p_any_ip_addresses, '18.104.22.168')ORDER BY p_event_time ASC
If there are concerns of a credential breach, then accounting for all AWS console activity is of critical importance. This query will find all the CloudTrail sourceIPaddresses involved in console signins and then return all the VPC Flow activity related. This will show if there are common IP addresses. Of particular interest are IP addresses outside of your organization communicating with the instances as well as logging into the console. This may indicate a compromise where an unauthorized actor is accessing account resources.
WITH cloudTrailIPs as(SELECTDISTINCT sourceIPAddress AS ipFROM panther_tables.aws_cloudtrailWHEREyear=2020 AND month=2 AND day=1ANDeventtype = 'AwsConsoleSignIn')SELECT*FROM cloudTrailIPs ips JOIN panther_tables.aws_vpcflow flow ON (ips.ip = flow.srcaddr OR ips.ip = flow.dstaddr)WHEREyear=2020 AND month=2 AND day=1ORDER BY p_event_time ASC
The root account should almost never sign into the AWS console; find all such signins.
SELECT*FROM panther_tables.aws_cloudtrailWHEREyear=2020 AND month=1 AND day=23ANDeventtype = 'AwsConsoleSignIn'ANDuseridentity.arn LIKE '%root%'ORDER BY p_event_time ASC
This query is similar to the above query, with the IP addresses ranked for all console logins. This helps identify which IP addresses are signing into the console while ranking the relative activity. This can often highlight anomalous behaviors.
SELECTsourceipaddress,count(1) as total_rowsFROM panther_tables.aws_cloudtrailWHEREyear=2020 AND month=1 AND day=23ANDeventtype = 'AwsConsoleSignIn'GROUP BY sourceipaddressORDER BY total_rows DESC
During an investigation a particular instance may become the focus. For example, if it is compromised. This query uses the the Panther field
p_any_aws_instance_ids to easily search over all CloudTrail events for any related activity.
SELECT*FROM panther_tables.aws_cloudtrailWHERE year=2020 AND month=1 AND contains(p_any_aws_instance_ids, 'i-0c4f541ef2f82481c')ORDER BY p_event_time ASC
Similar to the above query, the Panther field
p_any_aws_arns can be used to quickly and easily find all CloudTrail activity related to an ARN of interest (perhaps an ARN of role known to be compromised).
SELECT*FROM panther_tables.aws_cloudtrailWHERE year=2020 AND month=1 AND contains(p_any_aws_arns, 'arn:aws:iam::123456789012:role/SomeRole')ORDER BY p_event_time ASC
This is another variation of using a Panther field to broadly query. In this case finding all CloudTrail data related to an account of interest using
p_any_aws_account_ids (perhaps the account is compromised and the concern is lateral movement).
SELECT*FROM panther_tables.aws_cloudtrailWHERE year=2020 AND month=1 AND contains(p_any_aws_account_ids, '123456789012')ORDER BY p_event_time ASC
Often when credentials have been breached, there is concern about an actor creating or modifying infrastructure. The below query finds all RunInstances commands. These should be reviewed for anomalous activity. For example, actors have been known to spin-up large numbers of GPU instances for bitcoin mining in compromised accounts.
SELECTp_event_time,p_any_aws_instance_idsFROM panther_tables.aws_cloudtrailWHERE year=2020 AND month=1 AND eventname = 'RunInstances'ORDER BY p_event_time ASC
GuardDuty is a valuable source of visibility into threats against your infrastructure. However, it can produce a large number of findings. This query shows the distribution of findings which be used to assess the posture of an account.
SELECTseverity,count(1) AS total_rowsFROM panther_tables.aws_guarddutyWHERE year=2020 AND month=1GROUP BY severityORDER BY total_rows DESC
Similar to the above example, but in this example the query characterizes the findings by ranking affected resources.
SELECTjson_extract(resource, '$.resourcetype') AS resource_type,count(1) AS total_rowsFROM panther_tables.aws_guarddutyWHERE year=2020 AND month=1GROUP BY json_extract(resource, '$.resourcetype')ORDER BY total_rows DESC
The misconfiguration of S3 buckets is a major threat vector. If an open bucket is detected that was not intended to be world readable, it is of critical importance to understand if there were any inappropriate accesses. This query will collect and rank all IP addresses accessing the bucket of interest. These should be reviewed to determine if any are outside your organization (if so, you may have had a data leak).
SELECTremoteip,count(1) AS total_rowsFROM panther_tables.aws_s3serveraccessWHEREyear=2020 AND month=1ANDbucket='somebucket'GROUP BY remoteipORDER BY total_rows DESC
This query will characterize activity by UserAgent over ALB and Nginx logs. This can be useful in an investigation, if an actor has a known set of characteristic UserAgents.
SELECTuseragent,sum(row_count) AS total_rowsFROM (SELECTuseragent,count(1) AS row_countFROM panther_tables.aws_albWHERE year=2020 AND month=1 AND day=31GROUP BY useragentUNION ALLSELECThttpuseragent AS useragent,count(1) AS row_countFROM panther_tables.nginx_accessWHERE year=2020 AND month=1 AND day=31GROUP BY httpuseragent)GROUP BY useragentORDER BY total_rows DESC