Eliminate Duplicate Data

dalezjc · August 9th, 2010, 11:40 AM

I have a query that pulls server data along with script parameters, and there are lots of script parameters for each server, and it looks like this:

Code:

 
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_DataIndividual Collect individual processor utilization data? NULL n
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_DataOverall Collect total system utilization data? NULL n
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_DataQueue Collect processor queue data? NULL n
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_EventIndividual Create event if any individual CPU is high? NULL n
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_EventOverall Create event if total system CPU is high? NULL y
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_EventSMTPServerMissing Create event if SMTP server is not accessible? NULL y

What I'd like to do is pull the server name and policy information just once, and then the unique set of paramters. My query looks like this:

Code:

 
SQL="select distinct server_name, script_name, KSG_Name, Version, Parameter_Name,
Parameter_Description, Parameter_ValueINT, Parameter_ValueStr, Override 
from _JobInfo i 
inner Join _JobParameters p on i.UID = p.UID
where server_name like '%server01%'"

Obviously, the distinct isn't going to work because each row does have distinct data, it also happens to have repeating data.

What's the best way to list the repeating data just once, along with the unique data?

Thanks!

Old Pedant · August 9th, 2010, 02:36 PM

Just like you are doing. And then use server side coding/scripting (ASP, ASP.NET, whatever) to produce prettified output.

If you *must* do this from the query, it's possible, with the addition of another field that hopefully/presumably you can simply hide when you do the report.

dalezjc · August 9th, 2010, 03:13 PM

Right, which is what I'm trying to do:

Code:

 
<tr>
  <td width=150><font face="Verdana" size=1>
         <%response.write rs("server_name")%>
  </td>
  <td width=150><font face="Verdana" size=1>
         <%response.write rs("ksg_name")%>
  </td>
  <td width=150><font face="Verdana" size=1>
         <%response.write rs("script_name")%>
  </td>
  <td width=150><font face="Verdana" size=1>
         <%response.write rs("version")%>
  </td>
  <td width=300><font face="Verdana" size=1>
         <%response.write rs("parameter_description")%><br>
         Integer Value: <%response.write rs("parameter_ValueINT")%><br>
         String Value: <%response.write rs("parameter_ValueSTR")%><br>
         Override: <%response.write rs("override")%><br>
  </td>

</tr>

However, I'm not sure how to get just the unique data.

Old Pedant · August 9th, 2010, 03:35 PM

Code:

<%
SQL="select distinct server_name, KSG_Name, script_name, Version, " _
   & " Parameter_Name, Parameter_Description, Parameter_ValueINT, " _
   & " Parameter_ValueStr, Override " _
   & " from _JobInfo i Inner Join _JobParameters p on i.UID = p.UID " _
   & " where server_name like '%server01%' " _
   & " ORDER BY server_name, KSG_name "
Set RS = conn.Execute(SQL)
priorServer = ""
priorKSG = ""
Do Until RS.EOF
    currentServer = RS("server_name")
    currentKSG = RS("ksg_name")
    If currentServer = priorServer AND currentKSG = priorKSG Then
        currentServer = "&nbsp;"
        currentKSG = "&nbsp;"
    Else
        priorServer = currentServer
        priorKSG = currentKSG
    End If
%>
<tr>
  <td width=150><font face="Verdana" size=1><%=currentServer%></td>
  <td width=150><font face="Verdana" size=1><%=currentKSG%></td>
  <td width=150><font face="Verdana" size=1>%=rs("script_name")%></td>
  <td width=150><font face="Verdana" size=1><%=rs("version")%></td>
  <td width=300><font face="Verdana" size=1>
         <%=rs("parameter_description")%><br>
         Integer Value: <%=rs("parameter_ValueINT")%><br>
         String Value: <%=rs("parameter_ValueSTR")%><br>
         Override: <%=rs("override")%><br>
  </td>
</tr>
<%
    rs.MoveNext
Loop
%>

Don't forget the ORDER BY or all bets are off on whether the grouping works or not!

And there's no reason to use <%response.write xxx%>. <%=xxx%> truly is just "shorthand" for that (it's actually converted to response.write by the ASP engine before VBScript sees it).

Old Pedant · August 9th, 2010, 03:39 PM

p.s.: Actually, your HTML there is illegal. All those <font> tags with no matching </font> tags! Wouldn't it be better to use

Code:

<style type="text/css">
tr.info td {
    font-family: Verdana, Arial, sans-serif;
    font-size: small;
    width: 150px;
}
</style>

and then just:

Code:

<tr class="info">
  <td><%=currentServer%></td>
  <td><%=currentKSG%></td>
  <td><%=rs("script_name")%></td>
  <td><%=rs("version")%></td>
  <td style="width: 300px;">
         <%=rs("parameter_description")%><br>
         Integer Value: <%=rs("parameter_ValueINT")%><br>
         String Value: <%=rs("parameter_ValueSTR")%><br>
         Override: <%=rs("override")%><br>
  </td>
</tr>

dalezjc · September 13th, 2010, 11:54 AM

As usual, you're right on Old Pendant!