Windows Uptime Reporting

[Mike EvansCengageLearni]

I am aware that the System Uptime report in SIM does not work with Windows servers. I am also aware of the uptime.exe that Microsoft provides to retrieve uptime information from Windows servers. Does anyone currently utilize this utility to, or know of a way to, create reports that do not need to be manually massaged so that they can be emailed to upper management like the System Uptime report? Or does anyone know of a way to somehow have this data injected into the SQL database to be extracted by a custom report? We have this functionality with NNM and OpenView, but it is somewhat cumbersome and still needs to be manipulated after it has been extracted. I think it would be nice if the HP agents could provide this information to SIM or SIM could pull this information into the database by utilizing the uptime.exe application

[NJK-Work]

Hi Mike,

This is getting outside of HP SIM, but I would guess a simple VB script would yield uptime results for you. An example is here:

http://www.naterice.com/blog/template_permalink.asp?id=32

You could combine this with scripting that pulls a lists of servers from an AD OU into a dictionary object and then interate through each server using portions of the script from above. Here is an example (you fill in the AD stuff):

Set objDictionary = CreateObject("Scripting.Dictionary")
i = 0
Set objOU = GetObject("LDAP://OU=ServersOU, DC=YourADDomin, DC=COM")
objOU.Filter = Array("Computer")
For Each objDC in objOU
objDictionary.Add i, objDC.CN
i = i + 1
Next


In fact, you can probably get all of this done without using SIM at all.

Nelson

[Mike EvansCengageLearni]

Thanks for the reply! Unfortunately, that just looks like a more complex way of doing what I have already done, adding uptime.exe as a custom tool.

[SEBEMISMNUSA]

The uptime.exe command is easier for a small number of computer servers. I have not yet created any scripts (VBS) or spreadsheets to calculate uptime. This is straight forward to do, as the date/time notice is # of seconds from 1970. Simple subtraction can calculate this.

On a computer running HP SIM, with Microsoft SQL 2000 server for the HP SIM database configure HP SIM, enable SQL views and query template to determine computer server system availability.
This query produces this report
Computer Event description # of milliseconds
computer-name hp.nodestatus.unreachable 1177915832104
The events hp.nodestatus.unreachable and reachable are saved.

The number of milliseconds since 1970 UTC is the date/time notice. It makes calculations of uptime easy (simply subtraction to determine the time.
References: HP Systems Insight Manager Technical Reference Guide, HP part # 356920-007, November 2005, Edition 4.0 (for HP SIM 5.0), Reference Information, Database Tables, starting at page 645

1. Created B_Uptime SQL views - SQL Enterprise Manager
Copied HP supplied R_Inventory view to newly created B_Uptime view.
Added these tables to this B_Uptime view, notices and noticeType;
Added rows from the notice table
notice.noticeid, notice.NoticeServerity, notice.DeviceKey, notice.Generated
Added these rows from the noticeType table
notice.noticeType, notice.typeIdStr

2. Query template to generate data for computer system uptime

Notes:
* consolidatedNodeAuths.userID = aaaaaaaaaaa is a particular useid. this field may change for different people and their different accounts;
* ( IPAddress.IPAddressNumber >= xxxxxx AND IPAddress.IPAddressNumber <= yyyyyy ) )
this is for site 150 10.150.x.x; it is the "long form" of the TCP/IP address; it will need to change for different sites or TCP/IP address ranges
* This devices.productType = 28 and other type = specifies computer servers, not desktop or notebook computers, nor printers nor routers.

select B_Uptime.DeviceName, B_Uptime.typeIdStr, B_Uptime.Generated from B_Uptime
WHERE ((B_Uptime.SnapShotID=-1) OR (B_Uptime.SnapShotID is NULL))
AND ((B_Uptime.typeIdStr='Trap.coldstart') OR (B_Uptime.typeIdStr='Trap.linkup') OR
(B_Uptime.typeIdStr='Insight.DiscoveredDevice') OR (B_Uptime.typeIdStr='hp.nodestatus.unreachable')
OR (B_Uptime.typeIdStr='Trap.reachable'))
AND DeviceKey in (select devices.deviceKey from devices
JOIN notices ON devices.deviceKey = notices.deviceKey and notices.NoticeType = 1
LEFT OUTER JOIN DB_deviceInfoEx ON devices.deviceKey = DB_deviceInfoEx.deviceKey
LEFT OUTER JOIN IPXAddress ON devices.deviceKey = IPXAddress.deviceKey and IPXAddress.ipxIndex = 0
LEFT OUTER JOIN associated_device_data ON devices.deviceKey = associated_device_data.deviceKey
LEFT OUTER JOIN deviceProtocolInfo ON devices.deviceKey = deviceProtocolInfo.deviceKey
LEFT OUTER JOIN LockdownStatus ON devices.deviceKey = LockdownStatus.deviceKey
LEFT OUTER JOIN DeviceStatusValue ON devices.deviceKey = DeviceStatusValue.deviceKey
and DeviceStatusValue.StatusSourceId= 6 LEFT OUTER JOIN IPAddress ON devices.deviceKey = IPAddress.deviceKey
JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId
and consolidatedNodeAuths.userID = aaaaaaaaaaa
WHERE ( ( IPAddress.IPAddressNumber >= xxxxxx AND IPAddress.IPAddressNumber <= yyyyy ) )
AND ( ( devices.productType = 28 OR devices.productType = 6 OR devices.productType = 1
OR devices.productType = 24 OR devices.productType = 26) ) ) order by B_Uptime.DeviceName ASC


SQL to create B_Uptime view (it shows which tables and rows are used)

SELECT dbo.devices.DeviceKey, dbo.devices.Name AS DeviceName, dbo.devices.ProductName, dbo.DB_DeviceInfoEx.ProductId,
dbo.DB_DeviceInfoEx.TotalMemory AS MemorySize, dbo.DB_DeviceInfoEx.ROMVersion, dbo.DB_DeviceInfoEx.SerialNumber,
dbo.DB_DeviceInfoEx.AssetTag, dbo.DB_DeviceInfoEx.OSName, dbo.IPAddress.IPAddress, dbo.IPAddress.IPAddressNumber AS IPLongValue,
dbo.DB_DeviceInfoEx.OSVendor, computerSystem.SnapshotID, computerSystem.PrimaryOwnerContact AS DeviceOwner,
computerSystem.dc_SystemLocation AS Location, dbo.devices.ProductType, dbo.devices.OverallStatus AS DeviceStatus,
dbo.CIM_OperatingSystem.LastBootUpTime AS DeviceBootTime, dbo.devices.ProductSubType, dbo.devices.ProductTypeStr,
dbo.DC_ProliantHost.ServerRole, dbo.IPXAddress.IPXAddress, dbo.DC_ProliantHost.SystemUptime, dbo.notices.NoticeId,
dbo.notices.DeviceKey AS Expr1, dbo.IPAddress.DeviceKey AS Expr2, dbo.DB_DeviceInfoEx.DeviceKey AS Expr3, dbo.noticeType.noticeType,
dbo.notices.NoticeSeverity, dbo.notices.Generated, dbo.noticeType.typeIdStr
FROM dbo.devices INNER JOIN
dbo.notices ON dbo.devices.DeviceKey = dbo.notices.DeviceKey INNER JOIN
dbo.noticeType ON dbo.notices.NoticeType = dbo.noticeType.noticeType LEFT OUTER JOIN
dbo.IPXAddress ON dbo.devices.DeviceKey = dbo.IPXAddress.DeviceKey AND dbo.IPXAddress.ipxIndex = 0 LEFT OUTER JOIN
dbo.DB_DeviceInfoEx ON dbo.devices.DeviceKey = dbo.DB_DeviceInfoEx.DeviceKey LEFT OUTER JOIN
dbo.DC_ProliantHost RIGHT OUTER JOIN
dbo.CIM_OperatingSystem WITH (nolock) RIGHT OUTER JOIN
(SELECT CIM_ComputerSystem.SnapshotID, CIM_ComputerSystem.PrimaryOwnerContact, CIM_ComputerSystem.dc_SystemLocation,
CIM_ComputerSystem.NodeID
FROM CIM_ComputerSystem WITH (nolock)
WHERE ((CIM_ComputerSystem.R_PortCount IS NULL) AND CIM_ComputerSystem.ComputerSystem_LUID NOT IN
(SELECT partComponent
FROM CIM_ComponentCS WITH (nolock)))) computerSystem ON dbo.CIM_OperatingSystem.NodeID = computerSystem.NodeID AND
dbo.CIM_OperatingSystem.SnapshotID = computerSystem.SnapshotID ON dbo.DC_ProliantHost.SnapshotID = computerSystem.SnapshotID AND
dbo.DC_ProliantHost.NodeID = computerSystem.NodeID ON dbo.devices.DeviceKey = computerSystem.NodeID LEFT OUTER JOIN
dbo.IPAddress ON dbo.devices.DeviceKey = dbo.IPAddress.DeviceKey AND dbo.IPAddress.ipindex = 0


End