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