How to perform search over the values of each field that are returned by first query in the second query ?

[Iambharath]

HI All,

I have two index
1. index=index_app_csv
2. index=index_app_json

My requirement is to retrieve the values of certain fields from first index(index_app_csv) and use those values in the second index(index_app_json) to pick up the req and res having those values from second index.

index="index_app_csv"  BILLING_ACCOUNT_ID=7196333170682 | fields + PAYMENT_ID BILLING_ACCOUNT_ID WALLET_ID SESSION_ID

Above is the first query which will give me the values of fields like:

PAYMENT_ID          BILLING_ACCOUNT_ID         WALLET_ID                 SESSION_ID 
14180710541           7196333170682                9332303TCT                abcd
14180710512           196333170682              4332303TCT                abcd
14180710345           7196333170682                345t2303TCT               abcd

Now i want to perform the search for each value in second index(index_app_json) to retrieve the request and response having those values.
Fields in the first index is not available in the second index. I need to perform the search for the values so that events having those values will get picked up.

search index=epwf_app_json $PAYMENT_ID  $BILLING_ACCOUNT_ID $WALLET_ID $SESSION_ID  

I have tried with return, set append, join but nothing is working. joins and append are taking too much time and also results in inconsistent results.

Please suggest me how can i achieve this. And will the foreach command work for this...??

Thanks,
Bharath

[somesoni2 · Accepted solution]

If the name of the common fields are same in both the index , you can try like this

index=epwf_app_json [search index="index_app_csv" BILLING_ACCOUNT_ID=7196333170682 | fields + PAYMENT_ID BILLING_ACCOUNT_ID WALLET_ID SESSION_ID ]

This will, internally, add a giant condition based on your field values. like this,

index=epwf_app_json ( (PAYMENT_ID=14180710541 AND BILLING_ACCOUNT_ID=7196333170682 AND WALLET_ID=9332303TCT AND SESSION_ID=abcd) OR (PAYMENT_ID=14180710512 AND BILLING_ACCOUNT_ID=196333170682 AND WALLET_ID=4332303TCT AND SESSION_ID=abcd)...)

[Iambharath]

HI All,

Finally got the solution using mvappend and mvdedup.

index="index_epwf_json" [search index=index_epwf_csv BILLING_ACCOUNT_ID=9332303TCT | eval identifierValue=mvappend(mvdedup(BILLING_ACCOUNT_ID),mvdedup(PAYMENT_ID),mvdedup(WALLET_ID),mvdedup(SESSION_ID),mvdedup(EMAIL_ID)) |table  identifierValue] |fields +  src dst transactionApi transactionType identifierType  identifierValue | table _time src dst identifierType  identifierValue transactionApi  transactionType statusMessage  | sort +transactionType  | sort +_time  | rename _time AS TIME, src AS SOURCE,dst as DESTINATION,transactionApi as TRSANCTION_API,transactionType as TRANSACTION_TYPE,identifierType  as IDENTIFIER_TYPE,identifierValue AS IDENTIFIER_VALUE status as STATUS, statusMessage  AS STATUS_MESSAGE | convert timeformat="%Y-%m-%d %H:%M:%S %p" ctime(TIME)

Thanks,
Bharath

[Iambharath]

Hi somesoni,

Thank you for ur answer.

You are correct. That will work if we have fields in index_app_json but we dont have them in common.

can u please let me know how can we assign multiple fields values to single field using eval command so that i can do search for identifierValue using below.

index="index_app*" [search index=index_app_csv BILLING_ACCOUNT_ID=7196333170682 | eval identifierValue=PAYMENT_ID | eval identifierValue=BILLING_ACCOUNT_ID |table identifierValue] |stats by identifierValue

above query is adding only last field BILLING_ACCOUNT_ID to identifierValue and giving results accordingly to that.

i want to add all the fields like PAYMENT_ID, WALLET_ID, SESSION_ID to identifierValue field.
And do the stats by identifierValue because identifierValue is the valid field in index_app_json index.

Thanks,
Bharath

[somesoni2]

You need to add all 4 columns in single eval like this. Does that mean your index=index_app* has a field called identifierValue and has the value in exact format as what you're building in index_app_csv subsearch?? It won't work if it's not there.

index="index_app*" [search index=index_app_csv BILLING_ACCOUNT_ID=7196333170682 | eval identifierValue=PAYMENT_ID." ".BILLING_ACCOUNT_ID |table identifierValue]  |stats by identifierValue

[Iambharath]

somesoni,

Yes i have field called "identifierValue" in index="index_app_json".

i need to assign the each value of BILLING_ACCOUNT_ID,PAYMENT_ID, WALLET_ID from index_app_csv to identifierValue field of index_app_json and retrieve the corresponding request and responses.

But not continuously by using the concatenation.

index="index_app*" [search index=index_app_csv BILLING_ACCOUNT_ID=7196333170682 | eval identifierValue=PAYMENT_ID |table identifierValue] |stats by identifierValue

this will give me events for the PAYMENT_IDs based on criteria BILLING_ACCOUNT_ID=7196333170682 like :

{   [-] 

dst: HCDE

identifierType: PAYMENT_ID

identifierValue: 14180710501
messages: [ [+]
]

src: EPWF

status: SUCCESS

statusMessage:
time: 1485325379579

transactionApi: INITIATE_PAYMENT_SESSION
transactionType: RESPONSE

transactionValue: { [+]
}

}

{   [-] 

dst: HCDE

identifierType: PAYMENT_ID

identifierValue: 14180710501

messages: [ [+]
]

src: EPWF

status: SUCCESS

statusMessage:
time: 1485325379037

transactionApi: INITIATE_PAYMENT_SESSION
transactionType: REQUEST
transactionValue: { [+]
}

}

similarly ,
below query will give me events for BILLING_ACCOUNT_ID values in "index_app_json" index based on the criteria BILLING_ACCOUNT_ID=7196333170682 of index=index_app_csv :

index="index_app*" [search index=index_app_csv BILLING_ACCOUNT_ID=7196333170682 | eval identifierValue=BILLING_ACCOUNT_ID |table identifierValue] |stats by identifierValue

{   [-] 

dst: EPWF

identifierType: BILLING_ACCOUNT_ID

identifierValue: 7196333170682

messages: [ [+]
]

src: DVAR

status: SUCCESS

statusMessage:
time: 1485325352171

transactionApi: PAYMENT_HISTORY

transactionType: REQUEST
transactionValue: { [+]
}

}

{   [-] 

dst: EPWF

identifierType: BILLING_ACCOUNT_ID

identifierValue: 7196333170682

messages: [ [+]
]

src: DVAR

status: SUCCESS

statusMessage:
time: 1485325327879

transactionApi: PAYMENT_HISTORY

transactionType: RESPONSE

transactionValue: { [+]
}

}

Now i want to get all values of fields BILLING_ACCOUNT_ID, PAYMENT_ID,WALLET_ID .... from the "index_app_csv" based on the criteria BILLING_ACCOUNT_ID=7196333170682 and retrieve the req and resp from index_app_json

some thing like this from index_app_csv for BILLING_ACCOUNT_ID=7196333170682 :

indentifierValue
7196333170682
14180710501
9332303TCT
abcd

so that i can do stats by indentifierValue on the index_app_json.
That will retrieve me events having the
indentifierValue= 7196333170682,
indentifierValue=14180710501
indentifierValue=9332303TCT
indentifierValue=abcd

Thanks,
Bharath