cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 11

Custom SQL...Graph?

Jump to solution

Greetings Gentlemen,

This might take a minute to explain, so please bear with me.

So I've got Avaya phones. This obviously leads to some compatibility issues with SolarWinds, so I have to do some magical things to make polling worth it for this system. One of the things I've gone and done now is create UnDP's that return a table that contains the current status of each active channel on each individual trunk from our Avaya systems, with a separate UnDP per trunk.

What I wanted from this information is basically a poll to find the quantity of ACTIVE channels, per trunk, per poll period. So here's the query I built to achieve these results:

# NOTE: Line 6, '%AvayaTrunk#%', # is substituted on a trunk-by-trunk basis to specify desired results.

SELECT DATEADD(MINUTE, DATEDIFF(minute, 0, cpsd.DateTime),0) AS Time,

count(CASE WHEN cpsd.Status LIKE '%active%' THEN 1 ElSE 0 END) AS Quantity

FROM [dbo].[CustomPollerStatistics_Detail] AS cpsd

JOIN CustomPollerAssignment AS cpa ON cpsd.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID

JOIN CustomPollers AS cp ON cpa.CustomPollerID = cp.CustomPollerID

WHERE cp.UniqueName LIKE '%AvayaTrunk#%'

AND cpsd.Status LIKE '%active%'

GROUP BY cpsd.DateTime

ORDER BY cpsd.DateTime DESC

;

I then threw this in the web based Report Writer and got some fancy graphs that give me exactly what I want.


BUT...


How can I display this data as a graph on the node's page? Or for that matter, even a summary page?

1. The web-based Report Writer is not an option at all for custom resources, thus I cannot apply this report as a resource on my Avaya node or on a summary page...I can only retrieve this data via the actual web report.

2. The application Report Writer can be used as a custom resource, but as far as I know, I cannot graph the results, and can only retrieve them as a table.

3. Creating a custom SWQL resource also nets me a simple table, and not a graph.

Thanks in advance for any input you can provide!

1 Solution
Level 17

You can add a 'Custom Chart' resource to any dashboard view (Summary, Node Details, etc).

Edit the resource, click on 'Select Datasource' and change the selection method to 'Advanced Database Query (SQL, SWQL)'

and you should be able to use your SQL query

View solution in original post

9 Replies
Level 17

You can add a 'Custom Chart' resource to any dashboard view (Summary, Node Details, etc).

Edit the resource, click on 'Select Datasource' and change the selection method to 'Advanced Database Query (SQL, SWQL)'

and you should be able to use your SQL query

View solution in original post

Hah! I never spotted that. I had always dealt with the custom chart without modifying the selected data source. Thanks, sir!

0 Kudos

Next problem:

As with the report, it requires a Time Column, and my query provides one, but it isn't seeing it as an option here. Any ideas?

Graph Problem 1.JPG

0 Kudos

Can you check the UnDP poller to see if Historical is enabled.

0 Kudos

It is. Otherwise, my report would not have worked originally...and I checked again, just in case. 😃

0 Kudos

What I mean here is that I could populate the Time column just fine in the report, but it isn't seeing it in the Custom Chart Resource.

As with the report, it requires a Time Column, and my query provides one, but it isn't seeing it as an option here. Any ideas?

0 Kudos

Yea, the Time column should provide a browse button to select the Time column although I haven't tried the same steps with a SWQL for custom pollers. I am running v11.5.2

Core-Report-SWQL-Chart.png

0 Kudos

Yeah, that browse button isn't appearing on my screen, no matter how I modify the query to populate the column. =( It does appear (with the same query) when I put it in the web-based report, but will not populate when I throw the query in to the Custom Chart. When I was searching for an answer, I saw something about ${FromTimeUTC} and ${ToTimeUTC}, and even with the suggested modifications related to these values, I still don't get a populated option for the time column to browse for. It's extremely possible that I didn't do this right...

If anyone's got 11.0.1 and knows a good way to properly populate this, that'd be neat!

Hello,

You can see timecolumn picker only if you use minimum one timemacro that means: ${fromtime} ${totime} ${fromtimeUTC} or ${totimeUTC} can be commented of course.

Example:

select * from cpuload where datetime > ${FromTime}

Unfortunately it is not working in NetObject detail page (node detail, interface detail... etc.). Workaround is not easy but it needs some resource table modification.

0 Kudos