server uptime SQL report times out as a scheduled task

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

select

x.Application_Name,

x.NodeID,

x.NodeName,

x.LastCheck,

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

from

(select

Nodes.Application_Name AS Application_Name,

Nodes.NodeID AS NodeID,

Nodes.Caption AS NodeName,

RT.ComponentID as ComponentID,

max(RT.[DateTime]) AS LastCheck

from Nodes

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

Find more posts tagged with

sql

timeout

report

Accepted answers

All comments

aLTeReGo

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.

http://thwack.solarwinds.com/docs/DOC-27324

Petr.Vilem

I would recommend to restructure query to get better performance (unnecessary grouping and joining on datetime column are not optimal):

SELECT

a.Name AS Application_Name, -- or maybe n.Application_Name which seems to be custom column in nodes table

a.NodeID,

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.

Questionario

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

cdenuyl

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.

aLTeReGo

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?

cdenuyl

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.