Server Uptime

[malbanese1]

I need to create a report that will show a percent (?) of uptime for a server over a given date range. Is that possible?

[Malbanese]

This is great. Really helpful. Thank you very much.

[MaginHdez]

Hi,
I created this report in order to reduce multiple results for each computer, try it and I hoppe can help you


Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
Convert(Decimal(7,2),tblAssets.Uptime / 60 / 60 / 24) As UptimeDays,
DateDiff(m, tblAssets.Firstseen, tblAssets.Lastseen) As MonPeriod,
tblAssetCustom.Model,
max(tbluptime.eventtime) as LastUp,
datediff(dd,max(tbluptime.eventtime),getdate()) as UpDays
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblComputersystem.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblUptime On tblComputersystem.AssetID = tblUptime.AssetId
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
group by
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
Convert(Decimal(7,2),tblAssets.Uptime / 60 / 60 / 24),
DateDiff(m, tblAssets.Firstseen, tblAssets.Lastseen),
tblAssetCustom.Model
order by UpDays


Regards.

[ham737]

How does it select server OS's. Not all my servers are showing after running report. Thanks!

MaginHdez wrote:
Hi,
I created this report in order to reduce multiple results for each computer, try it and I hoppe can help you


Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
Convert(Decimal(7,2),tblAssets.Uptime / 60 / 60 / 24) As UptimeDays,
DateDiff(m, tblAssets.Firstseen, tblAssets.Lastseen) As MonPeriod,
tblAssetCustom.Model,
max(tbluptime.eventtime) as LastUp,
datediff(dd,max(tbluptime.eventtime),getdate()) as UpDays
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblComputersystem.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblUptime On tblComputersystem.AssetID = tblUptime.AssetId
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
group by
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
Convert(Decimal(7,2),tblAssets.Uptime / 60 / 60 / 24),
DateDiff(m, tblAssets.Firstseen, tblAssets.Lastseen),
tblAssetCustom.Model
order by UpDays


Regards.

[Daniel_B · Accepted solution]

tblAssets.Uptime contains the uptime of the computer since it's last reboot. It doesn't provide information about how long the computer has been up altogether. Have a look also on the Database Dictionary which is accessible from the Report Builder.

Events for each Power on and Power off are saved in tblUptime. But since you only have Date and Time for each single event, the uptime can't be easily calculated. You need to loop through all entries in this table in order to sum up the uptime. We are working on a widget which can calculate computer uptime over a given number of days.

[Sven_Thiele]

Daniel.B wrote:
tblAssets.Uptime contains the uptime of the computer since it's last reboot. It doesn't provide information about how long the computer has been up altogether. Have a look also on the Database Dictionary which is accessible from the Report Builder.

Events for each Power on and Power off are saved in tblUptime. But since you only have Date and Time for each single event, the uptime can't be easily calculated. You need to loop through all entries in this table in order to sum up the uptime. We are working on a widget which can calculate computer uptime over a given number of days.

Hey Daniel,

when will the widget be available? Is it still under development?
Our question is that... we want to know how many days from the last month (i.e. January 2019) the computers have been on in days or hours.

Greetings
Sven

[Malbanese]

I have this so far but I am getting multiple results for each computer

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
Convert(Decimal(7,2),tblAssets.Uptime / 60 / 60 / 24) As UptimeDays,
DateDiff(m, tblAssets.Firstseen, tblAssets.Lastseen) As Period,
tblAssetCustom.Model
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblUptime On tblAssets.AssetID = tblUptime.AssetId
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1