Useful SQL queries for OpsMgr DB

Here are some of the SQL scripts I usually use in case of… whatever.
Many of these must be credited to Kevin Holman

Set ALL agents to Remotely Managable

UPDATE MT_HealthService 
SET IsManuallyInstalled=0 
WHERE IsManuallyInstalled=1

Get Agents not Remotely Managable

select bme.DisplayName from MT_HealthService mths 
INNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityId 
where IsManuallyInstalled = 1

Set Agent Remotely Managable

UPDATE MT_HealthService 
SET IsManuallyInstalled=0 
WHERE IsManuallyInstalled=1 
AND BaseManagedEntityId IN 
(select BaseManagedEntityID from BaseManagedEntity 
where BaseManagedTypeId = 'AB4C891F-3359-3FB6-0704-075FBFE36710' 
AND DisplayName = '-- Servername Here --')

Get Unix Duplicates (Run if the *Nix agent view in console fails)

DECLARE @NeededTypeName NVARCHAR(256)
DECLARE @ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived UNIQUEIDENTIFIER
SET @NeededTypeName = 'Microsoft.Unix.OperatingSystem'
SET @ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived = (
SELECT ManagedTypeId
FROM ManagedType
WHERE TypeName = @NeededTypeName
)

SELECT
[ManagedEntityGenericView].[Id],
[ManagedEntityGenericView].[Name],
[ManagedEntityGenericView].[Path],
[ManagedEntityGenericView].[FullName],
[ManagedEntityGenericView].[LastModified],
[ManagedEntityGenericView].[TypedManagedEntityId],
NULL AS SourceEntityId

FROM dbo.ManagedEntityGenericView
INNER JOIN (
SELECT DISTINCT [BaseManagedEntityId]
FROM dbo.[TypedManagedEntity] TME WITH(NOLOCK)
JOIN [dbo].[DerivedManagedTypes] DT
ON DT.[DerivedTypeId] = TME.[ManagedTypeId]
WHERE
DT.[BaseTypeId] = @ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived AND
TME.IsDeleted = 0
) AS ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived
ON ManagedTypeIdForManagedEntitiesByManagedTypeAndDerived.[BaseManagedEntityId] = [Id]
WHERE
[IsDeleted] = 0 AND
[TypedMonitoringObjectIsDeleted] = 0 AND
[ManagedEntityGenericView].[Path] IN (
SELECT [BaseManagedEntity].[Path]
FROM [BaseManagedEntity]
GROUP BY [BaseManagedEntity].[Path]
HAVING COUNT([BaseManagedEntity].[Path]) > 1
)

ORDER BY [ManagedEntityGenericView].[Path]

Remove *NIX Duplicates

DECLARE @TypedManagedEntityId uniqueidentifier
DECLARE @LastErr INT
DECLARE @TimeGenerated DATETIME
SET @TimeGenerated = GETUTCDATE()
DECLARE EntitiesToBeRemovedCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT TME.TypedManagedEntityId
FROM TypedManagedEntity TME

WHERE TME.TypedManagedEntityId IN (' ID 1 OF THE DUPLICATE SERVER ',' ID 2 OF THE DUPLICATE SERVER')
OPEN EntitiesToBeRemovedCursor
FETCH NEXT FROM EntitiesToBeRemovedCursor
INTO @TypedManagedEntityId
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
EXEC @LastErr = [p_TypedManagedEntityDelete] @TypedManagedEntityId, @TimeGenerated

IF @LastErr <> 0
GOTO Err
COMMIT TRAN
FETCH NEXT FROM EntitiesToBeRemovedCursor
INTO @TypedManagedEntityId
END
CLOSE EntitiesToBeRemovedCursor

DEALLOCATE EntitiesToBeRemovedCursor
GOTO Done

Err:
ROLLBACK TRAN
GOTO Done
Done:
SELECT '!!! SUCCESS !!!'

Get Alerts where CustomField1 is used, and ResolutionState is 1

select  alertname, alertdescription, statesetbyuserid, resolutionstate, statesetdatetime, severity, repeatcount, ars.alertguid, customfield1
from Alert.vAlertResolutionState ars 
join alert.valert alt on ars.alertguid = alt.alertguid
join alert.valertdetail on ars.AlertGuid = vAlertDetail.AlertGuid
where ResolutionState like '1' AND customfield1 IS NOT NULL
order by statesetdatetime

Get the reason why your agent was gray

SELECT
ME.FullName,
HSO.StartDateTime AS OutageStartDateTime,
DATEDIFF (DD, hso.StartDateTime, GETDATE()) AS OutageDays,
HSO.ReasonCode,
DS.Name AS ReasonString
FROM  vManagedEntity AS ME
INNER JOIN     vHealthServiceOutage AS HSO ON HSO.ManagedEntityRowId = ME.ManagedEntityRowId
INNER JOIN     vStringResource AS SR ON HSO.ReasonCode =
REPLACE(LEFT(SR.StringResourceSystemName, LEN(SR.StringResourceSystemName)
- CHARINDEX('.', REVERSE(SR.StringResourceSystemName))), 'System.Availability.StateData.Reasons.', '')
INNER JOIN     vDisplayString AS DS ON DS.ElementGuid = SR.StringResourceGuid
WHERE (SR.StringResourceSystemName LIKE 'System.Availability.StateData.Reasons.[0-9]%')
AND DS.LanguageCode = 'ENU'
AND ME.FullName like '% SERVERNAME HERE %'
ORDER BY OutageDays

– F