We have a custom SQL report listing all servers with uptime > 60 days. We use this report to locate Windows hosts that have not been rebooted after their monthly patching run. The report can take 1-2 minutes to display in a browser, and this is causing issues with scheduling. The scheduled job sends an HTML email, which only displays the SAM login screen. The exact same scheduled task running a fast report returns the HTML data as expected. Does the below SQL query look optimal, or does it need to be restructured? If the structure is sound, are there ways to extend timeout settings on the scheduled task so that the data comes through? Thanks, C
convert(varchar,convert(int,APM_ResponseTime.StatisticData)/86400) + ' days ' + convert(varchar,(convert(int,APM_ResponseTime.StatisticData) % 86400 / 3600)) + ' hours ' + convert(varchar(2), ((convert(int,APM_ResponseTime.StatisticData) % 86400) % 3600) / 60) + ' minutes' AS Uptime
Nodes.Application_Name AS Application_Name,
Nodes.NodeID AS NodeID,
Nodes.Caption AS NodeName,
RT.ComponentID as ComponentID,
max(RT.[DateTime]) AS LastCheck
inner join APM_AlertsAndReportsData ARD ON (Nodes.NodeID = ARD.NodeId)
inner join APM_ResponseTime RT ON (ARD.ComponentId = RT.ComponentID)
where ARD.ComponentName = 'System: Uptime'
group by Nodes.Application_Name,Nodes.NodeID,Nodes.Caption, RT.ComponentID) x
inner join APM_ResponseTime on APM_ResponseTime.[DateTime] = x.LastCheck and APM_ResponseTime.ComponentId = x.ComponentID
WHERE APM_ResponseTime.StatisticData > 5184000
order by 1 asc, 3 asc
We have a similar behaviour since an upgrade from APM to SAM (I opened ticket# 318128 on 1st of March, almost two months ago for this).
The user used for the report has the option to never time out set but the session times out nonetheless!
If I log into windows with that user, open the report page within report scheduler and change the url part which states that the session timed out from yes to no, it fixes the issue for an several days/weeks.
I have had this happen about 4times since I opened the case but haven't had it happen in the past 2-3weeks (havent done another Orion update since opening the case).
The SQL code provided by Petr above does run much faster - thank you. Now for whatever reason the job won't send the email. I can get the email to send if I leave off the username/password in the Orion Web Login dialog, and don't embed it in the URL. When I do that, I get an HTML email containing the login screen. If I specify the credentials (verified that they work fine in a browser from the Orion server), the job never emails me.
What type of authentication are you using? Forms based (default) or Windows Integrated for pass-through authentication? (Pop-up window asking for credentials). Also, is the account you're trying to use for the Orion Report Scheduler a local Orion user account to a domain/Active Directory user?
Forms based auth, trying to authenticate as an Orion user (no AD auth). Job is running as a local NT user on the Orion app server (local admin). When no auth is passed, I get an email with the login screen. When auth is passed (either embedded in the report URL or via the Orion authentication tab) the emails never arrive.
This is an odd one I can't say I have a good answer for, only more questions. Instead of peppering you with more questions I'm going to suggest you open a case with support so we can get to the root cause of this much quicker.
I would recommend to restructure query to get better performance (unnecessary grouping and joining on datetime column are not optimal):
a.Name AS Application_Name, -- or maybe n.Application_Name which seems to be custom column in nodes table
n.Caption as NodeName,
DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), ccs.[TimeStamp]) as LastCheck,
convert(varchar,convert(int,ped.StatisticData)/86400) + ' days ' + convert(varchar,(convert(int,ped.StatisticData) % 86400 / 3600)) + ' hours ' + convert(varchar(2), ((convert(int,ped.StatisticData) % 86400) % 3600) / 60) + ' minutes' AS Uptime
FROM APM_CurrentComponentStatus ccs WITH (NOLOCK)
INNER JOIN APM_Application a ON a.ID = ccs.ApplicationID
INNER JOIN Nodes n WITH (NOLOCK) ON n.NodeID = a.NodeID
INNER JOIN APM_Component c ON c.ID = ccs.ComponentID
INNER JOIN APM_PortEvidence_Detail ped WITH (NOLOCK) ON ccs.ComponentStatusID = ped.ComponentStatusID
WHERE c.Name = 'System: Uptime' AND ped.StatisticData > 5184000
I also included some NOLOCKs to prevent possible concurrency issues with other frequent database activities.
Does this report run sometimes but not always through the Orion Report Scheduler? Have you validated that you are passing the proper credentials? The fact that you're only seeing the login page makes me think there are no credentials defined. In addition to the login page are there any errors displayed? You may want to look at this report and see if it runs quicker than your own custom report.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.