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

[someguy73]

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 ?

[somesoni2]

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?

[someguy73]

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.

[someguy73]

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

[someguy73]

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