merging data from 2 separate queries

[butsch100]

All, 

I have 2 separate queries working from AWS Description data that we collect on a regular basis.

The ask from one of our portfolio leads is to send them a report on a weekly basis (Monday) that includes the following information.

For all AWS EC2 instances which are in a stopped state include the following:

account_name = this is just a lookup field matching our account numbers to a human readable name

EC2 instance ID

who owns the instance (a tag applied to the instance)

date the instance was stopped - this is in the "reason" field from AWS Description data.

how much storage is attached to the stopped instance (a total amount)

Right now I have 2 separate queries that results in all of the data that I need but I need to find out a way to merge the two sets of data together into one report.

Search #1:  this gets the stopped instance IDs, who owns it (an applied tag), what account it is in, the region, the instance Name (an applied tag) and the reason it was stopped.

index=awsdescription source=*ec2_instances  state=stopped | dedup id |  rename tags.Name as Name | rename tags.Owner as Owner  |rename id as instance_id | table account_name, region, Name,  instance_id, Owner, reason

Search #2: uses the above search as a sub-search, just pulling out the instance IDs. Then bounces that list off of a different source (*ec2_volumes) to grab the list of volumes associated with the instances that are stopped.  The results are then aggregated (stats sum) to get a total amount of storage attached to each of the stopped instances.

index=awsdescription* source=*ec2_volumes [search index=awsdes* source=*ec2_instances  state=stopped | dedup id | rename id as attach_data.instance_id | fields attach_data.instance_id] | rename attach_data.instance_id as instance_id |dedup id |stats sum(size) by instance_id

The two searches combined gives me all of the data that I need but it is in 2 separate reports.  From here, I have to download the results of each, throw them into a spreadsheet and merge the two sets of data (using vlookup on the instance_id) into a single report before I send it off to the customer.

Is there a way to combine these two searches?  If so, I would love some guidance.

Thanks in advance

[richgalloway · Accepted solution]

There are a few ways to combine two or more searches.  See https://docs.splunk.com/Documentation/Splunk/8.2.3/SearchReference/Join#Alternative_commands for a good overview of them.

I like to use the append command to run separate queries then use stats to merge the results.

index=awsdescription source=*ec2_instances  state=stopped 
| dedup id 
| rename tags.Name as Name, tags.Owner as Owner, id as instance_id 
| append [ search index=awsdescription* source=*ec2_volumes 
  [search index=awsdes* source=*ec2_instances  state=stopped 
    | dedup id 
    | rename id as attach_data.instance_id 
    | fields attach_data.instance_id] 
  | rename attach_data.instance_id as instance_id 
  | stats sum(size) as TotalSize by instance_id
| stats values(*) as * by instance_id
| table account_name, region, Name,  instance_id, Owner, reason, TotalSize