These PCs have high uptime but cannot be placed on the map because they either have no machine relationship,
or their related machine has no map coordinates. * = has relationship but machine needs location.
| Hostname |
Uptime (Days) |
Last Boot |
Business Unit |
Related Machine |
Serial Number |
Action |
<%
' Query PCs with high uptime that CANNOT be mapped:
' 1. No machine relationship at all, OR
' 2. Has relationship but equipment has no map coordinates
Dim rsUnmapped, strUnmappedSQL
Dim unmappedDisplayCount
unmappedDisplayCount = 0
strUnmappedSQL = "SELECT " &_
"pc.machineid, " &_
"pc.hostname, " &_
"pc.serialnumber, " &_
"pc.lastboottime, " &_
"DATEDIFF(NOW(), pc.lastboottime) as uptime_days, " &_
"bu.businessunit, " &_
"eq.machinenumber as related_machine " &_
"FROM machines pc " &_
"LEFT JOIN machinerelationships mr ON pc.machineid = mr.related_machineid AND mr.isactive = 1 " &_
"LEFT JOIN machines eq ON mr.machineid = eq.machineid " &_
"LEFT JOIN businessunits bu ON pc.businessunitid = bu.businessunitid " &_
"WHERE pc.pctypeid IS NOT NULL " &_
"AND pc.isactive = 1 " &_
"AND pc.lastboottime IS NOT NULL " &_
"AND DATEDIFF(NOW(), pc.lastboottime) >= " & minUptime & " " &_
"AND (mr.relationshipid IS NULL OR eq.mapleft IS NULL OR eq.maptop IS NULL) " &_
"ORDER BY uptime_days DESC"
Set rsUnmapped = objConn.Execute(strUnmappedSQL)
Do While Not rsUnmapped.EOF
unmappedDisplayCount = unmappedDisplayCount + 1
Dim umHostname, umUptime, umLastBoot, umBU, umSerial, umId, umColor
umId = rsUnmapped("machineid")
umHostname = rsUnmapped("hostname") & ""
If IsNull(rsUnmapped("uptime_days")) Then
umUptime = 0
Else
umUptime = CLng(rsUnmapped("uptime_days"))
End If
umLastBoot = rsUnmapped("lastboottime")
umSerial = rsUnmapped("serialnumber") & ""
If IsNull(rsUnmapped("businessunit")) Then
umBU = "-"
Else
umBU = rsUnmapped("businessunit") & ""
End If
Dim umRelatedMachine
If IsNull(rsUnmapped("related_machine")) Then
umRelatedMachine = ""
Else
umRelatedMachine = rsUnmapped("related_machine") & ""
End If
' Color based on uptime
If umUptime >= 90 Then
umColor = "#F44336"
ElseIf umUptime >= 60 Then
umColor = "#FF9800"
ElseIf umUptime >= 30 Then
umColor = "#FFC107"
Else
umColor = "#4CAF50"
End If
%>
| <%=Server.HTMLEncode(umHostname)%> |
<%=umUptime%> |
<%=umLastBoot%> |
<%=Server.HTMLEncode(umBU)%> |
<%If umRelatedMachine <> "" Then%><%=Server.HTMLEncode(umRelatedMachine)%> *<%Else%>None<%End If%> |
<%=Server.HTMLEncode(umSerial)%> |
View |
<%
rsUnmapped.MoveNext
Loop
rsUnmapped.Close
Set rsUnmapped = Nothing
%>
Total: <%=unmappedDisplayCount%> PCs without machine relationships