Output of one String as an input to another search

[jayeshrajvir]

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 ]

[ITWhisperer]

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

[jayeshrajvir]

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

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

(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

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

[ITWhisperer]

---

@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

[jayeshrajvir]

what i really want is 

This is  query 1  - output

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

(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

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

[ITWhisperer]

(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

[jayeshrajvir]

ok. Thanks.

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

[PickleRick]

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

[jayeshrajvir]

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

[PickleRick]

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

[jayeshrajvir]

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