Splunk Search

Joining two queries with same field name , but different values

bsaujla131984
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

to4kawa
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

to4kawa
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

bsaujla131984
Path Finder

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

Thanks for your help ...

0 Karma

bsaujla131984
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

bsaujla131984
Path Finder

No, still not working..

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

0 Karma

to4kawa
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

to4kawa
Ultra Champion

join
How do you want to display results?

0 Karma

bsaujla131984
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...