Splunk Search

How to count success/fail event and group them by another field

someguy73
Explorer

Hello everyone!

My data have this form
alt text

I'm trying to make table in splunk, that will aggregate data to next format:


name            from        to              Status      Total_Success      Total_fail
KFI.Database    perun1      10.621.20.32            success        15               0

But my search don't work ( server sent me JSON file)

source="tcp:8080" index="qfi_sandbox_business"
| spath 
| rename message AS condition
| rename message AS to 
| eval a=mvzip(Type,condition)
| eval b=mvzip(environment,condition)
| eval x=mvzip(a,b)
| mvexpand x
| eval x=split(x, ",")
| eval condition=mvindex(x,1)
| eval to=mvindex(x,2) 
| eval name=mvindex(x,3) 
| chart count as total over name by MESSAGE="*SUCCESS*"  

( if i start search without capital letters ( by MESSAGE="SUCCESS") , its run perfectly, but count all event, when I want count separately FAIL and SUCCESS. When i start in that combination it show a error )

Also I have little bit another search:

source="tcp:8080" index="qfi_sandbox_business"
| spath 
| rename message AS condition
| rename message AS condition2 
| eval a=mvzip(Type,condition)
| eval b=mvzip(environment,condition)
| eval x=mvzip(a,b)
| mvexpand x
| eval x=split(x, ",")
| eval condition=mvindex(x,1)
| eval condition2=mvindex(x,2) 
| eval name=mvindex(x,3) 
| table  name, host, condition2, condition

which parse JSON string (every time in different way) and produce table

alt text

So, how to combine that two search and count success and fail ?

0 Karma

somesoni2
Revered Legend

Give this a try

source="tcp:8080" index="qfi_sandbox_business"
| spath 
| rename message.port as port message.status as status message.name as name message.host as to host as from
| eval temp=mvzip(mvzip(mvzip(port, status),name),to)
| table host temp
| mvexpand temp
| rex field=temp "(?<port>[^,]+),(?<status>[^,]+),(?<name>[^,]+),(?<to>[^,]+)"
| eval Success=if(status="SUCCESS",1,0)
| eval Failure=if(status!="SUCCESS",1,0)
| stats sum(Success) as Total_Success sum(Failure) as Total_Failure by name from to

Above is missing the Status column. How are you calculating it?

0 Karma

someguy73
Explorer

it seem to be very logical and correct decision, but it still can't find my json string. splunk return empty result like there is no event.
Also I tryied to changed your code ( add commas, delete string "message.host as to host as from", because "host" is not in "message" )
I don't understand your question about calculating Status. About each minutes I receive data from server if it is "success" connection or "fail". And further want to bring statistic for last 15 minutes.

0 Karma

someguy73
Explorer

UPDATE

Change to string chart as total over name by condition and received table, which count correct info. But becouse of JSON parsiring each time in different way it brings me odd information

0 Karma

someguy73
Explorer

column "condition" sometimes have "status success or fail" and sometimes "port":"1521", "port":7051" and so on.

0 Karma
Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...