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.
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+)"]
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
you can use appendcols command for combining two search query.
like
index="abc"| search user="hostee"| appendcols[index="xyz"|search host="apple"]
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
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+)"]
Thanks for the reply.