topic Re: How to group messages based on substring in Splunk Search

How to group messages based on substring

chimuru84 — Fri, 18 Aug 2023 08:30:35 GMT

Hello community. I'm trying to extract information from a string type field and make a graph on a dashboard. In the graph, I want to group identical messages. I encounter difficulties when grouping a type of message that contains information about an id, which is different for each message and respectively for each message it returns a separate value. Ex. message: {"status":"SUCCESS","id":"123456789"}. I use this query:

"source" originalField AND ("SUCCESS" OR "FAILURE") | stats count by originalField

This query groups my fields that contain a FAILURE status, but does not group the SUCCESS ones because they have different IDs. I tried different substrings but it doesn't work. Can someone give me a solution?

Re: How to group messages based on substring

ITWhisperer — Fri, 18 Aug 2023 08:33:55 GMT

Please can you share some anonymised versions of your events as it is unclear what originalField refers to nor whether you already have extracted the JSON fields

Re: How to group messages based on substring

yuanliu — Fri, 18 Aug 2023 08:38:10 GMT

Let me help you format the question so others can easily read

@chimuru84 wrote:
Hello community. I'm trying to extract information from a string type field and make a graph on a dashboard. In the graph, I want to group identical messages. I encounter difficulties when grouping a type of message that contains information about an id, which is different for each message and respectively for each message it returns a separate value. Ex. message: {"status":"SUCCESS","id":"123456789"}. I use this query:
"source" originalField AND ("SUCCESS" OR "FAILURE") | stats count by originalField 
This query groups my fields that contain a FAILURE status, but does not group the SUCCESS ones because they have different IDs. I tried different substrings but it doesn't work. Can someone give me a solution?

The description is very unclear. What is the relationship between "originalField" and SUCCESS or FAILURE, or with the groupby you desired? If you group by originalField, you get a breakdown by originalField. There can be no other outcome. Can you exemplify some events that your search returns, and show the stats result, then explain why that result is not what you need? Maybe illustrate your desired result with that sample dataset.

Re: How to group messages based on substring

chimuru84 — Fri, 18 Aug 2023 08:56:08 GMT

After I use this query, I have a result like this. For different reasons for status failure, I have grouped messages, but for status success, every message is separate because of his Id. And the result looks like a JSON but is type String.

Re: How to group messages based on substring

ITWhisperer — Fri, 18 Aug 2023 09:07:32 GMT

Replace the id in original field with another string

"source" originalField AND ("SUCCESS" OR "FAILURE") | rex mode=sed field=originalField "s/SUCCESS\",\"id\":\"[^\"]+\"/SUCCESS\",\"id\":\"id\"/g" | stats count by originalField

Re: How to group messages based on substring

chimuru84 — Fri, 18 Aug 2023 14:26:47 GMT

It works. Thank you. Now I saw that I have one more kind of success message. How can I group this message too?

Re: How to group messages based on substring

ITWhisperer — Fri, 18 Aug 2023 14:30:14 GMT

"source" originalField AND ("SUCCESS" OR "FAILURE") | rex mode=sed field=originalField "s/SUCCESS\",\"(id|detailedReason)\":\"[^\"]+\"/SUCCESS\"/g" | stats count by originalField

Re: How to group messages based on substring

chimuru84 — Fri, 18 Aug 2023 14:36:59 GMT

with this query messages with success are not grouped. I want to have one group with success and another with success/detailedReason. Is possible that? Thank You

Re: How to group messages based on substring

ITWhisperer — Fri, 18 Aug 2023 14:44:44 GMT

Because you have hidden what your event looks like, it is difficult for me to define a solution that works for you. Please share some sample events, with just the most sensitive parts obscured.

Re: How to group messages based on substring

chimuru84 — Fri, 18 Aug 2023 14:47:50 GMT

This is how 2 success messages looks like. And I want to make 2 groups. For first, 1 query works fine, for second need to add to this query something. Thanks you.

Re: How to group messages based on substring

ITWhisperer — Fri, 18 Aug 2023 15:33:29 GMT

"source" originalField AND ("SUCCESS" OR "FAILURE") | rex mode=sed field=originalField "s/SUCCESS\",\"accountId\":\"[^\"]+\"/SUCCESS\",\"accountId\":\"accountId\"/g" | rex mode=sed field=originalField "s/SUCCESS\",\"detailedReason\":\"[^\"]+\",\"accountId\":\"[^\"]+\"/SUCCESS\",\"detailedReason\":\"detailedReason\",\"accountId\":\"accountId\"/g" | stats count by originalField

Re: How to group messages based on substring

chimuru84 — Fri, 18 Aug 2023 15:37:33 GMT

Thanks a lot!! Everything woks fine!

Re: How to group messages based on substring

chimuru84 — Tue, 14 Nov 2023 09:57:52 GMT

Sorry for late answer. By "\"source\" originalField" I mean field which contains this kind of logs