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.

Custom SQL...Graph?

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!

Parents Reply Children
  • 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?

  • 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

  • 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.