Hi All,
There is a Orion.LicenseSaturation table in SWQL. Is there anything similar on the SQL tables?
Thank you,
Amit
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.
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).
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:
<a class="b" href="#">-</a> <span class="m"><</span><span class="t">queryPlan</span> <span class="t">type</span><span class="m">="</span><b>physical</b><span class="m">"</span> <span class="ns">xmlns</span><span class="m">="</span><b class="ns"><a href="http://schemas.solarwinds.com/2007/08/informationservice">schemas.solarwinds.com/.../informationservice</a></b><span class="m">"</span><span class="m">></span>
<a class="b" href="#">-</a> <span class="m"><</span><span class="t">op</span> <span class="t">type</span><span class="m">="</span><b>ProjectOp</b><span class="m">"</span><span class="m">></span>
<a class="b" href="#">-</a> <span class="m"><</span><span class="t">selectors</span><span class="m">></span>
<span class="b"> </span><span class="m"><</span><span class="t">selector</span> <span class="t">name</span><span class="m">="</span><b>T1.C1</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>String</b><span class="m">"</span> <span class="t">expression</span><span class="m">="</span><b>PropertyRef/T1.C1</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"><</span><span class="t">selector</span> <span class="t">name</span><span class="m">="</span><b>T1.C2</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>Int32</b><span class="m">"</span> <span class="t">expression</span><span class="m">="</span><b>PropertyRef/T1.C2</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"><</span><span class="t">selector</span> <span class="t">name</span><span class="m">="</span><b>T1.C3</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>Int32</b><span class="m">"</span> <span class="t">expression</span><span class="m">="</span><b>PropertyRef/T1.C3</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"></</span><span class="t">selectors</span><span class="m">></span>
<a class="b" href="#">-</a> <span class="m"><</span><span class="t">metaData</span><span class="m">></span>
<span class="b"> </span><span class="m"><</span><span class="t">field</span> <span class="t">name</span><span class="m">="</span><b>T1.C1</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>String</b><span class="m">"</span> <span class="t">ordinal</span><span class="m">="</span><b>0</b><span class="m">"</span> <span class="t">xmlPath</span><span class="m">="</span><b>LicenseSaturation[EntityType=Orion.LicenseSaturation]/@ElementType</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"><</span><span class="t">field</span> <span class="t">name</span><span class="m">="</span><b>T1.C2</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>Int32</b><span class="m">"</span> <span class="t">ordinal</span><span class="m">="</span><b>1</b><span class="m">"</span> <span class="t">xmlPath</span><span class="m">="</span><b>LicenseSaturation[EntityType=Orion.LicenseSaturation]/@ElementCount</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"><</span><span class="t">field</span> <span class="t">name</span><span class="m">="</span><b>T1.C3</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>Int32</b><span class="m">"</span> <span class="t">ordinal</span><span class="m">="</span><b>2</b><span class="m">"</span> <span class="t">xmlPath</span><span class="m">="</span><b>LicenseSaturation[EntityType=Orion.LicenseSaturation]/@MaxCount</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"></</span><span class="t">metaData</span><span class="m">></span>
<a class="b" href="#">-</a> <span class="m"><</span><span class="t">children</span><span class="m">></span>
<a class="b" href="#">-</a> <span class="m"><</span><span class="t">op</span> <span class="t">type</span><span class="m">="</span><b>ProviderScanOp</b><span class="m">"</span> <span class="t">name</span><span class="m">="</span><b>T1</b><span class="m">"</span> <span class="t">entity</span><span class="m">="</span><b>Orion.LicenseSaturation</b><span class="m">"</span><span class="m">></span>
<span class="b"> </span><span class="m"><</span><span class="t">field</span> <span class="t">name</span><span class="m">="</span><b>T1.C1</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>String</b><span class="m">"</span> <span class="t">ordinal</span><span class="m">="</span><b>0</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"><</span><span class="t">field</span> <span class="t">name</span><span class="m">="</span><b>T1.C2</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>Int32</b><span class="m">"</span> <span class="t">ordinal</span><span class="m">="</span><b>1</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"><</span><span class="t">field</span> <span class="t">name</span><span class="m">="</span><b>T1.C3</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>Int32</b><span class="m">"</span> <span class="t">ordinal</span><span class="m">="</span><b>2</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"><</span><span class="t">children</span><span class="m"> /></span>
<span class="b"> </span><span class="m"></</span><span class="t">op</span><span class="m">></span>
<span class="b"> </span><span class="m"></</span><span class="t">children</span><span class="m">></span>
<span class="b"> </span><span class="m"></</span><span class="t">queryPlan</span><span class="m">></span>
</code></p><p>As you see, there's no reference to a <span>ProviderPassThroughScanOp</span>.</p><p>By contrast, here's a query against Orion.Nodes:</p><p style="padding-left:30px;"><code>SELECT TOP 10 Caption, IP from Orion.Nodes WITH QUERYPLAN
And here is its query plan:
<span class="b"> </span><span class="m"><</span><span class="t">selector</span> <span class="t">name</span><span class="m">="</span><b>T1.C2</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>String</b><span class="m">"</span> <span class="t">expression</span><span class="m">="</span><b>PropertyRef/T1.C2</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"><</span><span class="t">field</span> <span class="t">name</span><span class="m">="</span><b>T1.C1</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>String</b><span class="m">"</span> <span class="t">ordinal</span><span class="m">="</span><b>0</b><span class="m">"</span> <span class="t">xmlPath</span><span class="m">="</span><b>nodes[EntityType=Orion.Nodes]/@caption</b><span class="m">"</span><span class="m"> /></span>
<span class="b"> </span><span class="m"><</span><span class="t">field</span> <span class="t">name</span><span class="m">="</span><b>T1.C2</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>String</b><span class="m">"</span> <span class="t">ordinal</span><span class="m">="</span><b>1</b><span class="m">"</span> <span class="t">xmlPath</span><span class="m">="</span><b>nodes[EntityType=Orion.Nodes]/@ip</b><span class="m">"</span><span class="m"> /></span>
<a class="b" href="#">-</a> <span class="m"><</span><span class="t">op</span> <span class="t">type</span><span class="m">="</span><b>ProviderPassThroughScanOp</b><span class="m">"</span><span class="m">></span>
<span class="b"> </span><span class="m"><</span><span class="t">passThrough</span><span class="m">></span><span class="tx" style="background-color:#ffff99;">SELECT TOP 10 [T1].[Caption] AS C1, [T1].[IP_Address] AS C2 FROM [dbo].[NodesData] AS T1 WITH QUERYPLAN RETURN XML Raw</span><span class="m"></</span><span class="t">passThrough</span><span class="m">></span>
<span class="b"> </span><span class="m"><</span><span class="t">field</span> <span class="t">name</span><span class="m">="</span><b>T1.C2</b><span class="m">"</span> <span class="t">type</span><span class="m">="</span><b>String</b><span class="m">"</span> <span class="t">ordinal</span><span class="m">="</span><b>1</b><span class="m">"</span><span class="m"> /></span>
