How to make another search in if condition using eval

[mnarmada]

Hello,

I think this might be simple but need some guidance. Any help would be really appreciated.

I have a log and in which, I have to check the successful transmission for all countries.
When some transmission is failed, I have to show the reason or error for that country.

Below is the sample data:

Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Initializing Communications...
Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Sending Sender Information...
Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Sending Recipient Information...
Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Sending Message...
Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Transmission Complete
Success--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at "WIMS.SP_BUILD_FCST", line 573
- ForeCast data committed successfully.
Failed--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at "WIMS.SP_BUILD_FCST", line 573
- RFS calculation failed
Trace--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at "WIMS.SP_BUILD_FCST", line 573
- Connecting to SMTP server for attempt:1
Status--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at "WIMS.SP_BUILD_FCST", line 573
- Connecting to SMTP Server (notesGWEUR.MICHELIN.com )...
Status--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at "WIMS.SP_BUILD_FCST", line 573
- Initializing Communications...

Here "KE" and "MY" are the countries.

I have tried like below, but giving errors.

| makeresults
| eval Possible_ORs="AU,MY,KE,JP,SI,VN,ID,TD,KO,J1"
| eval Possible_ORs=split(Possible_ORs, ",")
| mvexpand Possible_ORs
| eval count=0
| rename Possible_ORs as "ORs"
| fields - _time
| append [| search sourcetype=RFS_Log | rex "Status\W+(?P<Date>\d{1,2}\/\d{1,2}\/\d+\s+\d+\W+\d+\W+\d+).*(?P<OR_NAME>[A-Z]{2}).*Transmission\s+Complete"
| eval Date=strftime(Date, "%Y-%m-%d %H:%M:%S")
| eval ORs=OR_NAME | eval ORs = split(ORs,",") | mvexpand ORs | eval count=1
| fields - _raw _time]
| dedup ORs sortby - count
| eval Job Name=case(count>=0, "RFS Calculation")
| eval Status=case(count>0, "Calculation Successful", count=0, "Calculation Failed")
| eval Status=if(isnull(Status), "Calculation Failed", Status)
| eval Reason=if(Status="Calculation Failed",
[search sourcetype=RFS_Log
| rex "Status\W+\d{1,2}\/\d{1,2}\/\d+\s+\d+\W+\d+\W+\d+.*(?P<OR_NAME>[A-Z]{2}).*violated"],
"failed")

[to4kawa]

| makeresults
| eval _raw="Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Initializing Communications...
Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Sending Sender Information...
Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Sending Recipient Information...
Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Sending Message...
Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Transmission Complete
Success--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at \"WIMS.SP_BUILD_FCST\", line 573
- ForeCast data committed successfully.
Failed--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at \"WIMS.SP_BUILD_FCST\", line 573
- RFS calculation failed
Trace--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at \"WIMS.SP_BUILD_FCST\", line 573
- Connecting to SMTP server for attempt:1
Status--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at \"WIMS.\", line 573
- Connecting to SMTP Server (notesGWEUR.MICHELIN.com )...
Status--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at \"WIMS.SP_BUILD_FCST\", line 573
- Initializing Communications..."
| rex mode=sed "s/(?ms)^(\w+--)/#\1/g"
| makemv delim="#" _raw
| rename _raw as raw
| mvexpand raw
| rename raw as _raw
| rex "(?ms)(?<status>\w+)\-\-(?<date>\S+ \S+)\-\-\w+\-\-\((?<country>\w\w)\)\-\-\-?\d+ :.*\- (?<message>[^-]+)"
| eval _time=strptime(date,"%d/%m/%Y %T")

I'm not sure the status and condition.

[mnarmada]

Hello @to4kawa ,

Thanks for the response!

Let me put the question more clear.

For example, I have 10 countries, for which I have to check whether the transmission is successful or not. 

If the transmission is not success the I have to show, what error has been occurred.

In the log, Successful transmission can be determined by the below line:

Status--20/07/2020 12:18:15--CALC_RFS_TUE_PM--(KE)--0 : - Transmission Complete

If this line is not available for any of the countries, it should show the status as failed and show the error message in the reason column.

Expected Output:

SNo       Country                 Date                                Status                  Reason

1             KE               2020-07-20 12:18:15           Successful             NA

2             AU               2020-07-20 12:18:15           Successful            NA

3              SI                 2020-07-20 12:18:15           Successful            NA

4             MY               2020-07-20 12:18:15            Failed                       Failed--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(MY)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at "WIMS.SP_BUILD_FCST", line 573
- RFS calculation failed

5                TD                 2020-07-20 12:18:15              Failed                       Failed--20/07/2020 12:19:10--CALC_RFS_TUE_PM--(TD)---2207217873 :ORA-00001: unique constraint (WIMS.PK_TB_TRN_FCST_DAILY) violated
ORA-06512: at "WIMS.SP_BUILD_FCST", line 573
- RFS calculation failed

[to4kawa]

| where searchmatch("Transmission Complete") OR status="Failed"
| eval Status=if(status="Failed",status,"Successful")
| eval Reason = if(status="Failed",_raw,"NA")

add above.

[mnarmada]

Please reply if someone know the how to figure it out..