Archive

Joining two queries with same field name , but different values

Path Finder

I am trying to create an alert which will check how many messages are stuck in the queue and whats the age of messages.

Problem is , field for checking the number of messages and age of messages is same i.e. metric_dimensions.

Can someone guide how can we join these two fields with same name , but different values?

0 Karma

Ultra Champion
index="sqs" sourcetype="aws:cloudwatch" source="AWSSQS" account_id="#####"  
AND (metric_name="ApproximateNumberOfMessagesVisible" OR metric_name="ApproximateAgeOfOldestMessage" )
| stats count(eval(metric_name="ApproximateNumberOfMessagesVisible")) as Sum_first count(eval(metric_name="ApproximateAgeOfOldestMessage")) as Sum_second by queuename
| where Sum_A>0 AND Sum_B > 900

see https://conf.splunk.com/files/2016/slides/power-of-spl.pdf
Searching at same time, this is Splunk way.

0 Karma

Ultra Champion
index="sqs" sourcetype="aws:cloudwatch" source="AWSSQS" account_id="#####"  
 AND (metric_name="ApproximateNumberOfMessagesVisible" OR metric_name="ApproximateAgeOfOldestMessage" )`

is search two metric_name.

 | stats count(eval(metric_name="ApproximateNumberOfMessagesVisible")) as Sum_first count(eval(metric_name="ApproximateAgeOfOldestMessage")) as Sum_second by queuename

By each queuename and metric_name, aggregate .

No, still not working..
what's is this?
no results? count is wrong? what's the result?

I see you like join . ok, as you like. I'm sorry waste your time.

0 Karma

Path Finder

I will test again...will provide you detailed report.

Thanks for your help ...

0 Karma

Path Finder

Thanks to4kawa for your query . It seems to be useful.

However Stats need to be based on the Sum field instead of metric_dimensions.... how can we include Sum for both metric_dimensions?

0 Karma

Path Finder

No, still not working..

I think join can do by passing the result to main query...

0 Karma

Ultra Champion

| stats count as Sum count(eval(metric_name="ApproximateNumberOfMessagesVisible")) as Sum_first count(eval(metric_name="ApproximateAgeOfOldestMessage")) as Sum_second by queuename

0 Karma

Ultra Champion

join
How do you want to display results?

0 Karma

Path Finder

I want to combine following two queries:-

index="sqs" sourcetype="aws:cloudwatch" source="AWSSQS" account_id="#####" metric_name="ApproximateNumberOfMessagesVisible" | table queuename | where Sum>0

index="sqs" sourcetype="aws:cloudwatch" source="AWSSQS" account_id="#####" metric_name="ApproximateAgeOfOldestMessage" | table queuename | where Sum>900

First query should run if second query satisfies the where condition...

0 Karma