topic Re: How to merge multiple searches and combine the result in a tabular format in Splunk Search

How to merge multiple searches and combine the result in a tabular format

kdulhan — Thu, 31 Aug 2017 15:25:32 GMT

Hi All,

I have the below independent search queries giving the count.

ns=app1 Service='trigger1' id=100 | Search Response | stats counts as "Success Count"
ns=app1 Service='trigger2' id=100 OR 110 | Search Response | stats counts as "Success Count1"

I want to put a table with two columns as:
Success Count Success Count1
XXXXXX YYYYYY

Thank you!

Re: How to merge multiple searches and combine the result in a tabular format

richgalloway — Thu, 31 Aug 2017 15:55:38 GMT

Is "Search Response" the same in both queries?

Re: How to merge multiple searches and combine the result in a tabular format

somesoni2 — Thu, 31 Aug 2017 15:56:20 GMT

Give this a try

ns=app1 (Service='trigger1' id=100) OR (Service='trigger2' id=100 OR id=110)
| chart count over ns by Service
| table trigger1 trigger2 | rename trigger1 as "Success Count" trigger2 as "Success Count1"

Updated per last comment
If your field values for Service and ActNo doesn't contains single quotes

ns=app1 Service='trigger1' id=100 ActNo=* Response
| eval count1=if(AccNo="101",1,0)
| eval count2=if(count1=1,0,1)
| stats sum(count1) as "Success Count" sum(count2) as "Success Count1"

If they do contain single quotes

ns=app1 Service='trigger1' id=100 ActNo=* Response
| eval count1=if(AccNo="'101'",1,0)
| eval count2=if(count1=1,0,1)
| stats sum(count1) as "Success Count" sum(count2) as "Success Count1"

Re: How to merge multiple searches and combine the result in a tabular format

kdulhan — Thu, 31 Aug 2017 16:10:31 GMT

Thanks for the response.

There is a slight change in the above input data as it was missed from my side.

ns=app1 Service='trigger1' id=100 ActNo='101' | Search Response | stats counts as "Success Count"
ns=app1 Service='trigger1' id=100 OR 110 ActNo!='100' | Search Response | stats counts as "Success Count1"

I want to put a table with two columns as:
Success Count Success Count1
XXXXXX YYYYYY

Thank You.

Re: How to merge multiple searches and combine the result in a tabular format

somesoni2 — Thu, 31 Aug 2017 16:16:34 GMT

In 2nd search, is it id=100 OR 110 OR id=100 OR id=110? Both are different as in first one it searches for field id=100 and 110 number in raw data.

Re: How to merge multiple searches and combine the result in a tabular format

cmerriman — Thu, 31 Aug 2017 17:27:00 GMT

also in the second search, is it Service='trigger2', as you had mentioned in the original post?

Re: How to merge multiple searches and combine the result in a tabular format

kdulhan — Thu, 31 Aug 2017 19:06:50 GMT

Data is as below:

ns=app1 Service='trigger1' id=100 ActNo='101' | Search Response | stats counts as "Success Count"
ns=app1 Service='trigger1' id=100 ActNo!='100' | Search Response | stats counts as "Success Count1"

I want to put a table with two columns as:
Success Count Success Count1
XXXXXX YYYYYY

Service='trigger1' only. ActNo can have range of values in second case.

Re: How to merge multiple searches and combine the result in a tabular format

kdulhan — Thu, 31 Aug 2017 19:17:33 GMT

ns=app1 Service='trigger1' id=100 ActNo='101' | Search Response | stats counts as "Success Count"
Returns the count of events with ActNo='101' only and Response indicate and a success response.

ns=app1 Service='trigger1' id=100 OR 110 ActNo!='100' | Search Response | stats counts as "Success Count1"
Returns the count of events with ActNo!='100' which means it can be many and Response indicate and a success response.

I want to put a table with two columns as:
Success Count Success Count1
XXXXXX YYYYYY

Re: How to merge multiple searches and combine the result in a tabular format

somesoni2 — Thu, 31 Aug 2017 19:38:06 GMT

Try update answer.

Re: How to merge multiple searches and combine the result in a tabular format

kdulhan — Thu, 31 Aug 2017 20:20:19 GMT

Thanks, it worked.

Now I have a failed response which has event like

ns=app1 [ErrorResponse] Service='trigger1' id=100.

How can I add it to the above Search in order to get Failed Counts.

Thank You!

Re: How to merge multiple searches and combine the result in a tabular format

somesoni2 — Thu, 31 Aug 2017 20:24:00 GMT

Does the failed response have ActNo field?

Re: How to merge multiple searches and combine the result in a tabular format

kdulhan — Thu, 31 Aug 2017 20:35:07 GMT

No, it is just an [ErrorResponse] that gets logged in logs.

Re: How to merge multiple searches and combine the result in a tabular format

somesoni2 — Thu, 31 Aug 2017 20:41:43 GMT

Give this a try

ns=app1 Service='trigger1' id=100 (ActNo=* Response) OR ([ErrorResponse])
| eval count1=if(searchmatch("[ErrorResponse]"),1,0)
 | eval count2=if(count1=0 AND AccNo="101",1,0)
 | eval count3=if(count1=0 AND count2=0,1,0)
 | stats sum(count1) as "Failed Count" sum(count2) as "Success Count" sum(count3) as "Success Count1"

Re: How to merge multiple searches and combine the result in a tabular format

kdulhan — Thu, 31 Aug 2017 21:16:10 GMT

The events are being captured for ErrorResponse but the count3 is not being calculated.

Re: How to merge multiple searches and combine the result in a tabular format

somesoni2 — Thu, 31 Aug 2017 21:25:45 GMT

May be try by getting rid of square bracket around the ErrorResponse (update both base search and eval for count1.

Re: How to merge multiple searches and combine the result in a tabular format

kdulhan — Thu, 31 Aug 2017 21:30:00 GMT

I tried that as well. IN both cases events are being captured for ErrorResponse but the eval is not working as expected. Can we use search with eval or something like that to get the actual count of events having ErrorResponse?

Thank you!

Re: How to merge multiple searches and combine the result in a tabular format

kdulhan — Thu, 31 Aug 2017 21:56:16 GMT

Is there any way where we can search ErrorResponse independently and club the count with count1 and count2 in a tabular format?

Re: How to merge multiple searches and combine the result in a tabular format

somesoni2 — Thu, 31 Aug 2017 21:59:12 GMT

You can but it won't be most efficient solution (two searches running)

ns=app1 Service='trigger1' id=100 ActNo=* Response
 | eval count1=if(AccNo="101",1,0)
 | eval count2=if(count1=1,0,1)
 | stats sum(count1) as "Success Count" sum(count2) as "Success Count1"
| appendcols  [search ns=app1 [ErrorResponse] Service='trigger1' id=100 | stats count as "Failed Count" ]

Re: How to merge multiple searches and combine the result in a tabular format

kdulhan — Thu, 31 Aug 2017 22:24:25 GMT

Yes it worked, thanks a ton.

I have another query posed on the same line with subject 'Searching a particular field and performing actions based on its presence and value'. I think you might be able to help me quickly in that.

Thank You!.

Re: How to merge multiple searches and combine the result in a tabular format

kdulhan — Fri, 01 Sep 2017 09:21:55 GMT

Hello,

In order to search for the error records, I use :
ns=app1 Service='trigger1' Id!='temp-100' | Search ErrorResponse

Here I get an event like:
timestamp ns=app1 [ErrorResponse] Service='trigger1' id=105 ActNo=1234

Now I have to fetch this ActNo field and search with only ActNo=1234. It will list many events and in those I have to look for a field appId = 'New1'. If New1, I have to add it to a counter1 else counter2.

Thank you!