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
SplunkTrust
SplunkTrust

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!

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...

Want to Reduce Costs, Mitigate Risk, Improve Performance, or Increase Efficiencies? ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...