3 Replies Latest reply on Apr 21, 2017 5:40 AM by ludek.frey

    Tying SQL and SWQL together - help please

    yaquaholic

      Hi there,

       

      It appears that an alert has been setup that goes like this (SWQL):

       

      SELECT E0.[Uri], E0.[DisplayName]

      FROM Orion.VIM.Hosts AS E0

      WHERE  ( E0.[MemUsageThreshold].[CurrentValue] = E0.[MemUsageThreshold].[Level2Value] )

       

      I managed to expand on this, to return some more details, so now I can see the current memory utilisation and the two thresholds (critical and warning respectively): 

       

      SELECT E0.Uri, e0.HostID, E0.HostName, E0.NodeID, E0.MemUsageThreshold.CurrentValue, E0.MemUsageThreshold.Level2Value, E0.MemUsageThreshold.Level1Value

      FROM Orion.VIM.Hosts AS E0

       

      However, someone has asked me to tweak these thresholds on a few hosts and despite several hours of investigating in VIM and in SQL with the VIM_Host* tables,I am no closer to finding where I can adjust these thresholds, in Orion or by SQL.

       

      The values from E0.MemUsageThreshold.CurrentValue, E0.MemUsageThreshold.Level2Value, E0.MemUsageThreshold.Level1Value appear to be very similar to those in SWQL table VIM_HostThresholds, which uses  only one ID column, InstanceID.

       

      I assumed that one of the VIM_Host* tables would tie back to the VIM_Host*Threshold tables, but these tables use an InstanceID, which I have been unable to tie back to any other table.

       

      I have listed and search all table with InstanceID as a column name

       

      Select table_name

      From information_schema.columns

      Where column_name = 'InstanceId';

       

      Results in:

      table_name

      VIM_ClusterThresholds

      VIM_ClusterCpuLoadThreshold

      VIM_ClusterMemUsageThreshold

      ESI_Instance

      VIM_DatastoreThresholds

      SNI_AlertIncidents

      VIM_DatastoreIOPSTotalThreshold

      VIM_DatastoreIOPSReadThreshold

      VIM_DatastoreIOPSWriteThreshold

      VIM_DatastoreLatencyTotalThreshold

      VIM_DatastoreLatencyReadThreshold

      VIM_DatastoreLatencyWriteThreshold

      VIM_HostThresholds

      VIM_HostCpuLoadThreshold

      VIM_HostMemUsageThreshold

      VIM_HostNetworkUtilizationThreshold

      VIM_VirtualMachineThresholds

      VIM_VirtualMachineCpuLoadThreshold

      VIM_VirtualMachineMemUsageThreshold

      VIM_VirtualMachineCpuReadyThreshold

      VIM_VirtualMachineIOPSTotalThreshold

      VIM_VirtualMachineIOPSReadThreshold

      VIM_VirtualMachineIOPSWriteThreshold

      VIM_VirtualMachineLatencyTotalThreshold

      VIM_VirtualMachineLatencyReadThreshold

      VIM_VirtualMachineLatencyWriteThreshold

      VIM_VirtualMachineNetworkUsageRateThreshold

      DPI_Thresholds

      DPI_ApplicationsThresholds

      NodesThresholds

      NodesCpuLoadThreshold

      NodesPercentMemoryUsedThreshold

      NodesResponseTimeThreshold

      NodesPercentLossThreshold

      NodesForecastCapacity

      NodesPercentMemoryUsedForecastCapacity

      NodesCpuLoadForecastCapacity

      Thresholds

      WirelessHeatMap_MapPoint

      ForecastCapacitySettings

      WorldMapPoints

      InterfacesThresholds

      VolumesForecastCapacity

      InterfacesInErrorsDiscards

      VolumesPercentDiskUsedForecastCapacity

      InterfacesOutErrorsDiscards

      InterfacesInPercentUtilization

      InterfacesOutPercentUtilization

      InterfacesForecastCapacity

       

      So far, I have found nothing in there to allow me to tie InstanceID back with a host

       

      I am guessing there is a lookup table somewhere I am missing, does any one have any ideas?

      Or how to avoid SQL entirely and update these thresholds using the UI?

        • Re: Tying SQL and SWQL together - help please
          yaquaholic

          I have sussed from the SQL side, but not the UI.

           

          By opening the VIM_HostThreshold view, in SQL Studio, and then examining the design, I spotted the index IDs that link the tables:

           

          <blah> h.HostID AS InstanceId <blah> FROM [dbo].[VIM_Hosts]

           

          SELECT TOP (5) vh.HostID ,vh.NodeID ,vh.HostName ,vh.MemUsage ,vh.MemUsageMB

                                     ,vht.Name ,vht.Level1Value AS Lev1_Warning ,vht.Level2Value AS Lev2_Critical

          FROM VIM_Hosts vh

          INNER JOIN VIM_HostThresholds vht ON vh.HostID = vht.InstanceId

          WHERE vht.Name LIKE 'VIM.Hosts.Stats.MemUsage'

           

          Well that is enough for me to start changing the default warning threshold of 80% memory utilisation.

           

          But I may well re-ask this question to the VIM forum, as I have yet to find the UI to set these values....

          1 of 1 people found this helpful
          • Re: Tying SQL and SWQL together - help please
            yaquaholic

            Also, I cam across this on my search for answers - SolarWinds Information Service v3.0 Schema Documentation Index on

            I found this rather helpful and I am sure I will again in the future.

            1 of 1 people found this helpful