<%
Dim currentPCStatus, winrmFilter, deviceTypeFilter, pcTypeFilter, uptimeFilter, sel
currentPCStatus = Request.QueryString("pcstatus")
winrmFilter = Request.QueryString("winrm")
deviceTypeFilter = Request.QueryString("devicetype")
pcTypeFilter = Request.QueryString("pctype")
uptimeFilter = Request.QueryString("uptime")
' Check for specialized PCs (CMM, Wax Trace, Measuring Tool) without equipment relationships
Dim rsUnlinked, unlinkedCount
unlinkedCount = 0
Set rsUnlinked = objConn.Execute("SELECT COUNT(*) as cnt FROM machines m " & _
"WHERE m.pctypeid = 7 AND m.isactive = 1 " & _
"AND NOT EXISTS (SELECT 1 FROM machinerelationships mr WHERE (mr.machineid = m.machineid OR mr.related_machineid = m.machineid) AND mr.relationshiptypeid = 3 AND mr.isactive = 1)")
If Not rsUnlinked.EOF Then
unlinkedCount = CLng(rsUnlinked("cnt") & "")
End If
rsUnlinked.Close
Set rsUnlinked = Nothing
If unlinkedCount > 0 Then
%>
<%=unlinkedCount%> specialized PC(s) (CMM, Wax Trace, or Measuring Tool) need equipment relationships.
View them
<%
End If
%>
<% If currentPCStatus <> "" Or winrmFilter <> "" Or deviceTypeFilter <> "" Or pcTypeFilter <> "" Or uptimeFilter <> "" Or Request.QueryString("needsrelationship") <> "" Then %>
Clear
<% End If %>
Hostname
Serial
Model
OS
Equipment
Uptime
VNC
WinRM
<%
' Build query based on filters
Dim pcStatusFilter, winrmFilterSQL, deviceTypeFilterSQL, pcTypeFilterSQL, uptimeFilterSQL, needsRelationshipFilter, whereClause
pcStatusFilter = Request.QueryString("pcstatus")
winrmFilterSQL = Request.QueryString("winrm")
deviceTypeFilterSQL = Request.QueryString("devicetype")
pcTypeFilterSQL = Request.QueryString("pctype")
uptimeFilterSQL = Request.QueryString("uptime")
needsRelationshipFilter = Request.QueryString("needsrelationship")
' Base query with LEFT JOINs to show all PCs
strSQL = "SELECT m.machineid, m.hostname, m.serialnumber, m.machinenumber, m.machinestatusid, " & _
"m.modelnumberid, m.osid, m.loggedinuser, m.lastupdated, m.isvnc, m.iswinrm, m.lastboottime, " & _
"DATEDIFF(NOW(), m.lastboottime) AS uptime_days, " & _
"vendors.vendor, models.modelnumber, operatingsystems.operatingsystem, " & _
"c.address AS ipaddress, c.macaddress, " & _
"machinestatus.machinestatus, " & _
"eq.machineid AS equipment_id, eq.machinenumber AS equipment_number " & _
"FROM machines m " & _
"LEFT JOIN models ON m.modelnumberid = models.modelnumberid " & _
"LEFT JOIN vendors ON models.vendorid = vendors.vendorid " & _
"LEFT JOIN operatingsystems ON m.osid = operatingsystems.osid " & _
"LEFT JOIN communications c ON c.machineid = m.machineid AND c.isprimary = 1 " & _
"LEFT JOIN machinestatus ON m.machinestatusid = machinestatus.machinestatusid " & _
"LEFT JOIN machinerelationships mr ON (mr.machineid = m.machineid OR mr.related_machineid = m.machineid) AND mr.isactive = 1 AND mr.relationshiptypeid = 3 " & _
"LEFT JOIN machines eq ON (eq.machineid = mr.related_machineid OR eq.machineid = mr.machineid) AND eq.machineid <> m.machineid AND eq.pctypeid IS NULL " & _
"WHERE m.isactive = 1 AND m.pctypeid IS NOT NULL "
' Apply filters
whereClause = ""
If pcStatusFilter <> "" Then
whereClause = whereClause & " AND m.machinestatusid = " & pcStatusFilter
End If
' Filter by WinRM status
If winrmFilterSQL = "needswinrm" Then
' PCs with equipment relationships but no WinRM
whereClause = whereClause & " AND (m.iswinrm = 0 OR m.iswinrm IS NULL)" & _
" AND EXISTS (SELECT 1 FROM machinerelationships mr2 WHERE (mr2.machineid = m.machineid OR mr2.related_machineid = m.machineid) AND mr2.isactive = 1 AND mr2.relationshiptypeid = 3)"
ElseIf winrmFilterSQL = "haswinrm" Then
whereClause = whereClause & " AND m.iswinrm = 1"
ElseIf winrmFilterSQL = "nowinrm" Then
whereClause = whereClause & " AND (m.iswinrm = 0 OR m.iswinrm IS NULL)"
End If
' Filter by device type (laptop vs desktop) based on model name patterns
If deviceTypeFilterSQL = "laptop" Then
whereClause = whereClause & " AND (models.modelnumber LIKE '%Latitude%' OR models.modelnumber LIKE '%Precision%' AND (models.modelnumber NOT LIKE '%Tower%'))"
ElseIf deviceTypeFilterSQL = "desktop" Then
whereClause = whereClause & " AND (models.modelnumber LIKE '%OptiPlex%' OR models.modelnumber LIKE '%Tower%' OR models.modelnumber LIKE '%Micro%')"
End If
' Filter by PC type (pctypeid)
If pcTypeFilterSQL <> "" And IsNumeric(pcTypeFilterSQL) Then
whereClause = whereClause & " AND m.pctypeid = " & pcTypeFilterSQL
End If
' Filter by uptime (days since last boot)
If uptimeFilterSQL <> "" And IsNumeric(uptimeFilterSQL) Then
whereClause = whereClause & " AND m.lastboottime IS NOT NULL AND DATEDIFF(NOW(), m.lastboottime) > " & uptimeFilterSQL
End If
' Filter for specialized PCs needing equipment relationships
If needsRelationshipFilter = "1" Then
whereClause = whereClause & " AND m.pctypeid = 7" & _
" AND NOT EXISTS (SELECT 1 FROM machinerelationships mr WHERE (mr.machineid = m.machineid OR mr.related_machineid = m.machineid) AND mr.relationshiptypeid = 3 AND mr.isactive = 1)"
End If
strSQL = strSQL & whereClause & " GROUP BY m.machineid ORDER BY m.machinenumber ASC, m.hostname ASC"
set rs = objconn.Execute(strSQL)
while not rs.eof
%>
<%
' Uptime column - show days since last boot
If Not IsNull(rs("uptime_days")) And rs("uptime_days") <> "" Then
uptimeDays = CLng(rs("uptime_days") & "")
If uptimeDays > 90 Then
Response.Write("" & uptimeDays & "d")
ElseIf uptimeDays > 30 Then
Response.Write("" & uptimeDays & "d")
ElseIf uptimeDays > 7 Then
Response.Write("" & uptimeDays & "d")
Else
Response.Write("" & uptimeDays & "d")
End If
Else
Response.Write("-")
End If
%>
<%
' VNC column with link
hasVnc = False
If Not IsNull(rs("isvnc")) Then
If rs("isvnc") = True Or rs("isvnc") = 1 Or rs("isvnc") = -1 Then
hasVnc = True
End If
End If
If hasVnc And Not IsNull(rs("hostname")) And rs("hostname") <> "" Then
vncHost = rs("hostname") & ".logon.ds.ge.com"
Response.Write("VNC")
ElseIf hasVnc And Not IsNull(rs("ipaddress")) And rs("ipaddress") <> "" Then
' Fallback to IP address if no hostname
Response.Write("VNC")
ElseIf hasVnc Then
Response.Write("VNC")
Else
Response.Write("-")
End If
%>
<%
' WinRM column
hasWinrm = False
If Not IsNull(rs("iswinrm")) Then
If rs("iswinrm") = True Or rs("iswinrm") = 1 Or rs("iswinrm") = -1 Then
hasWinrm = True
End If
End If
If hasWinrm Then
Response.Write("WinRM")
Else
Response.Write("-")
End If
%>