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

[mdmaala]

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!

[vnravikumar · Accepted solution]

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

[mdmaala]

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?

[mdmaala]

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?

[vnravikumar]

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.

[mdmaala]

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

[niketn]

@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>.*)$"