License Saturation in SQL?

Hi All,

There is a Orion.LicenseSaturation table in SWQL.   Is there anything similar on the SQL tables?

Thank you,

Amit

  • Not that I can see, and you should avoid making calls directly to SQL whenever possible.  There's always a chance that the underlying database can change, but the SolarWinds Information Service (and thereby SWQL) shouldn't change.

  • I totally agree.   95% of the time I use SWQL.   Sometimes I find that on more complicated queries, going through Information Service can be a bit slow and the widget timesouts.   Whenever I want the fastest speed possible, I'll go the SQL route (taking into account that it does not use account limitations among other things).

  • From a technical sense, I don't know if the information is in SQL, but my guess is that it is somewhere - unless it's buried in the file system or something even more obscure..  However finding it (or collecting it across multiple tables if you have multiple products) seems more of a pain in this case.

    I've found that if you avoid JOINS and instead use Navigation Properties my SWQL queries run quicker.  It's only by a few milliseconds for me (but I only have a small server for testing).  It might be a larger time save on larger data sets.

  • It appears that there is not a corresponding SQL table. I queried Orion.LicenseSaturation in SWQL Studio using the WITH QUERYPLAN hint. When a query pulls data from SQL tables, the query plan will show an OptType of "ProviderPassThroughScanOp" query - that's the text of the SQL query that's sent to SQL Server.

    This is my query against Orion.LicenseSaturation:

    Select ElementType, ElementCount, MaxCount from Orion.LicenseSaturation with QUERYPLAN

    And this is the query plan:

    - <queryPlan type="physical" xmlns="schemas.solarwinds.com/.../informationservice">
    - <op type="ProjectOp">
    - <selectors>
     <selector name="T1.C1" type="String" expression="PropertyRef/T1.C1" />
     <selector name="T1.C2" type="Int32" expression="PropertyRef/T1.C2" />
     <selector name="T1.C3" type="Int32" expression="PropertyRef/T1.C3" />
     </selectors>
    - <metaData>
     <field name="T1.C1" type="String" ordinal="0" xmlPath="LicenseSaturation[EntityType=Orion.LicenseSaturation]/@ElementType" />
     <field name="T1.C2" type="Int32" ordinal="1" xmlPath="LicenseSaturation[EntityType=Orion.LicenseSaturation]/@ElementCount" />
     <field name="T1.C3" type="Int32" ordinal="2" xmlPath="LicenseSaturation[EntityType=Orion.LicenseSaturation]/@MaxCount" />
     </metaData>
    - <children>
    - <op type="ProviderScanOp" name="T1" entity="Orion.LicenseSaturation">
    - <metaData>
     <field name="T1.C1" type="String" ordinal="0" />
     <field name="T1.C2" type="Int32" ordinal="1" />
     <field name="T1.C3" type="Int32" ordinal="2" />
     </metaData>
     <children />
     </op>
     </children>
     </op>
     </queryPlan>

    As you see, there's no reference to a ProviderPassThroughScanOp.

    By contrast, here's a query against Orion.Nodes:

    SELECT TOP 10 Caption, IP from Orion.Nodes WITH QUERYPLAN

    And here is its query plan:

    - <queryPlan type="physical" xmlns="schemas.solarwinds.com/.../informationservice">
    - <op type="ProjectOp">
    - <selectors>
     <selector name="T1.C1" type="String" expression="PropertyRef/T1.C1" />
     <selector name="T1.C2" type="String" expression="PropertyRef/T1.C2" />
     </selectors>
    - <metaData>
     <field name="T1.C1" type="String" ordinal="0" xmlPath="nodes[EntityType=Orion.Nodes]/@caption" />
     <field name="T1.C2" type="String" ordinal="1" xmlPath="nodes[EntityType=Orion.Nodes]/@ip" />
     </metaData>
    - <children>
    - <op type="ProviderPassThroughScanOp">
     <passThrough>SELECT TOP 10 [T1].[Caption] AS C1, [T1].[IP_Address] AS C2 FROM [dbo].[NodesData] AS T1 WITH QUERYPLAN RETURN XML Raw</passThrough>
    - <metaData>
     <field name="T1.C1" type="String" ordinal="0" />
     <field name="T1.C2" type="String" ordinal="1" />
     </metaData>
     <children />
     </op>
     </children>
     </op>
     </queryPlan>