topic Re: HELP! Merging results from a search into a bar chart in Splunk Search

HELP! Merging results from a search into a bar chart

leomedina — Tue, 29 Sep 2020 13:34:50 GMT

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%

Re: HELP! Merging results from a search into a bar chart

leomedina — Fri, 07 Apr 2017 09:40:47 GMT

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.

Re: HELP! Merging results from a search into a bar chart

gcusello — Fri, 07 Apr 2017 09:44:20 GMT

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

Re: HELP! Merging results from a search into a bar chart

woodcock — Sat, 08 Apr 2017 03:31:17 GMT

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

Re: HELP! Merging results from a search into a bar chart

leomedina — Mon, 10 Apr 2017 02:13:13 GMT

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

Re: HELP! Merging results from a search into a bar chart

gcusello — Mon, 10 Apr 2017 07:49:13 GMT

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

Re: HELP! Merging results from a search into a bar chart

leomedina — Mon, 10 Apr 2017 19:02:06 GMT

Hi there...

Please see my comment above...

Re: HELP! Merging results from a search into a bar chart

leomedina — Mon, 10 Apr 2017 19:05:50 GMT

Hi Giuseppe,

That didn't work either.

Re: HELP! Merging results from a search into a bar chart

leomedina — Tue, 29 Sep 2020 13:35:38 GMT

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... 😕

Re: HELP! Merging results from a search into a bar chart

gcusello — Tue, 11 Apr 2017 10:06:39 GMT

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

Re: HELP! Merging results from a search into a bar chart

woodcock — Sat, 22 Apr 2017 21:59:52 GMT

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

Re: HELP! Merging results from a search into a bar chart

leomedina — Wed, 03 May 2017 17:32:49 GMT

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.

Re: HELP! Merging results from a search into a bar chart

woodcock — Wed, 03 May 2017 19:18:58 GMT

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

Re: HELP! Merging results from a search into a bar chart

leomedina — Wed, 03 May 2017 19:39:45 GMT

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

Re: HELP! Merging results from a search into a bar chart

DalJeanis — Wed, 03 May 2017 19:55:28 GMT

Just add this before your stats commands

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

and this after

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

Re: HELP! Merging results from a search into a bar chart

woodcock — Fri, 12 May 2017 04:53:18 GMT

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