nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Two Issues - 1 with modifying report and 2 with attempted solution to modify query no worky

Ballzo

So I sent the default report Daily Node Availability - Last Month to my boss and he liked it but wanted me to limit it to just the devices that were not 100%. So I modify the report and just added a filter Availability is less than 100. Doing this for some reason changes the results the average availability % in the report. I saw this problem posted on another tread so I am trying to enter some of my own sql queries from that thread to see if they work and find another problem. I cannot edit or add any sql code in the report writer at all. Any ideas on either issue????

Ballzo

Sacramento, CA

Find more posts tagged with

Accepted answers

sedmo

Looks like you didn't get the complete WHERE clause entered. Here is what the complete SQL should look like.

FROM
Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)

WHERE (DateTime BETWEEN (select dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0))) AND (select dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))))
AND
(
(Nodes.Vendor <> 'Cisco') AND
(Nodes.caption <> 'TSSD000')
)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address

Having Avg(DailyNodeAvailability.Availability) <99.99

ORDER BY SummaryMonth ASC, 4 ASC

All comments

njoylif

make a copy of the report
open the report in notepad/wordpad/etc and edit the query directly

copy the SQL from the original query and recreate as custom SQL report

kweise

Ballzo,

I just ran into the same issue the other day when I was creating a new report based on the default Daily Node Availability report. I'm not much of a SQL writer, but I'd be willing to try the queries from the other thread too. Can you post a link to the other thead?

Thanks!

kweise

I think I figured it out. You'll have to create a custom SQL report instead of trying to use the report wizard. To do that, just open the default report, go to the Report header in Report Writer and select Show SQL. That will add a SQL tab to the report in the Report Designer view. Go to that tab, copy the SQL query and then create a new report. In the new report window, select Advanced SQL. Then paste the code you copied from the default report into the SQL tab in the new report design mode window.

When you add the filter Availability less than 100, you aren't filtering on the average availability. The WHERE clause becomes the following:

WHERE
( DateTime BETWEEN 40208 AND 40235.9999884259 )
AND
(
(ResponseTime.Availability < 100)
)

In order to filter on the average availability, you have to add as HAVING statement under the GROUP BY clause. You can't use the average of availability in the WHERE clause because the average calculation occurs after the WHERE conditions are evaluated. If you add the following after the GROUP BY clause and before the ORDER BY clause, you should get the results you're looking for:

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.NodeID, Nodes.Caption, Nodes.MachineType, Nodes.IP_Address

HAVING AVG(ResponseTime.Availability)<100

ORDER BY SummaryMonth ASC, 6 DESC

Hope this helps!

Ballzo

I think I understand why the values are not coming out correctly on my modified report and would like to try and change it but still when I do the show SQL option and go to the SQL tab, I am unable to make any changes to the query. The same thing when I try to create a new report and enter my own query, I cannot type or paste anything into the window.

Is there any chance that I can maybe change the report without modifying the sql query manually and worry about that problem later?

This is what it looks like now in case you are wondering.

SELECT TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
Nodes.NodeID AS NodeID,
Nodes.VendorIcon AS Vendor_Icon,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability

FROM
Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)

WHERE
( DateTime BETWEEN 40208 AND 40235.9999884259 )
AND
(
(Nodes.Vendor <> 'Cisco') AND
(Nodes.Caption <> 'TSSD000') AND
(DailyNodeAvailability.Availability < 100)
)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address

ORDER BY SummaryMonth ASC, 4 ASC

kweise

That's correct. On the SQL tab on the modified report, you won't be able to modify the SQL query. The only way you'll be able to change the SQL query directly is to copy the SQL code from your report and create a new report. When report writer asks you what type of report you want to create, select Advanced SQL. Paste the SQL query from your modified report into the SQL tab in the new, Advanced SQL report and you'll be able to edit it in that report.

Ballzo

OK, I have the sql editing figured out. Thank you but now still no luck with the query. It looks like this and is still returning different values than the default report that displays all devices. What do I need to change?