topic Re: Create Statistic Table Based on Regex in Splunk Search

Create Statistic Table Based on Regex

Wendy — Tue, 10 Aug 2021 01:33:14 GMT

Hi experts, I am new to Splunk and came across this requirement at work.

Requirement:

I want to create a table showing numbers of 2 different versions of recaptcha being successfully and unsuccessfully processed.

Current Log info:

Each event has a field named "msg" which contains many information, including wording like "Exception: recaptcha v 2 validation failure," "Exception: recaptcha v 3 validation failure", "Recaptcha v2 verification: successful", Recaptcha v3 verification: successful" based on different events.

Tasks:
How can I create a regex expression to count number of all exceptions and number of different types of exceptions? Same tasks for successful message, but I can figure it out if someone can help with the previous question?

Thank you.

Re: Create Statistic Table Based on Regex

venkatasri — Tue, 10 Aug 2021 02:06:56 GMT

Hi @Wendy

can you share the sample raw event to write a regex. You can anonymize the critical info if any.

Re: Create Statistic Table Based on Regex

manjunathmeti — Tue, 10 Aug 2021 02:23:06 GMT

hi @Wendy,

You need to extract relevant values in the fields using rex and then use stats to count as per your requirements. Try this:

index=indexname | rex field=msg "(?<message>(Exception:\s)?(?i)Recaptcha\s(?<version>v\s?\d)[\w\W]+(successful|failure))" | eval version=replace(version, "\s+", ""), status=if(match(message, "Exception:"), "FAIL", "SUCCESS") | eventstats count as status_count by status | stats latest(_time) as _time, latest(*) as * count as message_count by message

If this reply helps you, a like would be appreciated.

Re: Create Statistic Table Based on Regex

Wendy — Tue, 10 Aug 2021 13:01:03 GMT

HI @manjunathmeti , that helps. I just need to dissemble your query to understand how it works.

Re: Create Statistic Table Based on Regex

manjunathmeti — Tue, 10 Aug 2021 15:22:28 GMT

1. Extract fields message and version from msg using rex command. Check this slink for detailed regex explanation: https://regex101.com/r/VjmWn6/1/ :

| rex field=msg "(?<message>(Exception:\s)?(?i)Recaptcha\s(?<version>v\s?\d)[\w\W]+(successful|failure))"

2. Remove whitespace in field version. Evaluate status to FAIL/SUCCESS based on message field values:

| eval version=replace(version, "\s+", ""), status=if(match(message, "Exception:"), "FAIL", "SUCCESS")

3. Count FAIL/SUCCESS status. Check this link for details on eventstats command eventstats .
| eventstats count as status_count by status

4. Count events by the messages. Check this link for details on stats command stats .
| stats latest(_time) as _time, latest(*) as * count as message_count by message