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

View solution in original post

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

niketnilay
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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.