Channels
#general
Title
# general
s
Sree
09/10/2020, 5:50 PMHow to convert last_execution_time to a human readable time format ? With unix, we can use the datetime(local_time, ‘unixepoch’, ‘localtime’) .. however, this logic is not working in windows
z
Zach Zeid
09/10/2020, 6:00 PM Copy code
select local_time from time;
> local_time = 1529608143 <-- in unix epoch time
select datetime(local_time, 'unixepoch', 'localtime') as
formatted_time from time;
> formatted_time = 2018-06-21 15:09:09This would also be a good question for #sql
f
fritz
09/10/2020, 6:48 PMI am confused, this seems to convert just fine?
Copy code
SELECT p.path, datetime(p.last_execution_time, 'unixepoch', 'localtime') AS human_readable_last_exec, p.count, u.username FROM userassist p join users u ON u.uuid = p.sid ORDER BY p.count DESC; Copy code
+-------------------------------------------------------------------------+--------------------------+-------+--------------+
| path | human_readable_last_exec | count | username |
+-------------------------------------------------------------------------+--------------------------+-------+--------------+
| Microsoft.*****************_8wekyb3d8bbwe!App | 2020-09-09 18:41:13 | 29 | kolide-razer |
| Microsoft.Windows.Explorer | 2020-09-10 14:14:04 | 7 | kolide-razer |
| {1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\SnippingTool.exe | 2020-09-10 13:45:49 | 6 | kolide-razer |
| C:\Users\kolide-razer\***************\*****************.exe | 2020-09-09 18:41:11 | 6 | kolide-razer |
| {0139D44E-6AFE-49F2-8690-3DAFCAE6FFB8}\Accessories\Snipping Tool.lnk | 2020-09-10 13:45:49 | 6 | kolide-razer |
| {9E3995AB-1F9C-4F13-B827-48B24B6C7174}\TaskBar\File Explorer.lnk | 2020-09-10 11:33:34 | 6 | kolide-razer |
| {1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\cmd.exe | 2020-09-10 13:22:46 | 5 | kolide-razer |
| {A77F5D77-2E2B-44C3-A6A2-ABA601054A51}\System Tools\Command Prompt.lnk | 2020-09-10 13:22:46 | 5 | kolide-razer |@Sree your original query does not have the
datetime13 Views