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