Splunk Search
Highlighted

Regex to extract fields from CSV file based on number of delimiters?

Hi,

I have two types of logs:

Log1:

Jun 18 14:10:57 lec05674568 ABC[455135]: 2015-06-18 14:10:57;indexserver;lec056741;XBE;06;30893;11.91.6.128;lec056287312;9580;9599;LEC Audit - SYSTEM Logins;CRITICAL;CONNECT;SYSTEM;;;;;;DAPNAPS;UNSUCCESSFUL;;;;;;invalid username or password;;5562789;;

Log2:

Jun 18 14:10:57 lec05674568 ABC[455135]: 2015-06-18 14:10:57;indexserver;lec056741;XBE;06;30893;DATABASE01;11.91.6.128;lec056287312;9580;9599;LEC Audit - SYSTEM Logins;CRITICAL;CONNECT;SYSTEM;;;;;;DAPNAPS;UNSUCCESSFUL;;;;;;invalid username or password;;5562789;;

If i use

^([^;]*;){10}(?<Field_Name>[^;]*) 

based on the number of delimiters ;, I get wrong output from Log2 as there is an Extra field (DATABASE01)

I need to count the number of delimiters and then extract the field based on the count.

Please advice.

Thanks
Nancy

Highlighted

Re: Regex to extract fields from CSV file based on number of delimiters?

SplunkTrust
SplunkTrust

Hi nancylawrence007,

Or you start from the end of the event, if the end is always ;VPN; you can use this regex:

;(?<Event_TimeStamp>\d+:\d+);VPN;

cheers, MuS

Highlighted

Re: Regex to extract fields from CSV file based on number of delimiters?

Contributor

Nice answer MuS.

Just thinking that if the event ending is different, then you may also be able to just extract the time with something like this (if no other fields match the time pattern) .

... |eval Event_TimeStamp=_raw |rex mode=sed field=Event_TimeStamp "s/([^;]+;)+(\d\d:\d\d)(;.*$)/\2/"
0 Karma
Highlighted

Re: Regex to extract fields from CSV file based on number of delimiters?

Contributor

Though, thinking about it again, if the field time pattern is unique across all fields, then you do not need to worry about the number of delimiters (or sed) and the following would work too

 rex "([^;]+;)+(?\d\d:\d\d);"
0 Karma
Highlighted

Re: Regex to extract fields from CSV file based on number of delimiters?

Logs above are just examples, exact logs differ but are separated by delimiters

0 Karma
Highlighted

Re: Regex to extract fields from CSV file based on number of delimiters?

SplunkTrust
SplunkTrust

either provide the exact log events or try the second regex mentioned by @gcato

0 Karma
Highlighted

Re: Regex to extract fields from CSV file based on number of delimiters?

Jun 18 14:10:57 lec05674568 ABC[455135]: 2015-06-18 14:10:57;indexserver;lec056741;XBE;06;30893;10.81.5.128;lec056287312;9580;9599;LEC Audit - SYSTEM Logins;CRITICAL;CONNECT;SYSTEM;;;;;;DAPNAPS;UNSUCCESSFUL;;;;;;invalid username or password;;5562789;;

0 Karma
Highlighted

Re: Regex to extract fields from CSV file based on number of delimiters?

SplunkTrust
SplunkTrust

thanks 🙂 in this example, where is the needed time ?

0 Karma
Highlighted

Re: Regex to extract fields from CSV file based on number of delimiters?

SplunkTrust
SplunkTrust

sorry to ask again, but which is the value you want to get for both examples?
Your regex captures from Log1:

9599;LEC Audit - SYSTEM Logins

and from Log2:

9580;9599

If you don't provide this information there is no way to help you with this problem.

0 Karma
Highlighted

Re: Regex to extract fields from CSV file based on number of delimiters?

i need to extract all the fields

0 Karma