How to combine two queries?

[kranthimutyala]

Hi All,

I have 2 different queries and I want to combine their results. These 2 queries return a single value output I want these 2 values in the same search result. Thanks for any help.

index=“abc” (TYPE="Run bot finished" OR TYPE="Run bot Deployed")  |  search STATUS=Successful TYPE="Run bot finished" | stats  count |rename count as Success_Count

index = “abc” RPAEnvironment = "prd"  ProcessName = "*" LogType = "*" TaskName = "*Main*"  (LogLevel=ERROR OR LogLevel=FATAL)
| eval Time = strftime(_time, "%Y-%m-%d %H:%M:%S")           
| eval LogDescription = trim(replace(LogDescription, "'", ""))         
| eval LogMessage = trim(replace(LogMessage, "'", ""))          
| eval TaskName = trim(replace(TaskName, "'", ""))           
| eval host=substr(host,12,4)           | eval Account=if(User!= "" ,User,LoginUser)          
| table  Time, LogNo, host, Account, LogType, LogMessage, TaskName ,ProcessName          
| rename  LogMessage as "Log Message", TaskName as "Task Name", host as "VDI"  | sort  - Time|stats count|rename count as Failure_Count

[gcusello]

Hi @kranthimutyala,

di you already explored the "append" command?

index=“abc” (TYPE="Run bot finished" OR TYPE="Run bot Deployed") STATUS=Successful TYPE="Run bot finished" 
| stats  count 
| rename count as Success_Count
| append [ search 
     index = “abc” RPAEnvironment = "prd"  ProcessName = "*" LogType = "*" TaskName = "*Main*"  (LogLevel=ERROR OR LogLevel=FATAL)
     | eval 
          Time = strftime(_time, "%Y-%m-%d %H:%M:%S"),
          LogDescription = trim(replace(LogDescription, "'", "")),
          LogMessage = trim(replace(LogMessage, "'", "")),
          TaskName = trim(replace(TaskName, "'", "")),
          host=substr(host,12,4),
          Account=if(User!= "" ,User,LoginUser)
     | stats count
     | rename count as Failure_Count ]

in this way you'll have the results of the two searches in two different rows of the same table, if you want to have them in a single row you can use "transpose".

Ciao.

Giuseppe

[MrIncredible]

@gcusello @PickleRick And say if there are 3 or more queries to combine the results ... append will work ?

[gcusello]

Hi @MrIncredible,

yes, but only id the results of each subsearch are less than 50,000.

if one answer solves your need, please accept one answer for the other people of Community or tell us how we can help you.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors;-)

[MrIncredible]

Thanks @gcusello append is working more than 1 time also. My results are appearing as per attached screenshot (3 rows * 3 columns). I tried transpose command to make this in good format but somehow not able to remove last 2 columns. I mean what I am looking for, values should be shown like 3 rows & 2 columns. Is it possible with transpose command? can you help please

[gcusello]

Hi @MrIncredible,

please share your search and next time open a new question instead append your request to another question.

Ciao.

Giuseppe

[MrIncredible]

@gcusello apologies!!! another question raised.

https://community.splunk.com/t5/Getting-Data-In/how-to-format-results-getting-from-multiple-append/m...

[kranthimutyala]

@gcusello I want do a eval calculation like success_count - Failure count but its not working

[gcusello]

@kranthimutyala,

In this case, you have to put both the results on the same row and the you can use eval, something like this:

index=“abc” (TYPE="Run bot finished" OR TYPE="Run bot Deployed") STATUS=Successful TYPE="Run bot finished" 
| stats  count 
| rename count as Success_Count
| append [ search 
     index = “abc” RPAEnvironment = "prd"  ProcessName = "*" LogType = "*" TaskName = "*Main*"  (LogLevel=ERROR OR LogLevel=FATAL)
     | eval 
          Time = strftime(_time, "%Y-%m-%d %H:%M:%S"),
          LogDescription = trim(replace(LogDescription, "'", "")),
          LogMessage = trim(replace(LogMessage, "'", "")),
          TaskName = trim(replace(TaskName, "'", "")),
          host=substr(host,12,4),
          Account=if(User!= "" ,User,LoginUser)
     | stats count
     | rename count as Failure_Count ]
| stats values(Failure_Count) AS Failure_Count values(Success_Count) AS Success_Count
| eval diff=Success_Count-Failure_Count

Ciao.

Giuseppe

[gcusello]

Hi @kranthimutyala,

sorry I forgot to add an information (as @PickleRick said) you can use a subquery if you have less than 50,000 results.

Ciao.

Giuseppe

[PickleRick]

I would be very cautious with appending such wildcarded subsearch. Especially over a longer time period the subsearch has a decent chance of timing out.

Since both searches pull from the same index, I'd try some conditional evaluation.

But the other - more "high-level" - question is if one of the searches gives Success count, the other gives Failure Count, is there any additional category of events? Or maybe they can be split easier between those two categories? (I don't suppose an event can be both Success and Failure)

[kranthimutyala]

@PickleRick  yes two events are different. And at the end I'm looking to take the difference between success count and Failure count , but it's not working with the query shared by @gcusello