Built off the open source project Osquery
This query looks for the existence of a Windows user's folder which indicates that they have logged into a system at least once. Then it also pulls the last login time and type from the Windows Event Logs.
There are two places with the username in the SQL below ('dale') that need to be changed to the user you are looking for. The instances are in bolded red.
with wel as (
select datetime as 'last_login',
json_extract(windows_eventlog.data,'$.EventData.TargetUserName') as 'username',
case json_extract(windows_eventlog.data,'$.EventData.LogonType')
when '2' then 'INTERACTIVE'
when '3' then 'NETWORK'
when '4' then 'BATCH'
when '5' then 'SERVICE'
when '7' then 'UNLOCK'
when '8' then 'NETWORK_CLEAR_TEXT'
when '9' then 'NEW_CREDENTIALS'
when '10' then 'REMOTE_INTERACTIVE'
when '11' then 'CACHED_INTERACTIVE'
end 'logon_type_description'
from windows_eventlog
where channel = 'Security'
and eventid = '4624'
and json_extract(windows_eventlog.data,'$.EventData.TargetUserName') = 'dale'
order by datetime desc
limit 1),
dir as (
select path,
filename as 'username',
datetime(btime,'unixepoch','localtime') as 'dir_created'
from file
where path = '\users\dale'
)
select *
from dir
join wel using(username);