→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎05-21-2024 07:13 PM - edited ‎05-21-2024 07:14 PM
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?
‎06-05-2024 10:03 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now