Question about Monitor PS commandlet

Luke LLuke L Posts: 43 Bronze 3
in Redgate Monitor
Hello, I'm trying to build a witness process for an automated checklist to determine if sql monitor is actually monitoring my servers and that someone didn't suspend monitoring and forget to turn it back on.

I am generally able to complete this using PS for standalone machines/instances with the following:

$MachineName is a string I will pass into the commandlet.

if (!$isCluster) {
    $MonitoredObject = Get-SqlMonitorMachine -NAME $MachineName 
    $status = Get-SqlMonitorMonitoredObjectStatus -MonitoredObject $MonitoredObject
    Write-Host "`tIs Monitored: $($status.IsMonitored)"
    Write-Host "`tStatus: $($status.Status)"
    Write-Host "`tConfiguration State: $($status.ConfigurationState)"
}

I'm having trouble with clustered instances...  below is the code I'm using currently.  in this case, @MachineName is the ClusterName.

##For Clusters, pass in the cluster name and return data about that cluster
if ($isCluster) {
    $cluster = Get-SqlMonitorCluster -Name $MachineName
      $machines = Get-SqlMonitorMachine $cluster
       foreach($machine in $machines) {
           $MonitoredObject = Get-SqlMonitorMachine -NAME $machine 
           Write-Host "MonitoredObject: " $MonitoredObject.Name
            Get-SqlMonitorMonitoredObjectStatus -MonitoredObject $MonitoredObject
           
       }

    $clusterInstances = Get-SqlMonitorInstance $cluster
    foreach($clusterInstance in $clusterInstances) {
        Write-Host $clusterInstance.Name
        Get-SqlMonitorMonitoredObjectStatus -MonitoredObject $clusterInstance
        
     }

}    

Perhaps I'm just going about this wrong.   Ideally, I want a PS module or TSQL code to call from a remote system that lets me know all is well and so I can have someone watching the watchers without someone looking at the configuration page everyday.  I'm expecting to be able to pass in as machine or instance name and get a thumbs up, thumbs down, on monitoring status for that machine or instance.

Thanks in advance.
-Luke.
Tagged:
· Share on Twitter

Answers

  • Alex BAlex B Posts: 1,158 Diamond 4
    edited September 30, 2020 11:06AM
    Hi @Luke L

    It seems you have found the "List All Monitored Entities" example and used parts of that which is good.  I'm not quite sure where the $IsCluster is coming from - are you specifying this as well?

    If you are able to pass two parameters the $EntityName (just more general than $MachineName) and something to specify whether that is a cluster, machine or SQL instance then you should be able to craft the script to find the appropriate entity and get it's status similarly to what you are doing by getting and setting the status for the standalone machine, just for the cluster level, the machine level and the sql instance level.

    Here is getting the status of the cluster, it's nodes and their instances as well as any Failover Clustered Instances (though I'm confirming the syntax around that, since without the |where-object it still returns the standalone instances on each node):
        using module .\RedgateSQM.psm1

    Connect- -ServerUrl 'http://localhost:8080' -AuthToken '<your auth>'

    $EntityName = '<yourentity>' #A cluster in this case

    $MonitoredObject = Get-Cluster -Name $EntityName
    Write-Output "Cluster: $($MonitoredObject.Name)"
    $machines = Get-Machine -Cluster $MonitoredObject
    foreach ($machine in $machines) {
        Write-Output "`tMachine: $($machine.Name)"
        $status = MonitoredObjectStatus -MonitoredObject $machine
        Write-Host "`tIs Monitored: $($status.IsMonitored)"
        Write-Host "`tStatus: $($status.Status)"
        Write-Host "`tConfiguration State: $($status.ConfigurationState)"
        $Instances = Get-Instance -Machine $machine
        foreach($Instance in $Instances) {
            Write-Host "`t`tInstance Name: $($Instance.Name)"
            $InstanceStatus = Get-MonitoredObjectStatus -MonitoredObject $Instance
            Write-Host "`t`tSuccessfully monitoring:" $InstanceStatus.IsMonitored
            Write-Host "`t`tConfiguration state:" $InstanceStatus.ConfigurationState
            Write-Host "`t`tConnection status error:" $InstanceStatus.Status
            Write-Host ""
        }
    }
    $ClusterInstances = Get-Instance -Cluster $MonitoredObject | Where-Object -Property "Cluster" -NE -Value $null
    foreach($ClusterInstance in $ClusterInstances) {
        Write-Host $ClusterInstance.Name
        $ClusterInstanceStatus = Get-MonitoredObjectStatus -MonitoredObject $ClusterInstance
        Write-Host "`tSuccessfully monitoring:" $ClusterInstanceStatus.IsMonitored
        Write-Host "`tConfiguration state:" $ClusterInstanceStatus.ConfigurationState
        Write-Host "`tConnection status error:" $ClusterInstanceStatus.Status
        Write-Host ""
    }


    There's probably some inconsistencies in formatting and such but hopefully that helps.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • Luke LLuke L Posts: 43 Bronze 3
    Alex, thanks for the reply... I'll work through it and see if I can make this work.  Yes, I used that example code as a basis for a process where I was planning to pass in a machinename or cluster name as well as set the $isCluster parameter since I would know which were FCI and which weren't. 

    The issue I ran into with the Cluster process was when I used Get-MonitoredObjectStatus to find the instances on a machine in the cluster, the first instance from the first machine, appeared with the second machine.   i.e. 4 node FCI, with 4 instances.  one instance per node on a normal day.  Get-MonitoredObjectStatus was returning nothing for the first machine but 2 instances on the second machine.

    I'll try your code and let you know if this solves the problem for me.

    Thanks again,
    -Luke.  
    · Share on Twitter
  • Luke LLuke L Posts: 43 Bronze 3
    Alex, I had to make some changes to your code.  All of the commandlets for my version of monitor powershell are named with the Keyword SqlMonitor... example: Get-SqlMonitorCluster vs. Get-Cluster  from the documentation this is a version compatibility issue... we are running 10.0.11.28553.  Based on other communication from RG, we won't be able to upgrade until after we complete a hardware refresh later this fall.  Our base monitor is installed on a windows 2012 server slated for refresh in the next few months.

    With the cluster instances I am seeing the same issue as with my code.  There is no status information returned for the first instance of a multi instance FCI.  The default instance returns: 

    (local)
    Successfully monitoring: 
    Configuration state:
    Connection status error:
     
    while the second instance returns
    NamedInstance
    Successfully monitoring: True
    Configuration state: Monitoring
    Connection status error: 
    The default instance returns nothing or NULLs?  while all named instances return the expected information.  Is this an issue because it's a default instance on a cluster?

    Thanks,
    -Luke.
    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    edited October 6, 2020 3:14PM
    Hey Luke, yeah, I can't remember what I've done to make it not recognize the alias - it should be e.g. Get-SqlMonitorCluster using the alias... I think I intentionally broke something trying to reproduce an issue and forgot what that was :-)

    Now, when I do this in the loop over all the machines (nodes) on the cluster
    $Instances = Get-SqlMonitorInstance -Machine $machineit will give me just the instances for the machine, but at the end if I only do ONLY
    $ClusterInstances = Get-SqlMonitorInstance -Cluster $MonitoredObjectIt will give me all the instances on the cluster rather than just the FCI.

    That's why I tried to use a similar thing to the part of the example script for standalone machines (rather than nodes of a cluster) - essentially modified it to only find those that are cluster instances (cluster property is not null) by piping in this
    Where-Object -Property "Cluster" -NE -Value $null

    And it seems to have worked.  When I run the script above against a 2 node cluster, with each node having a different set of standalone instances and there being an FCI, it correctly discovers the right instances on each node and the FCI at the end by itself.

    So depending on specifically what you are looking to do - i.e. if you want to put a cluster in and get everything, or a standalone machine in and get everything on it, or only put a sql instance in and only get that one instance then you would use the parts within the cluster example for each of those things - though you would need to specify the machine and the SQL instance name because different machines can have instances with the same name - so it would be:
    $SingleInstance = Get-SqlMonitorInstance -Machine "machine" -Name "instance name"

    This is using the 10.2.9 cmdles (as I haven't updated them yet after going to 10.2.11)
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
Sign In or Register to comment.