Getting Data In

In a CSV file, how do you sort date and time separately?

mdmaala
Communicator

hi!

I have a CSV file that indicates Date and Time in one column and is displayed like this 2019/03/05 17:05:04 I want to sort the date and time separately.

is there any way I can do this? thanks!

Tags (2)
0 Karma
1 Solution

vnravikumar
Champion

Hi @mdmaala

You can try this rex also, it will result in two new columns Date and Time

| makeresults 
| eval date_time="2019/03/05 17:05:04" 
| rex field=date_time "(?P<Date>\d{4}\/\d{2}\/\d{2})\s(?P<Time>\d{2}\:\d{2}\:\d{2})"

View solution in original post

0 Karma

vnravikumar
Champion

Hi @mdmaala

You can try this rex also, it will result in two new columns Date and Time

| makeresults 
| eval date_time="2019/03/05 17:05:04" 
| rex field=date_time "(?P<Date>\d{4}\/\d{2}\/\d{2})\s(?P<Time>\d{2}\:\d{2}\:\d{2})"
0 Karma

mdmaala
Communicator

Thank you so much! it works perfectly. but how about if I want to separate the date and time for all the date and time included in the table? is that possible?

0 Karma

mdmaala
Communicator

I see now I understand. In my table the field name is Date_Time so what I did was this:

source="C:\Users\NPIE-MDMAALA\Documents\PATLITE\*" host="DESKTOP-G546H69" sourcetype="csv" | rename "Green information" AS "Green" | rename "Amber information" AS "Amber" | rename "Red information" AS "Red" | rename "User name" AS Username | rename "MAC Address" AS "MAC_add" | rex field=Date_Time "(?P\d{4}\/\d{2}\/\d{2})\s(?P\d{2}:\d{2}:\d{2})" |

is this correct?

0 Karma

vnravikumar
Champion

Hi @mdmaala, specify your datetime field in the rex statement

your query...| rex field=date_time "(?P<Date>\d{4}\/\d{2}\/\d{2})\s(?P<Time>\d{2}\:\d{2}\:\d{2})"

here date_time is the field name that you want to split, It will give two fields Date and Time.
Please let me know if you need more info.

0 Karma

mdmaala
Communicator

Oh! Never mind! I already figured out what to do based on your last comment. and It really worked well! thank you so much @vnravikumar !!

0 Karma

niketn
Legend

@mdmaala, try the following regular expression based extraction (with run anywhere example based on your data). If the field name is DateTime the regex extract two fields Date and Time separately.

| makeresults
| eval DateTime="2019/03/05 17:05:04"
| rex field=DateTime "^(?<Date>[^\s]+)\s(?<Time>.*)$"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...