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

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

sunket6006 — Mon, 14 Jun 2021 19:31:59 GMT

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 ::"

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

venkatasri — Tue, 15 Jun 2021 05:30:34 GMT

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!

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

sunket6006 — Tue, 15 Jun 2021 14:04:24 GMT

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

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

ITWhisperer — Tue, 15 Jun 2021 14:23:21 GMT

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]

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

sunket6006 — Tue, 15 Jun 2021 15:40:08 GMT

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.

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

sunket6006 — Tue, 15 Jun 2021 15:46:08 GMT

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

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

ITWhisperer — Tue, 15 Jun 2021 16:15:06 GMT

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

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

sunket6006 — Tue, 15 Jun 2021 16:31:57 GMT

I am sorry, not working as expected.

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

ITWhisperer — Tue, 15 Jun 2021 16:49:12 GMT

| 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?

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

sunket6006 — Tue, 15 Jun 2021 16:50:48 GMT

I get no results.

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

ITWhisperer — Tue, 15 Jun 2021 16:56:48 GMT

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

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

sunket6006 — Tue, 15 Jun 2021 17:03:00 GMT

what is _raw here?

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

ITWhisperer — Tue, 15 Jun 2021 17:38:45 GMT

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

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

sunket6006 — Tue, 15 Jun 2021 17:53:26 GMT

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

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

ITWhisperer — Tue, 15 Jun 2021 18:00:54 GMT

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

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

sunket6006 — Tue, 15 Jun 2021 21:21:07 GMT

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.

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

ITWhisperer — Tue, 15 Jun 2021 19:01:02 GMT

| 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

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

sunket6006 — Tue, 15 Jun 2021 19:06:20 GMT

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

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

sunket6006 — Tue, 15 Jun 2021 19:07:07 GMT

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