topic Re: combine two queries in Getting Data In

How to combine two queries?

kranthimutyala — Thu, 05 Jan 2023 17:29:53 GMT

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

Re: combine two queries

gcusello — Wed, 27 Apr 2022 11:13:26 GMT

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

Re: combine two queries

PickleRick — Wed, 27 Apr 2022 11:49:15 GMT

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)

Re: combine two queries

gcusello — Wed, 27 Apr 2022 11:58:01 GMT

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

Re: combine two queries

kranthimutyala — Wed, 27 Apr 2022 13:20:32 GMT

@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

Re: combine two queries

kranthimutyala — Wed, 27 Apr 2022 13:32:19 GMT

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

Re: combine two queries

gcusello — Wed, 27 Apr 2022 14:03:21 GMT

@kranthimutyala,

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

Ciao.

Giuseppe

Re: combine two queries

MrIncredible — Thu, 05 Jan 2023 14:43:58 GMT

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

Re: combine two queries

gcusello — Thu, 05 Jan 2023 14:47:07 GMT

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;-)

Re: combine two queries

MrIncredible — Fri, 06 Jan 2023 07:43:10 GMT

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

Re: combine two queries

gcusello — Fri, 06 Jan 2023 10:49:10 GMT

Hi @MrIncredible,

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

Ciao.

Giuseppe

Re: combine two queries

MrIncredible — Fri, 06 Jan 2023 11:16:05 GMT

@gcusello apologies!!! another question raised.

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

Re: combine two queries

pavi1611 — Fri, 12 Jan 2024 15:06:18 GMT

what if ,it has more than 50k events

index="aaam_devops_elasticsearch_idx" | search project = Einstein360_TicketsCreated_ElasticSearch_20210419 | search source.TransactionName ="ITGTicketCreated" | dedup id | timechart span=1d count as ITGTicketCreated | join max=0 _time [| search index="aaam_devops_elasticsearch_idx" | search project = Einstein360_TruckRollCreated_ElasticSearch_20210420 | search source.TransactionName = "Truck_Roll_Create_Result" | timechart span=1d dc(id) as TruckRollCreated] | stats values(ITGTicketCreated) as ITGTicketCreated values(TruckRollCreated) as TruckRollCreated by _time

Re: combine two queries

dtburrows3 — Fri, 12 Jan 2024 16:42:45 GMT

If a subsearch has more than 50,000 events or takes longer than 1 minute (i think) to run it will auto-finalize. Occurrence of either of these scenarios will cause the data returned from the subsearch to be truncated and incomplete.

BTW I don't think the search you shared needs to use a join/subsearch, something like this will probably do the same thing.

index="aaam_devops_elasticsearch_idx" ((project="Einstein360_TicketsCreated_ElasticSearch_20210419" AND "source.TransactionName"="ITGTicketCreated") OR (project="Einstein360_TruckRollCreated_ElasticSearch_20210420" AND "source.TransactionName"="Truck_Roll_Create_Result")) | timechart span=1d dc(eval(case('project'=="Einstein360_TicketsCreated_ElasticSearch_20210419" AND 'source.TransactionName'=="ITGTicketCreated", id))) as ITGTicketCreated, dc(eval(case('project'=="Einstein360_TruckRollCreated_ElasticSearch_20210420" AND 'source.TransactionName'=="Truck_Roll_Create_Result", id))) as TruckRollCreated

Re: combine two queries

PickleRick — Fri, 12 Jan 2024 16:43:04 GMT

By default (unless the limit is reconfigured) join uses only 50000 results from the subsearch to join with the outer search and runs for only 60 seconds to generate those results. If your subsearch exceeds 60 seconds of execution time or generates more than 50k results, it's silently finalized and only the results returned so far (up to 50k) are used for join.

With other subsearch uses the limits can be lower - even down to 10k results.

That's one of the reasons subsearches are best avoided - since they are silently finalized, you're not getting any feedback that you're not getting full results from your search and you're not aware that your final results might be incomplete or plain wrong.

Re: combine two queries

pavi1611 — Tue, 16 Jan 2024 10:17:13 GMT

Thanks, a lot... Its working 🙂 🙂