Here is the one that we use
SELECT ArchiveName AS 'Archive', FolderName AS 'Folder', [Offline] AS 'Index Offline', Failed AS 'Index Failed', Rebuilding AS 'Index Rebuilding',
failedItems AS 'Failed Items'
FROM Archive a, IndexVolume iv
WHERE a.RootIdentity = iv.RootIdentity and
([Offline] = 'True' or Failed = 'True' or Rebuilding = 'True' or failedItems <> '')
We have it as a scheduled job to be run on SQL reporting services so it runs 3 times a day