Splunk Search Query which should match date format shown in logs.

[sunket6006]

From file =/apps_data_01/scds2/billing/processed/ICD_TXN2_210613.csv To node =snode=MCCDPVPN To user = To file =DTF.AR.R8D1.C.E0084977.D210614.T120015.C003 To permiss = To disp =rpl Xfer type =text Xfer late = DCB =RECFM=VB,LRECL=27994,BLKSIZE=27998 SPACE = sysopts =:datatype=text:strip.blanks=no: disp =disp=(rpl) dcbline =DCB=(RECFM=VB,LRECL=27994,BLKSIZE=27998) UC7 posting info =run task snode (pgm=U7SVC) sysopts="CL46'D=DTF.AR.R8D1.C.E0084977.D210614.T120015.C003'" volume ser info =/* mainframe volume info not needed */ unit info =UNIT=PROD status file =/sys_apps_01/cdunix/ndm/work/cjb4stl181/ process name =cd11547 return code =4 date/time =Mon Jun 14 12:00:49 CDT 2021 ===================================================================================== 21/06/14 12:00:49 [ERROR] - Execution failure in Transferring Transaction Billing File :: 210613

Collapse

• host = cjb4stl181
• source = /apps_data_01/scds2/billing/processed/txn_billing_gft.log
• sourcetype = securecode:billing:txn_gft

Above are the logs shown in splunk. Splunk query used for it is below. I need a query which should match the date -210613. Please help me.

index=securecode host IN (cjb4stl181) sourcetype=securecode:billing:txn_gft "Execution failure in Transferring Transaction Billing File ::" 

[venkatasri]

Hi @sunket6006 

See if following works, file_billing field having the date format that you require extracted from file ends with .csv. 

index=securecode host IN (cjb4stl181) sourcetype=securecode:billing:txn_gft "Execution failure in Transferring Transaction Billing File ::"
| rex field=file "(?<file_billing>\d+)\.csv" 
| table file file_billing

-----

An upvote would be appreciated if if helps!

[sunket6006]

Hi @venkatasri 

I want to filter the below line

21/06/14 12:00:49 [ERROR] - Execution failure in Transferring Transaction Billing File :: 210613

Search query should match only the above statement and filter it. There is only one event occurring everyday with the date of previous day. The query you suggested also showing few other events that I don't want.

I am also trying the below query

index=securecode host IN (cjb4stl181) sourcetype=securecode:billing:txn_gft "*Execution failure in Transferring Transaction Billing File :: " .strftime(relative_time(now(), "-1d@d"), "%y%m%d") . "*"

which matches to - Execution failure in Transferring Transaction Billing File :: 210613 but that expression is not evaluating. Do you know why

[ITWhisperer]

Try something like this

index=securecode host IN (cjb4stl181) sourcetype=securecode:billing:txn_gft "*Execution failure in Transferring Transaction Billing File :: " [| eval yesterday=strftime(relative_time(now(), "-1d@d"), "%y%m%d") | return yesterday]

[sunket6006]

That one is also not working. I am able to match the sentence upto 

Execution failure in Transferring Transaction Billing File :: 

There are previous 10 events with the same statement but got different dates as below. But I want only want one event with the search matching yesterdays event which is 210614.

Execution failure in Transferring Transaction Billing File :: 210614

Execution failure in Transferring Transaction Billing File :: 210613

Execution failure in Transferring Transaction Billing File :: 210611

Execution failure in Transferring Transaction Billing File :: 210610

[sunket6006]

That one is also not working. I am able to match the sentence upto 

Execution failure in Transferring Transaction Billing File :: 

There are previous 10 events with the same statement but got different dates as below. But I want only want one event with the search matching yesterdays event.

[ITWhisperer · Accepted solution]

index=securecode host IN (cjb4stl181) sourcetype=securecode:billing:txn_gft
| where match(_raw,strftime(relative_time(now(), "-1d@d"), "Execution failure in Transferring Transaction Billing File :: %y%m%d"))

[sunket6006]

I am sorry, not working as expected.

[ITWhisperer]

| makeresults count=5
| streamstats count as row 
| eval _raw="other stuff before Execution failure in Transferring Transaction Billing File :: 21061".(row-1)." other stuff afterwards"
| fields - _time row
| where match(_raw,strftime(relative_time(now(), "-1d@d"), "Execution failure in Transferring Transaction Billing File :: %y%m%d"))

The first 4 lines set up some dummy data (if you just run those, you will see the 5 lines of dummy data). The last line just matches with yesterday's date.

Which part of this does not work for you? Do you get no results, or too many?

[sunket6006]

I get no results.

[ITWhisperer]

You could try this (I had added the "Execution failure ..." string to the match in case the date appeared in the message somewhere else but perhaps that was unnecessary)

index=securecode host IN (cjb4stl181) sourcetype=securecode:billing:txn_gft
"Execution failure in Transferring Transaction Billing File ::"
| where match(_raw,strftime(relative_time(now(), "-1d@d"), "%y%m%d"))

[sunket6006]

what is _raw here?

[ITWhisperer]

_raw is a special field that holds the whole event so the match against _raw is similar (but not identical) to doing a search, but the match will allow you to use a calculated value e.g. the date.

[sunket6006]

not working , is it possible for you to see the logs I can show you.

[ITWhisperer]

Sure - send me a private message if you don't want to share here

[sunket6006]

Below are the 2 events for example listed I apply search Query - index=securecode host IN (cjb4stl181) sourcetype=securecode:billing:txn_gft "Execution failure in Transferring Transaction Billing File ::"

But I only want todays event that should match date "210614", I have tried your queries none of them working fine. Please help.

[ITWhisperer]

| makeresults
| eval _raw="=====================================================================================
From file =/apps_data_01/scds2/billing/processed/ICD_TXN2_210613.csv To node =snode=MCCDPVPN To user = To file =DTF.AR.R8D1.C.E0084977.D210614.T120015.C003 To permiss = To disp =rpl Xfer type =text Xfer late = DCB =RECFM=VB,LRECL=27994,BLKSIZE=27998 SPACE = sysopts =:datatype=text:strip.blanks=no: disp =disp=(rpl) dcbline =DCB=(RECFM=VB,LRECL=27994,BLKSIZE=27998) UC7 posting info =run task snode (pgm=U7SVC) sysopts=\"CL46'D=DTF.AR.R8D1.C.E0084977.D210614.T120015.C003'\" volume ser info =/* mainframe volume info not needed */ unit info =UNIT=PROD status file =/sys_apps_01/cdunix/ndm/work/cjb4stl181/ process name =cd11547 return code =4 date/time =Mon Jun 14 12:00:49 CDT 2021
=====================================================================================
21/06/14 12:00:49 [ERROR] - Execution failure in Transferring Transaction Billing File :: 210613|=====================================================================================
From file =/apps_data_01/scds2/billing/processed/ICD_TXN2_210614.csv To node =snode=MCCDPVPN To user = To file =DTF.AR.R8D1.C.E0084977.D210615.T120010.C003 To permiss = To disp =rpl Xfer type =text Xfer late = DCB =RECFM=VB,LRECL=27994,BLKSIZE=27998 SPACE = sysopts =:datatype=text:strip.blanks=no: disp =disp=(rpl) dcbline =DCB=(RECFM=VB,LRECL=27994,BLKSIZE=27998) UC7 posting info =run task snode (pgm=U7SVC) sysopts=\"CL46'D=DTF.AR.R8D1.C.E0084977.D210615.T120010.C003'\" volume ser info =/* mainframe volume info not needed */ unit info =UNIT=PROD status file =/sys_apps_01/cdunix/ndm/work/cjb4stl181/ process name =cd27950 return code =4 date/time =Tue Jun 15 12:00:44 CDT 2021
=====================================================================================
21/06/15 12:00:44 [ERROR] - Execution failure in Transferring Transaction Billing File :: 210614"
| eval events=split(_raw,"|") 
| mvexpand events
| eval _raw=events
| fields - _time events
| where match(_raw,strftime(relative_time(now(), "-1d@d"), "Execution failure in Transferring Transaction Billing File :: %y%m%d"))

This appears to work 

[sunket6006]

You should not use the events which I have given, there are 100 of previous events like that

[sunket6006]

You are not getting my question preoperly, can we talk on the phone?