Merge 2 queries base on id

[shrogers]

Need some assistance from the experts.

I have two queries below which I would like to merge on id.

Query 1

index=aws sourcetype=aws:cloudtrail eventName=RebootInstances | table _time userName sourceIPAddress requestParameters.instancesSet.items{}.instanceId
| rename requestParameters.instancesSet.items{}.instanceId as id

Query 2

index=aws sourcetype=aws:description source="us-east-2:ec2_instances" | table id private_ip_address

I would like the final table fields to be:

time  userName  sourceIPAddress    id   private_ip_address

Any assistance given will be appreciated.

[bowesmana]

Try this

(index=aws sourcetype=aws:cloudtrail eventName=RebootInstances) OR
(index=aws sourcetype=aws:description source="us-east-2:ec2_instances")
| eval id=coalesce(id, 'requestParameters.instancesSet.items{}.instanceId')
| eval _time=if(sourcetype="aws:cloudtrail", _time, null())
| fields _time userName sourceIPAddress private_ip_address id
| stats values(*) as * by id

 It searches both data sets.

• id is then set as either id OR the instanceId in the coalesce statement.
• _time is set to null if it is the description sourcetype, forcing _time to come from the cloudtrail event
• fields are then limited to the ones specified
• stats takes the values of all fields and effectively merges the data on the id field

Hope this helps

[kamlesh_vaghela]

@shrogers 

Can you please try this?

index=aws (sourcetype=aws:cloudtrail eventName=RebootInstances) OR (sourcetype=aws:description source="us-east-2:ec2_instances")
| fields _time userName sourceIPAddress requestParameters.instancesSet.items{}.instanceId id private_ip_address 
| rename requestParameters.instancesSet.items{}.instanceId as id
| stats latest(_time) as _time values(userName) as userName values(sourceIPAddress) as sourceIPAddress values(private_ip_address) as private_ip_address by id
| table time  userName  sourceIPAddress    id private_ip_address

KV 

[shrogers]

Hi Kamlesh,

Thank you for your quick response.

Unfortunately, the time and private_id_address are not being populated.

Regards,

Sheldon