All Server Uptime

[Glennmdh]

file added

[Daniel_B]

For a list of software per computer you can use the built-in report "Software: List of software by computer" (you can see it here as well).
It is expected behavior that when adding the list of installed software to a report the same computer will be listed multiple times. This is how SQL Server works. If you would like to have a better overview, you either need to create custom reports for the software you are interested in or possibly export the report to XLS and create a Pivot table in Excel.

Note: For questions which are not directly related to your original forum topic, it would be better to create a new one, so we keep a better overview which helps other forum visitors as well.

[Glennmdh]

Daniel, by the way do you know if you can list all the software installed on a server by server listed in a report?

this is my report just need to add the software piece. I added the software table but reports the same servers to many times.


Select Top (1000000) tblAssets.AssetName As Name,
tblAssets.AssetID,
tsysOS.Image As icon,
RTrim(LTrim(Coalesce(tblOperatingsystem.Caption, '') + ' ' +
Coalesce(tblOperatingsystem.OtherTypeDescription, ''))) As [OS Version],
tblAssetCustom.Model As [Model/Platform],
tblAssets.Processor As [Model CPU],
tblAssets.NrProcessors,
Sum(tblProcessor.NumberOfCores) As [CPU Cores],
dbo.tsysIPLocations.IPLocation
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Group By tblAssets.AssetName,
tblAssets.AssetID,
tsysOS.Image,
RTrim(LTrim(Coalesce(tblOperatingsystem.Caption, '') + ' ' +
Coalesce(tblOperatingsystem.OtherTypeDescription, ''))),
tblAssetCustom.Model,
tblAssets.Processor,
tblAssets.NrProcessors,
tblAssets.AssetUnique,
tsysIPLocations.IPLocation
Having RTrim(LTrim(Coalesce(tblOperatingsystem.Caption, '') + ' ' +
Coalesce(tblOperatingsystem.OtherTypeDescription, ''))) Like '%Server%'
Order By Name

[Glennmdh]

Thanks Daniel, the report is working great.

[Daniel_B · Accepted solution]

The following is a combination of the two reports. Detailed power-on and power-off events are only scanned from Windows computers. This report only considers power-on and standby-stop events for the "LastUp" column.

Select Top 1000000 tblAssetCustom.AssetID,
  tblAssets.AssetUnique,
  tblAssetCustom.Manufacturer As Vendor,
  tblAssetCustom.Model,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblAssets.Memory,
  Case When tblAssetCustom.Model Like '%virtual%' Then 'virtual' Else 'physical'
  End As VirtualCheck,
  VMWare.Hostname As [VMware Host],
  HyperV.Hostname As [HyperV Host],
  Convert(Decimal(7,2),tblAssets.Uptime / 60 / 60 / 24) As UptimeDays,
  DateDiff(m, tblAssets.Firstseen, tblAssets.Lastseen) As MonPeriod,
  tuptime.LastUp,
  tuptime.UpDays
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join (Select tblUptime.AssetId,
    Max(tblUptime.EventTime) As LastUp,
    DateDiff(dd, Max(tblUptime.EventTime), GetDate()) As UpDays
  From tblUptime
  Where tblUptime.EventType In (1, 4)
  Group By tblUptime.AssetId) tuptime On tblAssets.AssetID = tuptime.AssetId
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join (Select Top 1000000 tblAssetMacAddress.AssetID,
    tblAssets1.AssetName As Hostname
  From tblAssetMacAddress
    Left Join tblVmwareGuestNetwork On tblVmwareGuestNetwork.MacAddress =
      tblAssetMacAddress.Mac
    Left Join tblVmwareGuest On tblVmwareGuest.GuestID =
      tblVmwareGuestNetwork.GuestID
    Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
      tblVmwareGuest.AssetID
  Where tblAssets1.AssetName Is Not Null) VMWare On VMWare.AssetID =
    tblAssets.AssetID
  Left Join (Select Top 1000000 tblAssetMacAddress.AssetID,
    tblAssets1.AssetName As Hostname
  From tblAssetMacAddress
    Left Join TblHyperVGuestNetwork On TblHyperVGuestNetwork.MacAddress =
      tblAssetMacAddress.Mac
    Left Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
      TblHyperVGuestNetwork.HyperVGuestID
    Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
      tblHyperVGuest.AssetID
  Where tblAssets1.AssetName Is Not Null) HyperV On HyperV.AssetID =
    tblAssets.AssetID
  Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1

[Glennmdh]

I wanting all Servers, VM, HyperV to show on one report.


Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.Memory,
Case When tblAssetCustom.Model Like '%virtual%' Then 'virtual' Else 'physical'
End As VirtualCheck,
VMWare.Hostname As [VMware Host],
HyperV.Hostname As [HyperV Host]
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Top 1000000 tblAssetMacAddress.AssetID,
tblAssets1.AssetName As Hostname
From tblAssetMacAddress
Left Join tblVmwareGuestNetwork On tblVmwareGuestNetwork.MacAddress =
tblAssetMacAddress.Mac
Left Join tblVmwareGuest On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblVmwareGuest.AssetID
Where tblAssets1.AssetName Is Not Null) VMWare On VMWare.AssetID =
tblAssets.AssetID
Left Join (Select Top 1000000 tblAssetMacAddress.AssetID,
tblAssets1.AssetName As Hostname
From tblAssetMacAddress
Left Join TblHyperVGuestNetwork On TblHyperVGuestNetwork.MacAddress =
tblAssetMacAddress.Mac
Left Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
TblHyperVGuestNetwork.HyperVGuestID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblHyperVGuest.AssetID
Where tblAssets1.AssetName Is Not Null) HyperV On HyperV.AssetID =
tblAssets.AssetID
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1

And

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

[Daniel_B]

Could you post the report you would like to use or on which you have a question in your post? The PDF is not easily readable.

The uptime of assets is stored in tblAssets.Uptime.