Server uptime for all equipment

[dimi3]

Hello,

I'm trying to create a report where the total uptime for every piece of network equipment is listed. This includes Windows servers, virtual servers, Linux servers, WiFi devices, NAS, firewalls, switches, etc. In other words, everything except the hosts that rely on these devices to function (laptops, desktops, etc.).

I've written the query below, but I cannot get the remaining equipment to fit in the list. Only 31 virtual servers and 1 physical server are shown in the list now.

Select Top (1000000) tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
  24))) + ' days ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
  24))) + ' hours ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
  60))) + ' minutes' As Uptime
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
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.Uptime

I was thinking of using IP addressing to select the equipment needed for the list, since it is located in a different network segment/VLAN than the rest of the devices. However, up until now, I have not been able to get the written code to make this happen.

Could anyone shed some light on how this could be done?

[rader]

Your report was hanging up on two things that I discovered. One was you started with the premise of tblComputerSystem, which would limit assets not in that table once you started to filter out the results. 

Starting with the tblAssets you could grab all the assets and filter from there.

When I started with that table, the second thing hanging up the results was the Domain Roles. That effectively made the results tblComputerSystem again, thus limiting your results.

It took a while, but I think this might work for your report. There are two filters in play here, tblAssets.IPAdresses and tsysOS.OSname.

IP addresses to limit anything without an IP, like monitors, locations, old assets where the IP is gone, other things that don't have IP.

OSname replaces the DomainRole filter. Two things about this one. 1 - Is Null grabs the network gear and other items that don't have an OS assigned to the asset, and 2 - Not Like 'Win 11' And Not Like 'Win 10' And Not Like 'Win 7' removes the workstations but leaves the servers, regardless of the type or OS. You may have to tweak this line to include/remove any workstations in your organization.

It's been a learning experience for me and I hope this report works for you. Enjoy.

Select Top (1000000) tsysAssetTypes.AssetTypeIcon16 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
  24))) + ' days ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
  24))) + ' hours ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
  60))) + ' minutes' As Uptime,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model As Model,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysOS.OSname
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.IPAddress Not Like '' And (tsysOS.OSname Is Null Or
    (tsysOS.OSname Not Like 'Win 11' And tsysOS.OSname Not Like 'Win 10' And
      tsysOS.OSname Not Like 'Win 7'))
Order By tblAssets.Uptime Desc