Combine Multiple Queries output and produce the result in table format

[anandhalagaras1]

Hi Team,

I require merging three queries originating from the identical index and sourcetypes, yet each query necessitates extraction and manipulation of its output.

Query 1:

A single index is linked to three unique sourcetypes.

index = abc sourcetype= def, sourcetype=ghi & sourcetype=jkl

Query 2 :

Its same like Query 1 

index = abc sourcetype= def, sourcetype=ghi & sourcetype=jkl

Query 3:

Its same like Query 1 & 2

index = abc sourcetype= def, sourcetype=ghi & sourcetype=jkl

The index and sourcetype details remain consistent across all three queries, but the keywords differ.

Thus, I aim to merge the three queries, compare them, and extract the desired output.

For instance, in the initial query, the "Step" field is extracted during the search process, containing diverse data such as computer names and OS information.

In the second query, our aim is to ascertain the count of successful occurrences in the "Step" field, specifically the count of computer names indicating success. Likewise, in the third query, we intend to retrieve information regarding failures.

Query1:

index="abc" ("Restart transaction item" NOT "Pending : transaction item:") | rex field=_raw "Restart transaction item: (?<Step>.*?) \(WorkId:"| table Step |stats Count by Step

Query 2:

index="abc" ("Error restart workflow item:") | rex field=_raw "Error restart workflow item: (?<Success>.*?) \(WorkId:"| table Success |stats Count by Success

Query 3:

index="abc" "Restart Pending event from command," | rex field=_raw "Restart Pending event from command, (?<Failure>.*?) \Workid"| table Failure |stats Count by Failure

Thus, in the initial query, the Step field is extracted, and our objective is to extract both success and failure data from this field, presenting it in a tabular format.

Despite attempting to use a join query, it was unsuccessful. Assistance in this matter would be greatly appreciated.

Kindly help on the same.

[gcusello]

Hi @anandhalagaras1 ,

you could try something like this:

index="abc" ("Restart transaction item" OR "Error restart workflow item:" OR "Restart Pending event from command,")
| rex field=_raw "Restart transaction item: (?<Step>.*?) \(WorkId:"
| rex field=_raw "Error restart workflow item: (?<Success>.*?) \(WorkId:"
| rex field=_raw "Restart Pending event from command, (?<Failure>.*?) \Workid"
| stats 
   count(eval(searchmatch("Restart transaction item"))) AS "Step"
   count(eval(searchmatch("Error restart workflow item:"))) AS "Success"
   count(eval(searchmatch("Restart Pending event from command,"))) AS "Failure"

Ciao.

Giuseppe

[anandhalagaras1]

@gcusello ,

I have shared the sample events as well for all the 3 queries and for each Step field i want to get the Success and Failure information so kindly help to achieve the same.

The query you have provided pulls the total count of success and failure but i need a split of each "Step" field and their corresponding "Success" and "Failure" information.

So kindly help to check and update on the same.

[PickleRick]

While @ITWhisperer 's response about the original data (and desired result) is valid, there is one important thing worth noting - with Splunk often the approach of "joining" separate searches is not the best idea. The typical Splunk approach would be to search for all events in the initial search and then subsequently filter and split into separate categories further down the search pipeline.

[ITWhisperer]

It would help if you could share some sample anonymised events so we can see what it is that you are dealing with and try to figure a search that will work for you, because just discussing searches without knowing what they apply to is often fruitless.

[anandhalagaras1]

@ITWhisperer @PickleRick ,

Thank you for your response.

Here are my updates as requested.

==================================================================

Query1:

index="abc" ("Restart transaction item" NOT "Pending : transaction item:") | rex field=_raw "Restart transaction item: (?<Step>.*?) \(WorkId:"| table Step |stats Count by Step

Sample Events:

2024-04-21 03:00:02.6106|INFO|Transaction.Overflow.card.Command.Control|Restart transaction item: Validation (WorkId: 1234567) for RUNTIME: 987654|
2024-04-21 02:00:03.5437|INFO|Transaction.Overflow.card.Command.Control|Restart transaction item: Creation (WorkId: 1234567) for RUNTIME: 987654|
2024-04-18 09:00:10.9426|INFO|Transaction.Overflow.card.Command.Control|Restart transaction item: Compliance Portal Report (WorkId: 1234567) for RUNTIME: 987654|

Output in Table Format:

Step                                                    Count
Validation                                              1
Creation                                                1
Compliance Portal Report            1

Query 2:

index="abc" ("Error restart workflow item:") | rex field=_raw "Error restart workflow item: (?<Success>.*?) \(WorkId:"| table Success |stats Count by Success

For the 1st and 2nd event it contains 30+ Lines of sample event hence I have took a small portion of it. While the 3rd and 4th event contains 10+ Lines and i have extracted a small amount of data.

Sample Events:

2024-04-14 02:00:07.8759|ERROR|Transaction.Overflow.card.Command.Control|Error restart workflow item: Validation (WorkId: 1234567) for RUNTIME: 987654|System.Info.Entra.Solution.UpdateExecution: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Transaction (Process ID 12) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

2024-03-26 15:00:05.9123|ERROR|Transaction.Overflow.card.Command.Control|Error restart workflow item: Validation (WorkId: 1234567) for RUNTIME: 987654|System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Transaction (Process ID 12) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

2024-03-27 03:00:15.3116|ERROR|Transaction.Overflow.card.Command.Control|Error restart workflow item: Creation (WorkId: 1234567) for RUNTIME: 987654|System.NullReferenceException: Object reference not set to an instance of an object.

2024-03-27 01:00:16.3231|ERROR|Transaction.Overflow.card.Command.Control|Error restart workflow item: Compliance Portal Report (WorkId: 1234567) for RUNTIME: 987654|System.NullReferenceException: Object reference not set to an instance of an object.

Output in Table Format:

Success                                         Count
Validation                                           2
Creation                                             1
Compliance Portal Report         1

Query 3:

index="abc" "Restart Pending event from command," | rex field=_raw "Restart Pending event from command, (?<Failure>.*?) \Workid"| table Failure |stats Count by Failure

===============================================================================================================================================================================================

Sample Events:

2024-04-21 03:01:14.7929|INFO|Transaction.Overflow.card.Command.ValidationCommand|Pending: Restart Pending event from command, Validation Workid (WorkId: 1234567) for RUNTIME: 987654.|
2024-04-18 09:00:11.8332|INFO|Transaction.Overflow.card.Command.CreationCommand|Pending: Restart Pending event from command, Creation Workid (WorkId: 1234567) for RUNTIME: 987654.|
2024-04-17 06:51:16.7544|INFO|Transaction.Overflow.card.Command.CompliancePortalReportCommand|Pending: Restart Pending event from command, Compliance Portal Report Workid (WorkId: 1234567) for RUNTIME: 987654.|
2024-04-16 13:00:34.6238|INFO|Transaction.Overflow.card.Command.PageCountsCommand|Pending: Restart Pending event from command, Page Counts Workid (WorkId: 1234567) for RUNTIME: 987654.|

Output in Table Format:

Failure                                               Count
Validation                                             1
Creation                                                1
Compliance Portal Report            1
Page Counts                                       1 

So I need to combine all the 3 queries, i.e. Example For Step "Validation" i need to get how many Step are present for last 24 hours and in which how many success and how many failure.

Hence kindly help to check and update on the same please.

[anandhalagaras1]

@gcusello @PickleRick @ITWhisperer 

Can you kindly help to check and update on the same.

[ITWhisperer]

How do you wish them to "be combined"?

[anandhalagaras1]

@ITWhisperer ,

Query1 has a field extracted as Step. So in this Step field which we have extracted as the information "Validation, Creation, Compliance Portal Report etc., with count So the same information Validation, Creation and Compliance Portal Report with Success count needs to be pulled using the second query and the failure ones needs to be extracted using the 3rd query.

The output should be something like this(Combining all 3 queries):

Step (Count)                                                      Success(Count)     Failure (Count)

Validation                                                                     3                                           2

Creation                                                                       2                                          2

Compliance Report Portal                                  2                                          2\

So kindly help with the query.

[ITWhisperer · Accepted solution]

Try something like this (after finding all events)

| rex field=_raw "Restart transaction item: (?<Step>.*?) \(WorkId:"
| rex field=_raw "Error restart workflow item: (?<Success>.*?) \(WorkId:"
| rex field=_raw "Restart Pending event from command, (?<Failure>.*?) Workid"
| eval Step=coalesce(Step,coalesce(Success, Failure))
| stats count(eval(if(Step==Success,1,null()))) as Success count(eval(if(Step==Failure,1,null()))) as Failure by Step

[anandhalagaras1]

@ITWhisperer 

Thank you for your assistance. I've successfully tested the query you provided and managed to retrieve the expected success and failure counts. However, in addition to this, I also need to retrieve the step count for each step from Query 1.

For instance, when I ran Query 1 individually for the last 30 days, I observed that 20 steps were extracted as a field along with the count of each step. Similarly, when I ran Query 2 for the same period, approximately 10 successes were extracted as a field with their respective counts. Likewise, when I ran Query 3, around 18 failures were extracted as a field with their counts.

So, with the combined search you provided, I'm able to obtain a total of 18 fields comprising both successes and failures. This is because if any of the step fields have either a success or a failure, it reflects in the query output. However, the other two step fields don't have successes or failures, and their step information is not present in the output of the provided query.

Therefore, we need to include the output of the first query, which includes the step field along with its count, in addition to the success and failure counts obtained from Queries 2 and 3.

Since Query 1 captures all events, Query 2 captures successes, and Query 3 captures failures, we need to ensure that the first query's output is also included in the combined search.

Could you please review and update accordingly? Thank you for your attention to this matter.

[ITWhisperer]

If the step has neither Success nor Failure, the counts for these columns will be zero