UNION just like SQL....

[Raj_Splunk_Ing]

Hi, I think i am in the right way to use the union concept in splunk search query but wanted to confirm

I have 6 different queries using the same index but different search conditions on few fields and finally a table with the same set of columns in all 6 search queries.

I did something like below and

Index=Index123,
some other search criteria

|table Field1, Field2, Field3, Field4, Field5

| append 

[search Index=Index123

some other search criteria

|table Field1, Field2, Field3, Field4, Field5]

samething for the other queries - i just want to get the results from each query and look at all together
there is nothing like UNION right? just wanted to confirm

[ITWhisperer]

@livehybrid is correct that it is more efficient to use multiple search criteria on the initial search, but it is not quite the same as a union / append because the method outlined will return an event only once even if it satisfies multiple criteria whereas the append could have multiple instances of events which satisfy multiple criteria. You need to bear this in mind depending on what you are going to be doing with the events you have retrieved.

[Raj_Splunk_Ing]

Thank you ITWhisperer
since i have different search criteria in each search query which are going against the same Index 

I want to look at all the events in each scenario - if 1 event is in query 1 results and the same event is in query2 results it is fine since the criteria for query1 and query2 are different.. so having it in 2 different places is ok

I think i have to go with UNIOn or append in this case? correct 

[ITWhisperer]

It depends what you mean by "look at" - the events for each scenario will be returned using the method outlined by @livehybrid - what you then want to do with them will depend on whether you want duplicated events, and even then, you may be able to take that into account from the single list of events. If you could explain what you are trying to do in a bit more detail, we might be able to offer further  advice. For now, the advice is to stay away from union / append and use the method outlined, (until you can show some compelling reason to make your search less efficient!).

[Raj_Splunk_Ing]

Hi All,

when i search using 2 different individual search queries - i get the data from query1 say 100 rows in statistics

and when i run the query2 say i get 50 records or rows in the statistics tab - each result set includes the data based on the search criteria. the query2 result might include an event that is in the query1 result but the search criteria is different for this. What this means is for us is it is ok to have the 2 duplicates when i combine both of them

[ITWhisperer]

The other thing to bear in mind is that subsearches (as used by the append command) have limitations which are avoided by retrieving all the events in a single search. If your searches are small, this may not be a concern, but if your data sets get larger you may get inconsistent results.

[Raj_Splunk_Ing]

Hi,

I dont have the data but i can give an idea about the 2 diff search queries that  i have

index=AWS_Index_For_App1
sourcetype=AWS_Logs
source=AWS_Logs_App1
host="sub-service*" "AWS" "httpStatusCode":"409"
|rex field=_raw "\"httpStatusCode\":\"(?<httpStatusCode>\d+)\",.*?\"errorCode\":\"(?<errorCode>\d+)\",.*?\"errorDescription\":\"(?<errorDescription>[^\"]+)\""
|rex field=_raw "dd\.service=(?<service>[^,]+)" | rex field=_raw "http\.endpoint=(?<path>[^,]+)"
|eval ServiceType="Subscriptions"
|eval Date_Only=strftime(_time, "%Y-%m-%d")
|table _time httpStatusCode errorCode errorDescription service path _raw ServiceType Date_Only
|append [search
index=AWS_Index_For_App1
sourcetype=AWS_Logs
source=AWS_Logs_App1
host="stat-service*" "errorCode: 4091" OR "errorCode: 40914" OR "errorCode: 40922"
|spath input=_raw
|rex field=_raw "errorCode:\s*(?<errorCode>\d+)"
|rex field=_raw "errorDescription:\s*(?<errorDescription>[^}]+)"
|rex field=_raw "dd\.service=(?<service>[^,]+)"
|rex field=_raw "http\.endpoint=(?<path>[^,]+)"
|eval ServiceType="Statements"
|eval Date_Only=strftime(_time, "%Y-%m-%d")
|table _time httpStatusCode errorCode errorDescription service path _raw ServiceType,Date_Only]

please suggest if it is useful... i cannot provide the data for sec reasons

[PickleRick]

It most probably will _not_ work as you wish. While append is sometimes useful, the use cases for it are limited for several reasons, mostly because it's limited by the subsearch limits. A subsearch cannot run for more than 30 seconds by default and return more than 10k results. What's more important - if a search exceeds those limits it will get silently finalized without generating an error so you might never know you got wrong/incomplete results.

As @yuanliu said - it's not SQL and do not think in SQL terms about searching in Splunk. It requires some time to get used to SPL and some of its quirks but it pays off.

BTW, for your search it makes completely no sense to use append.

You're searching from the same set of data. You should definitely get a common base search and differentiate - if needed - further processing based on specific types of events.

You also seem to have had completely no data onboarding if you need to extract your fields manually this way. Not to mention that after you've already done spath (in the appended subsearch), there's no point of trying to extract data manually with regexes. Don't use regexes on structured data. It won't end well.

[Raj_Splunk_Ing]

In my case data returned is around 10,000 to 15,000 records not more than that.. I can clearly see the number of records from each are exactly matching with the append.. you think still go with OR condition in 1 single search

[Raj_Splunk_Ing]

Hi Rick, Thanks a lot.

I got these queries from some other team. If I have to rewrite these 2 queries without APPEND what would it look like. Could you please help for these 2 queries that i posted.. using the OR condition

[PickleRick]

The general base search should simply contains the common set of conditions

index=AWS_Index_For_App1 sourcetype=AWS_Logs source=AWS_Logs_App1

as well as OR-ed specific conditions for each of those "compound searches"

(host="sub-service*" "AWS" "httpStatusCode":"409") OR (host="stat-service*" "errorCode: 4091" OR "errorCode: 40914" OR "errorCode: 40922")

You could filter out later but it's better to have it early so Splunk can filter out any unneeded events as soon as possible.

If you don't already have your fields extracted (you use spath so your event must be a well-formed JSON structure), you can use spath and have all the fields now

| spath

Don't rex the raw data now because there's no point. You have your fields. You might need to do some renaming or extracting some parts of their contents but the bulk of your results you already have.

If you want to manipulate only some events, use the if() function in your eval statement (possibly with searchmatch() condition).

And that's it.

Don't overthink it.

[Raj_Splunk_Ing]

Rick, should i give you the whole query so you would get an idea

all condtions and rex everything together for all 5 queries

[Raj_Splunk_Ing]

Hi Rick,
These queries were given to me by someone to automate; when i look at closely they are using rex to get the values into those columns based on some pattern.. after the search filter on host with OR condition

these are rex related in each query - five queries -  what could be an easy way to get these so that it covers all patterns that are in each rex

|rex field=_raw "\"httpStatusCode\":\"(?<httpStatusCode>\d+)\",.*?\"errorCode\":\"(?<errorCode>\d+)\",.*?\"errorDescription\":\"(?<errorDescription>[^\"]+)\""
|rex field=_raw "dd\.service=(?<service>[^,]+)"
|rex field=_raw "http\.endpoint=(?<path>[^,]+)"

|spath input=_raw
|rex field=_raw "errorCode:\s*(?<errorCode>\d+)"
|rex field=_raw "errorDescription:\s*(?<errorDescription>[^}]+)"
|rex field=_raw "dd\.service=(?<service>[^,]+)"
|rex field=_raw "http\.endpoint=(?<path>[^,]+)"

| rex field=_raw "dd\.trace_id=(?<traceId>[^,]+)"
|transaction traceId
|rex field=_raw "\"httpStatusCode\":\"(?<httpStatusCode>\d+)\",.*?\"errorCode\":\"(?<errorCode>\d+)\",.*?\"errorDescription\":\"(?<errorDescription>[^\"]+)\""
|rex field=_raw "http\.endpoint=(?<path>[^,]+)"

|rex field=_raw "\"httpStatusCode\":\"(?<httpStatusCode>\d+)\",.*?\"errorCode\":\"(?<errorCode>\d+)\",.*?\"errorDescription\":\"(?<errorDescription>[^\"]+)\""
|rex field=_raw "dd\.service=(?<service>[^,]+)"
|rex field=_raw "http\.endpoint=(?<path>[^,]+)"

|spath input=_raw
|rex field=_raw "errorCode:\s*(?<errorCode>\d+)"
|rex field=_raw "errorDescription:\s*(?<errorDescription>[^}]+)"
|rex field=_raw "dd\.service=(?<service>[^,]+)"
|rex field=_raw "http\.endpoint=(?<path>[^,]+)"

[yuanliu]

I agree with @livehybrid and @ITWhisperer: you do NOT go with union.  Especially because you haven't shown anything in your use case that cannot be achieved using what @livehybrid suggests.  It takes some practice to stay away from SQL-like constructs but you will reap the benefit along the way.

Collect some data from your various criteria (can be mock data), and illustrate the result you want from these data.  People here will for sure show you a much more efficient search without reaching for union.

SPL does have a union command.  But unless there is no alternative, just don't touch it.

[livehybrid]

Hi @Raj_Splunk_Ing 

I would suggest trying to include in a single search, will be much more efficient. You can do something like 

index=yourIndex (searchCriteria1=here) OR (searchCriteria2=here) | table Field1 Field2 etc. 

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful

• Marking it as the solution if it resolved your issue

• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing.