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
