Splunk Search
Highlighted

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

Explorer

HI All,

I have two index
1. index=indexappcsv
2. index=indexappjson

My requirement is to retrieve the values of certain fields from first index(indexappcsv) and use those values in the second index(indexappjson) 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(indexappjson) 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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma
Highlighted

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

Explorer

Hi somesoni,

Thank you for ur answer.

You are correct. That will work if we have fields in indexappjson 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="indexapp*" [search index=indexappcsv BILLINGACCOUNTID=7196333170682 | eval identifierValue=PAYMENTID | eval identifierValue=BILLINGACCOUNTID |table identifierValue] |stats by identifierValue

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

i want to add all the fields like PAYMENTID, WALLETID, SESSIONID to identifierValue field.
And do the stats by identifierValue because identifierValue is the valid field in index
app_json index.

Thanks,
Bharath

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

You need to add all 4 columns in single eval like this. Does that mean your index=indexapp* has a field called identifierValue and has the value in exact format as what you're building in indexapp_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
0 Karma
Highlighted

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

Explorer

somesoni,

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

i need to assign the each value of BILLINGACCOUNTID,PAYMENTID, WALLETID from indexappcsv to identifierValue field of indexappjson and retrieve the corresponding request and responses.

But not continuously by using the concatenation.

index="indexapp*" [search index=indexappcsv BILLINGACCOUNTID=7196333170682 | eval identifierValue=PAYMENTID |table identifierValue] |stats by identifierValue

this will give me events for the PAYMENTIDs based on criteria BILLINGACCOUNT_ID=7196333170682 like :

{   [-] 

dst: HCDE

identifierType: PAYMENTID

identifierValue: 14180710501
messages: [ [+]
]

src: EPWF

status: SUCCESS

statusMessage:
time: 1485325379579

transactionApi: INITIATE
PAYMENT_SESSION
transactionType: RESPONSE

transactionValue: { [+]
}

}

{   [-] 

dst: HCDE

identifierType: PAYMENTID

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 BILLINGACCOUNTID values in "indexappjson" index based on the criteria BILLINGACCOUNTID=7196333170682 of index=indexappcsv :

index="indexapp*" [search index=indexappcsv BILLINGACCOUNTID=7196333170682 | eval identifierValue=BILLINGACCOUNT_ID |table identifierValue] |stats by identifierValue

{   [-] 

dst: EPWF

identifierType: BILLINGACCOUNTID

identifierValue: 7196333170682

messages: [ [+]
]

src: DVAR

status: SUCCESS

statusMessage:
time: 1485325352171

transactionApi: PAYMENT_HISTORY

transactionType: REQUEST
transactionValue: { [+]
}

}

{   [-] 

dst: EPWF

identifierType: BILLINGACCOUNTID

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 BILLINGACCOUNTID, PAYMENTID,WALLETID .... from the "indexappcsv" based on the criteria BILLINGACCOUNTID=7196333170682 and retrieve the req and resp from indexappjson

some thing like this from indexappcsv for BILLINGACCOUNTID=7196333170682 :

indentifierValue
7196333170682
14180710501
9332303TCT
abcd

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

Thanks,
Bharath

0 Karma
Highlighted

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

Explorer

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

Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.