topic Re: How to count success/fail event and group them by another field in Splunk Search

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

someguy73 — Fri, 29 Dec 2017 09:37:57 GMT

Hello everyone!

My data have this form

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

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

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

someguy73 — Fri, 29 Dec 2017 11:35:34 GMT

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

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

someguy73 — Fri, 29 Dec 2017 11:38:03 GMT

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

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

somesoni2 — Fri, 29 Dec 2017 15:13:30 GMT

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?

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

someguy73 — Tue, 09 Jan 2018 08:56:44 GMT

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.