want to use field value of 1st search in join search query to filter event of search query inside join.

Abhineet — Mon, 03 Jul 2023 12:17:18 GMT

Hi,

Looking for splunk query to use field value of 1st search in join search query to filter event of search query inside join.

Query:

index=lsc_db2_qa_index sourcetype=lsc_db2_ewm_qa_outbound
| dedup EDIDCDOCNUM
| rex field=_raw "(?<dateTime>[\d\-\s:]+).\d{3}, TIME.*"
| rename EDIDCDOCNUM as ewmIdoc EDIDCSTATUS as ewmIdocStatus MESTYP as ewmmesType dateTime as ewmCreateTime
| table ewmIdoc ewmIdocStatus ewmmesType ewmCreateTime
| join type=outer ewmIdoc
[search index=webmethods_qa5555_index sourcetype=transactions_qa5555_src
| search sender="AMAT_SAP_EWM" AND receiver="EXACTA"
| rex field=_raw "(?<wmDateTime>[\d\-:\s]+) .*"
| rex field=messageId "(?<docNum>\d+)\|\|(?<whoNum>.*)"
| rex field=messageId "(?<docNum>\d+)"
| eval wmcreateDateTime= if( like( message, "%request from EWM%" ), wmDateTime,"")
| eval wmconfirmDateTime=if( like( message, "%request sent to Exacta successfully%" ), wmDateTime,"")
| eval wmsentDateTime=if( like( message, "%ready to send to Exacta%" ), wmDateTime,"")
| lookup wminterface_mapping.csv wmInterface as interface OUTPUT Interface
| stats values(Interface) as Interface values(whoNum) as whoNum values(wmcreateDateTime) AS wmcreateDateTime values(wmconfirmDateTime) AS wmconfirmDateTime values(wmsentDateTime) AS wmsentDateTime by docNum
| rename docNum as ewmIdoc]
| eval ewmIdoc=ltrim(tostring(ewmIdoc),"0")
| fields ewmIdoc ewmIdocStatus ewmmesType ewmCreateTime whoNum,Interface,wmcreateDateTime,wmconfirmDateTime,wmsentDateTime
| join type=outer whoNum
[search index=lsc_exacta_qa_index source="D:\\ProgramData\\Bastian Software\\Logs\\ExactaImportAdapter\\ExactaImportAdapter*"
| rex field=_raw ".* ORDER_NAME=\"(?<imaWho>[\d-]+)\" .*"
| rex field=_raw ".*JSON received for product import:.*\"product\":\"(?<imaWho>[\d-]+)\",.*"
| rex field=_raw ".*JSON received for putaway import:.*\"who\":\"(?<imaWho>[\d-]+)\",.*"
| eval exactaRecTime = strftime(_time,"%Y-%m-%d %H:%M:%S")
| dedup imaWho sortby +exactaRecTime
| eval exactaInfStatus = if(exactaRecTime != "","Success",NA)
| table imaWho exactaRecTime exactaInfStatus
| join type=outer imaWho [search index=lsc_exacta_qa_index source="D:\\ProgramData\\Bastian Software\\Logs\\ExactaImport\\ExactaImport.txt"
| rex field=_raw ".* Order \[(?<imWho>[\d-]+) - .*\] successfully assigned.*"
| rex field=_raw "\.* Bastian\.Exacta\.Interface\.Processes\.ExactaProductTranslatorBase - Validation of Message Successfull, Prepare to Insert\n.*ROWS ONLY;\@p0 = \'(?<imWho>[\d-]+)\'.*\[.*"
| rex field=_raw ".*\/line id \[(?<imWho>[\d-]+) -.* was cancelled successfully.\n.*"
| rex field=_raw ".*\[Import Pick Orders\].*ROWS ONLY;@p0 = \'(?<imWho>[\d-]+)\' \[[\S\s]*- Messages processed successfully.*"
| eval exactaDocTime = strftime(_time, "%Y-%m-%d %H:%M:%S")
| search imWho !=""
| eval exactaDocStatus = if(exactaDocTime != "","Created",NA)
| table imWho exactaDocTime exactaDocStatus
| rename imWho as imaWho]
| table imaWho exactaRecTime exactaDocTime exactaInfStatus exactaDocStatus
| rename imaWho as whoNum]
| search Interface = "*"
| rename whoNum as "WHO/PRODUCT"
| table ewmIdoc ewmIdocStatus ewmmesType ewmCreateTime "WHO/PRODUCT",Interface,wmcreateDateTime, wmsentDateTime, wmconfirmDateTime, exactaRecTime exactaDocTime exactaInfStatus exactaDocStatus

OUTPUT:

looking to execute above red highlighted search query on events whose "_time" field value is equal to or greater than field value "wmsentDateTime" which we got from search query highlighted in green.

Thanks

Abhineet Kumar

Re: want to use field value of 1st search in join search query to filter event of search query inside join.

isoutamo — Tue, 04 Jul 2023 12:33:07 GMT

When you are attaching SPL code or other configurations, please use </> token from edit options! That way it's much easier to read and we can sure that that it what you have paste! You should also use Ctrl/Cmd+F to get it formatted. See above. Unfortunately you lost colors when you use code block 😞

index=lsc_db2_qa_index sourcetype=lsc_db2_ewm_qa_outbound | dedup EDIDCDOCNUM | rex field=_raw "(?<dateTime>[\d\-\s:]+).\d{3}, TIME.*" | rename EDIDCDOCNUM as ewmIdoc EDIDCSTATUS as ewmIdocStatus MESTYP as ewmmesType dateTime as ewmCreateTime | table ewmIdoc ewmIdocStatus ewmmesType ewmCreateTime | join type=outer ewmIdoc [ search index=webmethods_qa5555_index sourcetype=transactions_qa5555_src | search sender="AMAT_SAP_EWM" AND receiver="EXACTA" | rex field=_raw "(?<wmDateTime>[\d\-:\s]+) .*" | rex field=messageId "(?<docNum>\d+)\|\|(?<whoNum>.*)" | rex field=messageId "(?<docNum>\d+)" | eval wmcreateDateTime= if( like( message, "%request from EWM%" ), wmDateTime,"") | eval wmconfirmDateTime=if( like( message, "%request sent to Exacta successfully%" ), wmDateTime,"") | eval wmsentDateTime=if( like( message, "%ready to send to Exacta%" ), wmDateTime,"") | lookup wminterface_mapping.csv wmInterface as interface OUTPUT Interface | stats values(Interface) as Interface values(whoNum) as whoNum values(wmcreateDateTime) AS wmcreateDateTime values(wmconfirmDateTime) AS wmconfirmDateTime values(wmsentDateTime) AS wmsentDateTime by docNum | rename docNum as ewmIdoc] | eval ewmIdoc=ltrim(tostring(ewmIdoc),"0") | fields ewmIdoc ewmIdocStatus ewmmesType ewmCreateTime whoNum,Interface,wmcreateDateTime,wmconfirmDateTime,wmsentDateTime | join type=outer whoNum [ search index=lsc_exacta_qa_index source="D:\\ProgramData\\Bastian Software\\Logs\\ExactaImportAdapter\\ExactaImportAdapter*" | rex field=_raw ".* ORDER_NAME=\"(?<imaWho>[\d-]+)\" .*" | rex field=_raw ".*JSON received for product import:.*\"product\":\"(?<imaWho>[\d-]+)\",.*" | rex field=_raw ".*JSON received for putaway import:.*\"who\":\"(?<imaWho>[\d-]+)\",.*" | eval exactaRecTime = strftime(_time,"%Y-%m-%d %H:%M:%S") | dedup imaWho sortby +exactaRecTime | eval exactaInfStatus = if(exactaRecTime != "","Success",NA) | table imaWho exactaRecTime exactaInfStatus | join type=outer imaWho [ search index=lsc_exacta_qa_index source="D:\\ProgramData\\Bastian Software\\Logs\\ExactaImport\\ExactaImport.txt" | rex field=_raw ".* Order \[(?<imWho>[\d-]+) - .*\] successfully assigned.*" | rex field=_raw "\.* Bastian\.Exacta\.Interface\.Processes\.ExactaProductTranslatorBase - Validation of Message Successfull, Prepare to Insert\n.*ROWS ONLY;\@p0 = \'(?<imWho>[\d-]+)\'.*\[.*" | rex field=_raw ".*\/line id \[(?<imWho>[\d-]+) -.* was cancelled successfully.\n.*" | rex field=_raw ".*\[Import Pick Orders\].*ROWS ONLY;@p0 = \'(?<imWho>[\d-]+)\' \[[\S\s]*- Messages processed successfully.*" | eval exactaDocTime = strftime(_time, "%Y-%m-%d %H:%M:%S") | search imWho !="" | eval exactaDocStatus = if(exactaDocTime != "","Created",NA) | table imWho exactaDocTime exactaDocStatus | rename imWho as imaWho] | table imaWho exactaRecTime exactaDocTime exactaInfStatus exactaDocStatus | rename imaWho as whoNum] | search Interface = "*" | rename whoNum as "WHO/PRODUCT" | table ewmIdoc ewmIdocStatus ewmmesType ewmCreateTime "WHO/PRODUCT",Interface,wmcreateDateTime, wmsentDateTime, wmconfirmDateTime, exactaRecTime exactaDocTime exactaInfStatus exactaDocStatus

If I look that correctly you have wrong field name (imaWho) on you red block. On join you mast have same column on join (whoNum) and sub search! Probably you will have the same situation on the next join after you have fixed this first issue!

Anyhow join is not a preferred method to join events on Splunk. You should read the next articles and conf presentation to do better / more powerful joins.

r. Ismo

topic want to use field value of 1st search in join search query to filter event of search query inside join. in Dashboards & Visualizations

want to use field value of 1st search in join search query to filter event of search query inside join.

Re: want to use field value of 1st search in join search query to filter event of search query inside join.