这是一个典型的孤岛和缺口问题。搜索一下，你会发现很多关于这个的参考资料

select  DeviceID, 
    [Fault Start]   = min(DateTime),
    [Fault End]     = max(DateTime),
    [Total Fault Time] = convert(varchar(8), 
                                 dateadd(second, 
                                         datediff(second, 
                                                  min(DateTime), 
                                                  max(DateTime)), 
                                         0), 
                                 108),
    [Message]   = max(Message)
from
(
    select  *,
        grp = ID - row_number() over (order by Status, ID)
    from    your_log_table
) d
where   Status  = 'Fault'
group by DeviceID, grp