Splunk Search

How to combine the result of 2 search queries?

ID_SplunkUser
Path Finder

Hi All,

I have a scenario to combine the search results from 2 queries. For Type= 101 I don't have fields "Amount" and "Currency", so I'm extracting them through Regex in separate query. I can't combine the regex with the main query due to data structure which I have. At the end I just want to display the Amount and Currency with all the fields.

1st query: 
sourcetype=abc | table Currency, Amount, Address, Type

2nd Query:
sourcetype=abc | rex field=_raw max_match=0 "(:32B:(?<Curr>.{3})(?<Amnt>.[^\n]+))" |where Type=101|eval fields = mvzip(Curr,Amnt,"#") | mvexpand fields | rex field=fields "(?<Currency>\w+)#(?<Amount>\S+)"

Other than 101 Type all other messages are having Amount and Currency fields. I just want to combine both results.

Thanks.

Tags (1)
0 Karma
1 Solution

inventsekar
Ultra Champion

please try -

 sourcetype=abc | table Currency, Amount, Address, Type | append [ sourcetype=abc | rex field=_raw max_match=0 "(:32B:(?<Curr>.{3})(?<Amnt>.[^\n]+))" |where Type=101|eval fields = mvzip(Curr,Amnt,"#") | mvexpand fields | rex field=fields "(?<Currency>\w+)#(?<Amount>\S+)"]

View solution in original post

0 Karma

woodcock
Esteemed Legend

Try this:

 sourcetype=abc | rex field=_raw max_match=0 "(:32B:(?<Curr>.{3})(?<Amnt>.[^\n]+))" |where Type=101|eval fields = mvzip(Curr,Amnt,"#") | mvexpand fields | rex field=fields "(?<Currency>\w+)#(?<Amount>\S+)" | stats sum(Amount) As Amount BY Currency Address Type
0 Karma

vivek_manoj
Explorer

you can use appendcols command for combining two search query.
like
index="abc"| search user="hostee"| appendcols[index="xyz"|search host="apple"]

0 Karma

sundareshr
Legend

I don't think you need a sub-search. Try this (assuming your regex is extracting values correctly).

sourcetype=abc | rex field=_raw max_match=0 "(:32B:(?<Curr>\w{3})(?<Amnt>[^\n]+))" | eval fields = mvzip(Curr, Amnt, "#") | mvexpand fields | rex field=fields "(?<Currency>\w+)#(?<Amount>\S+) | table Currency, Amount, Address, Type

OR

sourcetype=abc | rex field=_raw max_match=0 "(:32B:(?<Curr>\w{3})(?<Amnt>[^\n]+))" | eval fields = mvzip(Curr, Amnt, "#") | mvexpand fields | rex field=fields "(?<Currency>\w+)#(?<Amount>\S+) | stats count by Currency, Amount, Address, Type
0 Karma

inventsekar
Ultra Champion

please try -

 sourcetype=abc | table Currency, Amount, Address, Type | append [ sourcetype=abc | rex field=_raw max_match=0 "(:32B:(?<Curr>.{3})(?<Amnt>.[^\n]+))" |where Type=101|eval fields = mvzip(Curr,Amnt,"#") | mvexpand fields | rex field=fields "(?<Currency>\w+)#(?<Amount>\S+)"]
0 Karma

ID_SplunkUser
Path Finder

Thanks for the reply.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...