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

[kdulhan]

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!

[somesoni2 · Accepted solution]

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"

[kdulhan]

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.

[somesoni2]

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.

[cmerriman]

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

[kdulhan]

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.

[somesoni2]

Try update answer.

[kdulhan]

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!

[somesoni2]

Does the failed response have ActNo field?

[kdulhan]

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

[somesoni2]

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"

[kdulhan]

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

[somesoni2]

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

[kdulhan]

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!

[kdulhan]

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

[somesoni2]

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

[kdulhan]

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!

[kdulhan]

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

[richgalloway]

Is "Search Response" the same in both queries?

[kdulhan]

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