This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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>