topic Re: Splunk query to display count based on message in Splunk Search

Splunk query to display count based on message

nanoo1 — Mon, 20 Dec 2021 09:46:01 GMT

Hi,

I need a help with a query to display the count based on a particular message. For example, "Failed project on ABC", the query basically should read and count 2 and if it's greater than 2 , should display the number

I tried something like this, but not working

index="Project" | stats count(eval(message like("%Failed Project on%")) | where count>2

Could someone suggest way of achieving this?

/nanoo1

Re: Splunk query to display count based on message

Random_Walk — Mon, 20 Dec 2021 09:50:58 GMT

This should do the trick ...

index=Project "Failed Project on"
| stats count AS Total
| search Total > 2

Re: Splunk query to display count based on message

nanoo1 — Mon, 20 Dec 2021 14:44:37 GMT

I tried above and it is working but not I expected.

| stats count As Total -> it is counting the number of occurrences like 2,1,1

| search Total > 2 -> it is displaying overall value

For the below table if you see, and above query, it should not display any event as there is no data with >2

but it is displaying 4

Project	Failed Count
ITC029	2
ITC1034	1
ITC1035	1

Re: Splunk query to display count based on message

Random_Walk — Mon, 20 Dec 2021 15:44:20 GMT

Those results look like you have done a 'group by Project ' or similar with a Project field, like.

index=Project "Failed Project on"
| rex "Failed Project on (<?Project>\w+)
| stats count As Total BY Project
| search Total > 2

What is your exact search string? What fields have you extracted?

Re: Splunk query to display count based on message

nanoo1 — Mon, 20 Dec 2021 16:20:06 GMT

I apologize for lack of clarity here

I have a field "objectName" which refers to different projects like IT256, IT345 and so on and "message" field which shows messages like "Failed project on <objectname>" .

requirement is, say 3 times there had been a failure occurred and 4th time it should generate an incident.

This applies to each of the objectName here is ,

ex IT256 failed 2 times- as the count is 2 -don't generate incident

IT345 failed 4 times - as the count is greater than 3 generate an incident.

Hope this helps.

Re: Splunk query to display count based on message

nanoo1 — Mon, 20 Dec 2021 16:51:46 GMT

Just to add more here, here is complete search:

| from datamodel:"Project_job_events"| where clusterName=="ITS07-SD02A" | where eventStatus=="Failure" | table _time,objectName,message,locationName,,eventStatus,objectType,objectId,_raw

I did below for each fields,

| eval json_field=split(_raw,",")
| eval field1=mvindex(json_field,1)

| eval
itsi_entity=objectName,
itsi_event_key=objectId,
itsi_correlation_key=objectId,
message=message,
itsi_message="Alerting time: "+human_readable_time+"~~"+field1+"~~"+field2+"~~"+field3+"~~"+field4+"~~"+field5+"~~"+field6+"~~"+field7+"~~"+field8,

itsi_impact=case(
message like("%Failed project %") | ,"High"
message like("%Failed Compliance Project%"),"High",
true(), "Medium"),
itsi_urgency=case(
message like("%Failed project %"), "High",
message like("%Failed Compliance project%"), "High",
true(),"Medium")

Requirement - For a message like "Failed project" , the search should basically count for 3 times failure and then send an alert. The below search works when we run index and so on but when try to keep in eval statement it does not. Moreover I tried keeping above itsi_impact and inside of that as well, still no luck