Splunk Search

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

kdulhan
Explorer

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!

0 Karma
1 Solution

somesoni2
Revered Legend

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"

View solution in original post

0 Karma

somesoni2
Revered Legend

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"
0 Karma

kdulhan
Explorer

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.

0 Karma

somesoni2
Revered Legend

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.

0 Karma

cmerriman
Super Champion

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

0 Karma

kdulhan
Explorer

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.

0 Karma

somesoni2
Revered Legend

Try update answer.

0 Karma

kdulhan
Explorer

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!

0 Karma

somesoni2
Revered Legend

Does the failed response have ActNo field?

0 Karma

kdulhan
Explorer

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

0 Karma

somesoni2
Revered Legend

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"
0 Karma

kdulhan
Explorer

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

0 Karma

somesoni2
Revered Legend

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

0 Karma

kdulhan
Explorer

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!

0 Karma

kdulhan
Explorer

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

0 Karma

somesoni2
Revered Legend

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" ]
0 Karma

kdulhan
Explorer

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!

0 Karma

kdulhan
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Is "Search Response" the same in both queries?

---
If this reply helps you, Karma would be appreciated.
0 Karma

kdulhan
Explorer

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

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