Splunk Search

stats count when a given when A > some value AND B > some value

man03359
Communicator

Hi Everyone,

Hope everyone is alright. 

I have the below base search. I am trying to built an alert

index=idx-cloud-azure "*09406b3b-b643-4e86-876e-4cd5f5a8be57*" metricName=MemoryPercentage OR metricName=CpuPercentage

This is the below condition which I have to follow- 

CPUPercentage > 85 MemoryPercentage > 85, where CPUPercentage and MemoryPercentage are values of a field called metricName.

I am doing like this -

index=idx-cloud-azure "*09406b3b-b643-4e86-876e-4cd5f5a8be57*"
| eval metricCount=if((metricName="MemoryPercentage" OR metricName="CpuPercentage"),1,0)
| stats count by metricCount
| where MemoryPercentage > 85 OR CpuPercentage > 85

not sure if this is correct way to do.

Could anyone pls suggest a better way.

Thanks in advance 🙂

Labels (4)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Without knowing what your events look like, it is difficult to say whether this is right or not. For example, do CPUPercentage and MemoryPercentage appear in the same event?

Having said that "stats count by metricCount" is almost certainly wrong as all you will be left with a single field called count so the where command will always be false.

Also, you title suggests an AND condition but your where command is using an OR condition. This is a bit confusing.

Please clarify what you are trying to do and include some (anonymised) representative sample event, preferably unformatted in a code block </>

0 Karma

man03359
Communicator

@ITWhisperer 

The logs look like this -

 amdl_ResourceGroup: PLANALLOC-GSAS-NONPROD-IACD01-EUS2-GSAS-RG
   amdl_ResourceName: ALLOC-EUS2-IACD01-GSAS-WINASP01
   amdl_ResourceType: MICROSOFT.WEB/SERVERFARMS
   amdl_SubscriptionId: 09406B3B-B643-4E86-876E-4CD5F5A8BE57
   average: 0
   count: 1
   maximum: 0
   metricName: CpuPercentage
   minimum: 0
   resourceId: /SUBSCRIPTIONS/09406B3B-B643-4E86-876E-4CD5F5A8BE57/RESOURCEGROUPS/PLANALLOC-GSAS-NONPROD-IACD01-EUS2-GSAS-RG/PROVIDERS/MICROSOFT.WEB/SERVERFARMS/ALLOC-EUS2-IACD01-GSAS-WINASP01
   time: 2023-11-06T11:38:00Z
   timeGrain: PT1M
   total: 0
0 Karma

man03359
Communicator

@ITWhisperer 

CPUPercentage and MemoryPercentage are the field values of a field called metricName.

The condition for the alert is -

when the count of CPUPercentage > 85 and MemoryPercentage > 85, it should trigger an alert.

 

Please refer to the screenshot attached.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This will give you what you have asked for, although I am not sure of the value of it as if your timeframe is wide enough, you will eventually get more than 85 events of each metric.

index=idx-cloud-azure "*09406b3b-b643-4e86-876e-4cd5f5a8be57*"
| chart count by index, metricName
| where CpuPercentage > 85 AND MemoryPercentage > 85

0 Karma

inventsekar
SplunkTrust
SplunkTrust

Hi @man03359 .. the metricName can be either CPUPercentage or MemoryPercentage. 

and then, how do you get the value of either CPUPercentage or MemoryPercentage

 

or.. if you have the values for either CPUPercentage or MemoryPercentage.. then you should be able to run:

index=idx-cloud-azure "*09406b3b-b643-4e86-876e-4cd5f5a8be57*"
| chart count by index, metricName
| where CpuPercentage > 85 AND MemoryPercentage > 85

 when you run this Search query, do you get results as you expected ah.. if yes, then you can save it as an alert. 

Please let us know if this about search works fine.. if its not working, pls update us how to get the values of either cpu or memory percentage. thanks. 

0 Karma

man03359
Communicator

@ITWhisperer 

The base search is -

index=idx-cloud-azure "*09406b3b-b643-4e86-876e-4cd5f5a8be57*"

and the logs with this search looks like- 

amal_ResourceGroup: PLANALLOC-GSAS-NONPROD-D01-EUS2-GSAS-RG
   amal_ResourceName: ALLOCD01GSASTENANTCOSMOSDBACCOUNT01 - COSMOSDB ACCOUNT ADMIN OPERATION
   amal_ResourceType: MICROSOFT.INSIGHTS/ACTIVITYLOGALERTS
   amal_SubscriptionId: 09406B3B-B643-4E86-876E-4CD5F5A8BE57
   caller: Microsoft.Insights/ActivityLogAlerts
   category: Alert
   correlationId: 6132ca53-ed10-4f13-8c2a-5496dd7decde
   identity: { [+]
   }
   level: Informational
   location: global
   operationName: Microsoft.Insights/ActivityLogAlerts/Activated/action
   properties: { [+]
   }
   resourceId: /subscriptions/09406b3b-b643-4e86-876e-4cd5f5a8be57/resourceGroups/planalloc-gsas-nonprod-d01-eus2-gsas-rg/providers/microsoft.insights/activityLogAlerts/allocd01gsastenantcosmosdbaccount01 - CosmosDB Account Admin Operation
   resultDescription: Alert: allocd01gsastenantcosmosdbaccount01 - CosmosDB Account Admin Operation called on action groups : alloceus2d01ag01
   resultType: Succeeded
   time: 2023-11-06T11:53:58.8277854Z

I have a field called "metricName" one of those values are CpuPercentage , MemoryPercentage etc. listed in the image

man03359_0-1699271968838.png

So I am filtering my search with the metricName like this - 

index=idx-cloud-azure "*09406b3b-b643-4e86-876e-4cd5f5a8be57*" metricName="MemoryPercentage" OR metricName="CpuPercentage"

The condition for the alert is -

when the count of CPUPercentage > 85 and MemoryPercentage > 85, it should trigger and alert.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

OK, so use the search I gave you for your alert and trigger when there are results.

But, again, I am not sure how useful this is. What are you actually trying to achieve?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

So, each event has a metricName?

Which value are you comparing to 85, or are you just looking to count the different metricNames and see if you have more than 85 of both?

0 Karma

man03359
Communicator

@ITWhisperer 

Yes, each event has metricName, listed like this-

man03359_0-1699872352470.png

CpuPercentage and MemoryPercentage are one of the values of metricName.

The query has to be built in such a way that it calculates the % of CPU utilization and throws an alert when the CPUPercentage is more than 85%., similarly for MemoryPercentage also.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This doesn't really answer the question.

How about this (to try and clarify what your events mean): Is the count always 1?

If so, it appears that average, minimum, maximum and total will always be the same number, right? That is, any one of them could be used as the value for the event?

If not, which value do you want to use as the value for the event?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...