Splunk Search

Use regex on a known date field

jamescasey2
New Member

First, new to regex, so don't really know where to start. I've done some Google searching and up and down Splunk Answers.

I am creating a POC to search data and it has a known date field. The date field can be populated as d/m/yyyy, dd/m/yyyy, d/mm/yyyy, or dd/mm/year. It always follows the format of day/month/year separated by slashes.

Examples:
1/1/2017
1/11/2017
11/1/2017
11/11/2017

What I would like to do is extract that day month and year as independent pieces to analyze. Id like to extract values based on the slash.

Start point: FiledDate = m/d/yyyy
End point: FiledDateMonth = m , FiledDateDay = d , FiledDayYear = yyyy

Thanks for your help.

0 Karma

woodcock
Esteemed Legend

If you indexed it properly with TIME_FORMAT = %m/%d/%Y, then you can just do this:

| eval date_month=strftime(_time, "%m")
| eval date_mday=strftime(_time, "%d")
| eval date_year=strftime(_time, "%Y")
0 Karma

somesoni2
SplunkTrust
SplunkTrust

There can be multiple methods for it.

Using rex command

your search with field FiledDate | rex field=FiledDate "(?<FiledDateDay>\d+)\/(?<FiledDateMonth>\d+)\/(?<FiledDateYear>\d+)"

Using time conversion commands

your search with field FiledDate | eval temp=strptime(FiledDate,"%d/%m/%Y") | eval FiledDateDay=stftime(temp,"%d") | eval FiledDateMonth=stftime(temp,"%m") | eval FiledDateYear=stftime(temp,"%Y")

Using String split command

your search with field FiledDate | eval FiledDateDay=mvindex(split(FiledDate,"/"),0) | eval FiledDateMonth=mvindex(split(FiledDate,"/"),1)| eval FiledDateYear=mvindex(split(FiledDate,"/"),2)
0 Karma

cpetterborg
SplunkTrust
SplunkTrust

Use the following :

(?P<FiledDateMonth>\d+)/(?P<FiledDateDay>\d+)/(?P<FiledDayYear>\d{4})

It will match all of your data parameters. In a rex command it would look like this:

... | rex field=FiledDate "(?P<FiledDateMonth>\d+)/(?P<FiledDateDay>\d+)/(?P<FiledDayYear>\d{4})"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...