topic Output of one String as an input to another search in Splunk Search

Output of one String as an input to another search

jayeshrajvir — Tue, 09 Jan 2024 06:26:23 GMT

I have this query which is working as expected. There are two different body axs_event_txn_visa_req_parsedbody and axs_event_txn_visa_rsp_formatting and common between two is F62_2

(eventtype =axs_event_txn_visa_req_parsedbody "++EXT-ID[C0] FLD[Authentication Program..] FRMT[TLV] LL[1] LEN[2] DATA[01]") OR eventtype=axs_event_txn_visa_rsp_formatting
| rex field=_raw "(?s)(.*?FLD\[Acquiring Institution.*?DATA\[(?<F19>[^\]]*).*)"
| rex field=_raw "(?s)(.*?FLD\[Authentication Program.*?DATA\[(?<FCO>[^\]]*).*)"
| rex field=_raw "(?s)(.*?FLD\[62-2 Transaction Ident.*?DATA\[(?<F62_2>[^\]]*).*)"
| rex field=_raw "(?s)(.*?FLD\[Response Code.*?DATA\[(?<VRC>[^\]]*).*)"
| stats values(txn_uid) as txn_uid, values(txn_timestamp) as txn_timestamp, values(F19) as F19, values(FCO) as FCO, values(VRC) as VRC by F62_2
| where F19!=036 AND FCO=01

now lets say i want to rewrite this query using appendcol/substring. something like this. TID from axs_event_txn_visa_req_parsedbody the resulted output should be passing to another query so i can corresponding log

For example

Table -1

Name Emp-id

Jayesh 12345

Table

Designation Emp-id

Engineer 12345

use Emp-id from table-1 and get the destination from table-2, similarly TID is the common field between two index, i want to fetch VRC using TID from Table-1

index=au_axs_common_log source=*Visa* "++EXT-ID[C0] FLD[Authentication Program..] FRMT[TLV] LL[1] LEN[2] DATA[01]" | rex field=_raw "(?s)(.*?FLD\[62-2 Transaction Ident.*?DATA\[(?<TID>[^\]]*).*)"
|appendcols search [ index=au_axs_common_log source=*Visa* "FORMATTING:" | rex field=_raw "(?s)(.*?FLD\[62-2 Transaction Ident.*?DATA\[(?<TID>[^\]]*).*)" |rex field=_raw "(?s)(.*?FLD\[Response Code.*?DATA\[(?<VRC>[^\]]*).*)"
| stats values(VRC) as VRC by TID ]

Re: Output of one String as an input to another search

jayeshrajvir — Tue, 09 Jan 2024 06:45:20 GMT

append is used for historical data but my data in real time so please suggest

Re: Output of one String as an input to another search

PickleRick — Tue, 09 Jan 2024 08:51:22 GMT

1. appendcols just adds additional columns from the subsearch to the results of the main search without any correlation between the result sets. It just "glues" them together in the order return by respective searches. So it's usually not the best possible idea. The command has its uses but they are very rare.

2. Running real-time searches is generally not the best idea - it allocates a single CPU across every indexer participating in the search as well as your search-head. Also real-time searches have a lot of limitations (and you can only use some of the commands in your searches).

Re: Output of one String as an input to another search

ITWhisperer — Tue, 09 Jan 2024 09:33:56 GMT

Try something like this

index=au_axs_common_log source=*Visa* "++EXT-ID[C0] FLD[Authentication Program..] FRMT[TLV] LL[1] LEN[2] DATA[01]" | rex field=_raw "(?s)(.*?FLD\[62-2 Transaction Ident.*?DATA\[(?<TID>[^\]]*).*)" |append [search index=au_axs_common_log source=*Visa* "FORMATTING:" | rex field=_raw "(?s)(.*?FLD\[62-2 Transaction Ident.*?DATA\[(?<TID>[^\]]*).*)" |rex field=_raw "(?s)(.*?FLD\[Response Code.*?DATA\[(?<VRC>[^\]]*).*)"] | stats values(index) as index values(VRC) as VRC by TID | where index="au_axs_common_log"

By the way, this is untested - if you want people to suggest tested examples, you should provide (anonymised) sample events (together with example expected results).

Re: Output of one String as an input to another search

jayeshrajvir — Thu, 11 Jan 2024 03:31:27 GMT

append and appendcol simply appending the query its like a glue. Please correct me if i am wrong

what i really want is

This is query 1 - output

-------------------------------

F62_2	F19	FCO
384011068172061	840	1
584011056069894	826	1

Query 2

eventtype=axs_event_txn_visa_rsp_formatting
| rex field=_raw "(?s)(.*?FLD\[62-2 Transaction Ident.*?DATA\[(?<F62_2>[^\]]*).*)"
| stats values(txn_uid) as txn_uid, values(txn_timestamp) as txn_timestamp, by F62_2

What I really want is the output of the for query 1 and pass as an input to query, common field between two queries is F62_2.

if i run the query it would be different output, so basically two queries should be combined and when it run it should take from F62_2 from query 1 and produce values(txn_uid) as txn_uid, values(txn_timestamp) as txn_timestamp

Re: Output of one String as an input to another search

jayeshrajvir — Thu, 11 Jan 2024 03:32:39 GMT

thank you so what is the best practice to combine two queries and produce the output

Re: Output of one String as an input to another search

PickleRick — Thu, 11 Jan 2024 07:24:17 GMT

Depends on the actual use case - the data you have and the desired output. You already had one example in this thread from @ITWhisperer .

Re: Output of one String as an input to another search

ITWhisperer — Thu, 11 Jan 2024 09:34:16 GMT

@jayeshrajvir wrote:
append and appendcol simply appending the query its like a glue. Please correct me if i am wrong

Not quite right - append adds events to the event pipeline, appendcols adds fields to existing event i.e. append is vertical "glue" whereas appendcols is horizontal "glue"

For completeness, appendpipe is also vertical "glue" but it uses the existing events pipeline as its base data rather than a new search

Re: Output of one String as an input to another search

jayeshrajvir — Thu, 11 Jan 2024 10:51:38 GMT

ok. Thanks.

Would you please share your thoughts on how to merge the two queries

Re: Output of one String as an input to another search

jayeshrajvir — Thu, 11 Jan 2024 10:52:09 GMT

what i really want is

This is query 1 - output

-------------------------------

F62_2	F19	FCO
384011068172061	840	1
584011056069894	826	1

Query 2

What I really want is the output of the for query 1 and pass as an input to query, common field between two queries is F62_2.

Re: Output of one String as an input to another search

ITWhisperer — Thu, 11 Jan 2024 11:05:49 GMT

(eventtype =axs_event_txn_visa_req_parsedbody "++EXT-ID[C0] FLD[Authentication Program..] FRMT[TLV] LL[1] LEN[2] DATA[01]") | rex field=_raw "(?s)(.*?FLD\[Acquiring Institution.*?DATA\[(?<F19>[^\]]*).*)" | rex field=_raw "(?s)(.*?FLD\[Authentication Program.*?DATA\[(?<FCO>[^\]]*).*)" | rex field=_raw "(?s)(.*?FLD\[62-2 Transaction Ident.*?DATA\[(?<F62_2>[^\]]*).*)" | stats values(F19) as F19, values(FCO) as FCO by F62_2 | where F19!=036 AND FCO=01 | append [search eventtype=axs_event_txn_visa_rsp_formatting | rex field=_raw "(?s)(.*?FLD\[62-2 Transaction Ident.*?DATA\[(?<F62_2>[^\]]*).*)"] | stats values(F19) as F19, values(FCO) as FCO values(txn_uid) as txn_uid, values(txn_timestamp) as txn_timestamp, by F62_2