Enrich Splunk events with Steampipe
Splunk lookup tables can enrich AWS event data with IP-address/name mappings not available in CloudTrail. Here's how to build those tables with Steampipe.
When analyzing telemetry from AWS in a security operations role, context is key. What is this instance i-7ba5bed288a? Is this random AWS-owned IP address one of mine, or does it belong to someone else? Which account is 178901234562 again? AWS doesn't provide any of this context in CloudTrail or GuardDuty.
If you use Splunk as your Security Event and Incident Management (SEIM) platform, you've probably heard of Lookups. Per Splunk: "Lookups enrich your event data by adding field-value combinations from lookup tables". Lookups can be a great way to improve your detection and investigations by adding attributes and key business context to your CloudTrail, GuardDuty, and VPC Flow Log data.
Steampipe can pull that context into Splunk lookup tables. In a pair of examples, for AWS Accounts and Elastic Network Interfaces, we'll use Steampipe to query AWS Accounts and public and private IP addresses of Elastic Network Interfaces. We'll save that data as Splunk lookup tables. The Steampipe SQL queries and the Splunk SPL queries provided here are examples you can build upon to create your own enrichment tables.
Context enrichment with Steampipe
Steampipe is an open source project that provides a common interface that enables you to query cloud APIs with SQL. WIth Steampipe and the AWS Plugin installed and configured, you can easily run SQL queries against AWS APIs represented as database tables, and export the results to CSV files that load into Splunk as lookup tables.
Let's start with a simple example: a list of all AWS Accounts in an organization. This query (accounts.sql) pulls the twelve-digit account id, Account Name, Status (Active or Suspended), and four specific tags on each account.
select id, name, status,tags ->> 'ExecutiveOwner' as Executive_Owner,tags ->> 'TechnicalContact' as Technical_Contact,tags ->> 'DataClassification' as Data_Classification,tags ->> 'environment' as Environmentfrom aws_payer.aws_organizations_account;
Here's the command to extract this information into a CSV file.
steampipe query accounts.sql --output csv > sp_aws_accounts.csv
The command extracts the account information from AWS Organizations into a CSV file that's ready to be used by Splunk.
id,name,status,executive_owner,technical_contact,data_classification877426665359,fooli-dev,ACTIVE,Richard Hendricks,Dinesh Chugtai,Public352894534996,fooli-security,ACTIVE,Bertram Gilfoyle,Bertram Gilfoyle,Internal152981771857,fooli-prod,ACTIVE,Erlich Bachman,Richard Hendricks ,PersonalInformation540147993428,fooli-payer,ACTIVE,Monica Hall,Bertram Gilfoyle,None747037951011,fooli-memefactory,ACTIVE,Erlich Bachman,Richard Hendricks,PersonalInformation755629548949,fooli-sandbox,ACTIVE,Erlich Bachman,Dinesh Chugtai,None
One of the most useful Splunk lookup tables maps from internal or external IP addresses to the cloud resources they belong to. This Steampipe query gets all of the ENIs in your environment. It joins that data with your account and VPC data to provide account and VPC names. Finally, it populates the
attached_resource column with either the EC2 Instance ID or the ENI Description to tell you which resource each public or private IP address belongs to.
selecteni.network_interface_id,eni.private_ip_address,eni.vpc_id as vpc_id,eni.region,eni.status,eni.interface_type,eni.association_public_ip as public_ip,casewhen eni.attached_instance_id is not nullthen eni.attached_instance_idelse eni.descriptionend as attached_resource,vpc.tags ->> 'Name' as vpc_name,org.name as account_namefromaws_ec2_network_interface as eni,aws_vpc as vpc,aws_payer.aws_organizations_account as orgwhere vpc.vpc_id = eni.vpc_idand org.id = eni.account_id;
steampipe query eni.sql --output csv > sp_eni.csv to generate a CSV file like:
network_interface_id,private_ip_address,vpc_id,region,status,interface_type,public_ip,attached_resource,vpc_name,account_nameeni-0bb119c9b8271e13c,10.12.15.127,vpc-0baccbf3534d6a80c,us-east-1,in-use,interface,<null>,arn:aws:ecs:us-east-1:747037951011:attachment/2d6f0e6e-4e26-4dea-ae2f-7f0eac27d471,Prod VPC,fooli-memefactoryeni-03fb47e928c58f8c6,10.12.11.158,vpc-0baccbf3534d6a80c,us-east-1,in-use,interface,18.104.22.168,ELB app/prod-FooliApiStack/564122fa59bf64fe,Prod VPC,fooli-memefactoryeni-019de27cb07fe61f2,10.12.11.162,vpc-0baccbf3534d6a80c,us-east-1,in-use,interface,22.214.171.124,i-0a2af645e985e6aed,Prod VPC,fooli-memefactoryeni-0b0a0f4a74f50324b,10.12.15.233,vpc-0baccbf3534d6a80c,us-east-1,in-use,lambda,<null>,AWS Lambda VPC ENI-prod-FooliMailerStack-mailer-13aa6e45-264b-46cd-a91b-17cc79e7a011,Prod VPC,fooli-memefactoryeni-0d55432870ac355fc,10.12.11.119,vpc-0baccbf3534d6a80c,us-east-1,in-use,interface,126.96.36.199,i-0eeacb7ba5bed288a,Prod VPC,fooli-memefactoryeni-0dc96ad6321864543,10.12.11.151,vpc-0baccbf3534d6a80c,us-east-1,in-use,interface,188.8.131.52,RDSNetworkInterface,Prod VPC,fooli-memefactoryeni-06741026048ceb699,10.12.10.106,vpc-0baccbf3534d6a80c,us-east-1,in-use,interface,184.108.40.206,ELB app/prod-FooliApiStack/564122fa59bf64fe,Prod VPC,fooli-memefactoryeni-03831a874fce92713,10.12.10.104,vpc-0baccbf3534d6a80c,us-east-1,in-use,nat_gateway,220.127.116.11,Interface for NAT Gateway nat-0038ea0ba69861382,Prod VPC,fooli-memefactoryeni-0cb11b13ee1ded995,10.10.11.215,vpc-0c320ebb500ab616a,us-east-1,in-use,interface,18.104.22.168,i-08154eb5935852d50,Dev VPC,fooli-deveni-0265d9a496ff2b01e,10.10.10.218,vpc-0c320ebb500ab616a,us-east-1,in-use,interface,22.214.171.124,ELB app/dev-FooliApiStack/6fb5790bfca6ea6f,Dev VPC,fooli-deveni-074aac46384fe2501,10.10.10.217,vpc-0c320ebb500ab616a,us-east-1,in-use,nat_gateway,126.96.36.199,Interface for NAT Gateway nat-031b431e19aa13518,Dev VPC,fooli-deveni-0f45b8f63195340c5,10.10.11.66,vpc-0c320ebb500ab616a,us-east-1,in-use,interface,188.8.131.52,i-0d1bfdfc785de0619,Dev VPC,fooli-deveni-0bf128705f3c36d94,10.8.10.56,vpc-0da7e4ff31018985b,us-east-1,in-use,interface,<null>,i-06944d48a6262f7f9,SecurityVPC,fooli-security
You can now use this lookup table with either the
private_ip or the
How Splunk lookups can simplify your life and help find threats in your environment.
Let's start with a simple example: Who is logging into your AWS accounts via the Web Console? Here we can decorate the ConsoleLogin events with not just the user and IP address, but also include the AWS Account's Name which isn't normally available in CloudTrail.
index="aws_cloudtrail" eventName=ConsoleLogin| LOOKUP sp_aws_accounts.csv id AS recipientAccountIdOUTPUT name as account_name| table account_name, userIdentity.arn, sourceIPAddress
Steampipe-generated lookup tables also work well when you're searching VPC Flow Logs. Here we can cross-reference the FlowLog record
dvc (device) with a lookup table of all the network devices to get the names of the resources that this target IP address was talking to.
index=aws_flowlogs 184.108.40.206| LOOKUP sp_eni.csv network_interface_id AS dvcOUTPUT attached_resource as attached_resource, vpc_name as VPC, account_name as Account| stats count by attached_resource, VPC, Account
One final example: Using ENI data to cross-reference where an EC2 Instance Role is coming from:
index=aws_cloudtrail "userIdentity.arn"="arn:aws:sts::*:assumed-role/payments_role/*"| LOOKUP sp_eni.csv public_ip AS sourceIPAddressOUTPUTNEW attached_resource as attached_resource| fillnull attached_resource value="External IP"| stats count by sourceIPAddress, eventName, attached_resource
If a role used by an EC2 Instance is generating CloudTrail events from an IP address not part of your VPC, that's a major concern that needs to be investigated. Here we see a role that belongs to an EC2 Instance being used outside from IP Address that are not tied to known EC2 Instances.
Extend your enrichment activities with Steampipe
AWS Accounts and elastic IP addresses are only two examples of event enrichment for your SEIM. If you have a tagging strategy for your EC2 Instances, you can query instance names and contact teams to correlate them with your endpoint detection and response (EDR) alerts.
Your organization is probably polycloud, and so is Steampipe. Plugins exist for all the major cloud providers; you can easily decorate your Azure activity logs and GCP audit logs.
The sample queries here were only examples of the power of Steampipe to enrich logging data. If you try this technique, let us know. We love hearing how practitioners use Steampipe.