Splunk Search

HELP! Merging results from a search into a bar chart

leomedina
Explorer

Hello,

I am trying to merge/concatenate the results of a field with a wild card into one. Your help is greatly appreciated.

Sample query:

index=tibco host=kewlbox OR host=QAbox InterfaceName="data*" OR InterfaceName="from*" OR InterfaceName="tibco*"
| stats count as Success by InterfaceName, host

| append [search index=tibco host=kewlbox OR host=QAbox InterfaceName="data*" OR InterfaceName="from*" OR InterfaceName="tibco*" [error]
| stats count as Errors by InterfaceName, host ]

| stats values(Success) as Success, values(Errors) as Errors by InterfaceName, host
| fillnull Success, Errors | addtotals | eval "Success%"=round((Success/Total)*100,2)

| table InterfaceName, Success, Errors | sort - Success%

0 Karma

woodcock
Esteemed Legend

Like this:

| makeresults | eval raw="data 213::data1 43::data2 125::from 32::from-1 09::tibco5 4::tibco3 7"
| makemv delim="::" raw
| mvexpand raw
| rex field=raw "^(?<InterfaceName>\S+)\s+(?<Count>\S+)$"
| fields - _time raw

| rename COMMENT AS "Everything above fakes sample (intermediate) events; everything below is your solution"
| rename COMMENT AS "YOUR BASE SEARCH GOES HERE"

| rex field=InterfaceName mode=sed "s/[\d-]*$//"
| stats sum(Count) AS Count BY InterfaceName
0 Karma

DalJeanis
Legend

Just add this before your stats commands

| eval InterfaceName=substr(InterfaceName,1,4)

and this after

| eval InterfaceName=if(InterfaceName="tibc","tibco",InterfaceName)
0 Karma

woodcock
Esteemed Legend

To move from this output:

InterfaceName Count
data 213
data1 43
data2 125
from 32
from-1 09
tibco5 4
tibco3 7

To this output:

data = 381 from = 41 and tibco = 11

Do this:

| makeresults 
| eval raw="data 213::data1 43::data2 125::from 32::from-1 09::tibco5 4::tibco3 7" 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "^(?<interfaceName>\S+)\s+(?<Count>\S+)$" 
| table interfaceName Count

| rename COMMENT AS "Everything above fakes your event data; everything below is your solution"

| rex field=interfaceName mode=sed "s/[\-\d]+//g" 
| eval _time = now() 
| chart sum(Count) AS Count OVER _time BY interfaceName 
| fields - _time
0 Karma

leomedina
Explorer

Hi!

Thanks for the great input and help. But what if I don't want the output data to be fake?

When I tried the above I wasn't getting any results.

| makeresults 
| eval raw="CustAcctSvcAsync-CustAcctAsyncPA::CCSubscrAsyncSvc-CCSubscriptionSvcPA::CCSubscrAsyncSvc-CCSubscriptionSvcPA-1::CDMSvcAsync-CDMSvcAsyncPA::CDMSvcAsync-1-CDMSvcAsyncPA::CDMSvcAsync-HeathCheck::CDMSvcAsync-1-HeathCheck-1::CDMSvcAsync-RetryPA::CDMSvcAsync-1-RetryPA-1::CDMSvcAsync-CDMSvcAsyncPA-1::CDMSvcAsync-1-CDMSvcAsyncPA-1" 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "^(?<InterfaceName>\S+)\s+(?<Count>\S+)$" 
| table InterfaceName, Count
| rename COMMENT AS "Everything above fakes your event data; everything below is your solution"
| rex field=InterfaceName mode=sed "s/[\-\d]+//g" 
| eval _time = now() 
| chart sum(Count) AS Count OVER _time BY InterfaceName 
| fields - _time

Ultimately I'd like to merge the corresponding fields together (i.e. CCSubscriptionSvcPA with CCSubscriptionSvcPA -1).

Not sure why I'm having such a difficult time with this one when it's worked with other things such as host.

Greatly appreciate the advice.

0 Karma

woodcock
Esteemed Legend

You need to replace all the stuff before and including the rename line with your base search and then it should work.

0 Karma

leomedina
Explorer

Nope 😕

index=tibco environment=Dev InterfaceName="CCSubscrAsyncSvc*" OR InterfaceName="CustAcctSvcAsync*" OR InterfaceName="CDMSvcAsync-CDMSvcAsyncPA*" OR InterfaceName="CDMSvcAsync-RetryPA*"
| rex field=InterfaceName mode=sed "s/[\-\d]+//g" 
| eval _time = now() 
| chart sum(Count) AS Count OVER _time BY InterfaceName 
| fields - _time

I went back 30 Days and it came back with No Results this time...

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi leomedina,,
I hope to had understood your need, try this:

index=tibco host=kewlbox OR host=QAbox InterfaceName="data*" OR InterfaceName="from*" OR InterfaceName="tibco*"
| eval InterfaceName=case(InterfaceName="data*","data", InterfaceName="from*", "from", InterfaceName="tibco*","tibco")
| stats count as Success by InterfaceName, host 
| append [search index=tibco host=kewlbox OR host=QAbox InterfaceName="data*" OR InterfaceName="from*" OR InterfaceName="tibco*" [error] 
| eval InterfaceName=case(InterfaceName="data*","data", InterfaceName="from*", "from", InterfaceName="tibco*","tibco")
| stats count as Errors by InterfaceName, host ] 
| stats values(Success) as Success, values(Errors) as Errors by InterfaceName, host 
| fillnull Success, Errors | addtotals | eval "Success%"=round((Success/Total)*100,2) 
| table InterfaceName, Success, Errors | sort - Success%

Bye.
Giuseppe

0 Karma

leomedina
Explorer

Hi Giuseppe,

That didn't work either.

0 Karma

leomedina
Explorer

Actual script:

index=tibco host=tus3eaiapppin22 OR host=tus3eaiapppin19 InterfaceName="CustAcctSvcAsync*" OR InterfaceName="CCSubscrAsyncSvc*" OR InterfaceName="CDMSvc*"
| eval ("status":"SUCCESS") OR ("ended successfully")="Success"

| eval (SYS_ERR_27001) OR (SYS_ERR_27002) OR (SYS_ERR_27004) OR (SYS_ERR_27011) OR (SYS_ERR_27012)="Errors"
| stats count as Success by InterfaceName, host

| append [search index=tibco host=tus3eaiapppin22 OR host=tus3eaiapppin19 InterfaceName="CustAcctSvcAsync*" OR InterfaceName="CCSubscrAsyncSvc*" OR InterfaceName="CDMSvc*" [error]
| stats count as Errors by InterfaceName, host ]

| stats values(Success) as Success, values(Errors) as Errors by InterfaceName, host
| fillnull Success, Errors | addtotals | eval "Success%"=round((Success/Total)*100,2)

| table InterfaceName, Success, Errors | sort - Success%

I also noticed that the query is only giving me the count of on Success... If I change the | stats count as Success by InterfaceName, host to | stats count as Errors by InterfaceName, host the same data is then moved to the errors column... 😕

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi leomedina,
your evals are wrong:
syntax is

| eval fieldname=case(fieldname="case1",value1,fieldname="case2",value2,fieldname="case3",value3)

(see http://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/Eval)

so I don't know if the fieldname is InterfaceName and which are the fileds in your conditions, so I use this field, if I'm wrong change fieldname and conditions:

...
| eval InterfaceName=if(status="SUCCESS" OR status="ended successfully","Success")
| eval InterfaceName=if(InterfaceName="SYS_ERR_27001" OR InterfaceName="SYS_ERR_27002" OR InterfaceName="SYS_ERR_27004" OR InterfaceName="SYS_ERR_27011" OR InterfaceName="SYS_ERR_27012","Errors" )
...

Bye.
Giuseppe

0 Karma

woodcock
Esteemed Legend

Break this apart line by line from the bottom up and you should be able to build what you need:

index=tibco host=kewlbox OR host=QAbox InterfaceName="data*" OR InterfaceName="from*" OR InterfaceName="tibco*"
| stats count AS Total count(eval(searchmatch("[error]"))) AS Errors BY InterfaceName host
| eval Success = Total - Errors
| stats sum(*) AS * BY InterfaceName
0 Karma

leomedina
Explorer

Hi there...

Please see my comment above...

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi leomedina,
I don't understand your need:
this search seems to be correct, what is the additional result you like?
what is the field with a wild card you are sayng?
Note that in the table command you forgot host!
Bye.
Giuseppe

0 Karma

leomedina
Explorer

Hi Giuseppe,

Please see my "almost forgot" comment above... I am attempting to reconcile a number of interface outputs/returns into a single individual output (eg. InterfaceName=data* returns data for several interface names data213, data 1 data2. I want to see the total of these in one simple output as "data ===bar graph=== count".

Greatly appreciate your help in advance.

Kind regards,

Leo

0 Karma

leomedina
Explorer

Almost forgot... Sample output today:

InterfaceName Count
data 213
data1 43
data2 125
from 32
from-1 09
tibco5 4
tibco3 7

Data that I would like to see is a compilation of everything named data into one with all of the values added; same for from and tibco. So at the end of the day we'll have data = 381 from = 41 and tibco = 11

Thanks again in advance.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...