Runs on top of OlaHallengren's Logging table.
Create this view, and change the path to the commandlog table if needed.
A common query for me is
select * from vcommandlog where Fragmentation > 25 and cast(starttime as date) = cast(getdate() as date)
go
select * from vcommandlog where DurationInSeconds > 60 and cast(starttime as date) = cast(getdate() as date)
go
create view [dbo].[VCommandLog] as
SELECT [ID],[DatabaseName],[SchemaName],[ObjectName]
,[ObjectType],[IndexName],[IndexType],[StatisticsName]
,[PartitionNumber],[ExtendedInfo],[Command]
,[CommandType],[StartTime],[EndTime]
,[ErrorNumber],[ErrorMessage]
,datediff(second,startTime,EndTime) [DurationInSeconds]
,ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]','bigint') as [pagecount]
,ExtendedInfo.value('(/ExtendedInfo/Fragmentation)[1]','numeric(7,5)') as [Fragmentation]
FROM [dbo].[CommandLog]
GO
--Then use like so
select * from vcommandlog where Fragmentation > 25 and cast(starttime as date) = cast(getdate() as date)
go
select * from vcommandlog where DurationInSeconds > 60 and cast(starttime as date) = cast(getdate() as date)
go