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.

Cannot use derived table for custom metric query

ziv over 5 years ago

As the subject states, I've tried to use derived tables in a query for CPU usage metrics generation but received the error that "LEM" the name I gave one of the derived tables in the query does not exist.

I assume this is because SolarWinds DPA simply does not support derived tables for Sybase ASE (maybe for MSSQL where you can use a "with" clause and then the derived table's code comes before the columns).

Code: (I don't know how to insert this as code)

set arithabort off

select

convert(numeric(5,2),avg((LEM.UserCPUTime - PEM.UserCPUTime)*100.0/((LEM.CPUTime - PEM.CPUTime)*1.0))) as 'UserCPU'

from

(

select

EngineNumber,

CPUTime,

SystemCPUTime,

UserCPUTime,

IdleCPUTime,

CPUTime - SystemCPUTime - UserCPUTime - IdleCPUTime as 'IOCPUTime'

from

mda_repository..mdaEngine

where

MeasureID = (select max(MeasureID) from mda_repository..mdaEngine)

and Status='online'

) as LEM

join (

select

EngineNumber,

CPUTime,

SystemCPUTime,

UserCPUTime,

IdleCPUTime,

CPUTime - SystemCPUTime - UserCPUTime - IdleCPUTime as 'IOCPUTime'

from

mda_repository..mdaEngine

where

MeasureID = (select max(MeasureID) from mda_repository..mdaEngine where MeasureID < (select max(MeasureID) from mda_repository..mdaEngine))

and Status='online'

) PEM

on LEM.EngineNumber = PEM.EngineNumber

set arithabort on

-- END OF CODE

mda_repository..mdaEngine simply contains samples taken and stored from master..monEngine .

The code was originally written to fetch all CPU times, but for this metric the rest were removed.

Of course, the code was ran on an ASE ISQL client with no errors and returned the expected result.

I intended to introduce this code, for the the IO CPU usage, which is absent (at least from my version), and specifically for one site, introduce the "User" CPU usage metric like this, as the built in one stopped working for that site, for some reason.

If you're wondering, PEM = Previous Engine Measurement, LEM = Last Engine Measurement.

Has anyone run in to this problem? Is this a known bug? Something SolarWinds intends to amend in a future version?

My version is 11.0.378

Thanks in advance,

Shay.

0 darichar over 5 years ago

I used a SQuirrel Client to connect to a Sybase instance and ran a different query (below) with derived tables since I don't have the mda_repository database that you have. This query worked so I then plugged it into a custom metric and it also worked. DPA sends the query as it is to the Sybase instance to execute, so it should support what the target instance supports. Not sure why your query is not working. Did you connect as the DPA monitoring user when you ran the query via iSQL? Is it possible the DPA monitoring user does not have permissions to query the mda_repository database?
select a.col2
from (select 'A' as col1, 1 as col2) as a
join (select 'A' as col1, 2 as col2) as b
on a.col1 = b.col1
Cancel
Vote Up 0 Vote Down

Cancel
0 ziv over 5 years ago in reply to darichar

Hi darichar , thanks for your input.
The user I configured for DPA has full access to the mentioned database.
I have noted a difference between my query and yours, I actually employ a sub-query inside the derived table expression.
(The "MeasureID = ..." in the where clause)
Can you perform a test with that?
Thanks,
Shay.
Cancel
Vote Up 0 Vote Down

Cancel
0 darichar over 5 years ago in reply to ziv

Yes, definitely. I will try that and get back to you.
Cancel
Vote Up 0 Vote Down

Cancel
0 darichar over 5 years ago in reply to darichar

I created 2 simple tables named test1 and test2, inserted some data and was able to get results from the following query within DPA custom metrics using derived tables:
select a.col2
from (select col1, col2 from swload.dbo.test1 where col2 = (select max(col2) from swload.dbo.test1 where col2 =3)) as a
join (select col1, col2 from swload.dbo.test2) as b
on a.col1 = b.col1
I have reviewed your query and am not seeing anything wrong with it. Can you copy your query into the DPA's Database Query Tool in the Options > Support tab? Connect to a Sybase instance, execute it and let me know the results. My guess is that you will get the same error but wanted to make sure.
When you run this query outside of DPA what are the results? Does it return more than 1 row? If so, that could cause custom metrics to fail since it's expecting 1 row.
Cancel
Vote Up 0 Vote Down

Cancel
0 ziv over 5 years ago in reply to darichar

Hi darichar ,
I just found the problem!
In the code here, I did not include 3 lines of code that I have been trying to use. The reason I did not include them, was that they were commented out in the code.
I have made a code to pull all 4 CPU Time values, and created two metrics for them. One for User CPU Time, and one for IO CPU Time.
On each of the two metrics, I was pasting the whole code, while simply commenting out the 3 lines not relevant to that metric.
It turns out those 3 lines were the problem!
When I removed the commented lines entirely, it worked just fine!
Thanks for your inputs!
---
Shay.
Cancel
Vote Up 0 Vote Down

Cancel
0 darichar over 5 years ago in reply to ziv

That's awesome, glad you were able to find the problem.
Cancel
Vote Up 0 Vote Down

Cancel