Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Combining and displaying data

    I'm not sure if this should go in this section some other section so if I put it in the wrong section please forgive me.

    Now onto my problem. I have an SQL database back end with an ASP front end. I'm trying to only pull certain data from the database to be displayed on the webpage. Except I'd like some of the records to combine if they have similar fields.

    My database has the following fields many fields but the only ones I want to pull data from are below.

    Audit ID
    Risk
    SevCode
    Vendor
    Vulnerability
    NumVuln
    IP
    Status
    FixAction

    I have the page setup to just display each record in a table row. It basically looks like a spreadsheet. Then I just have a repeat region that cycles through the database.

    In some of the sections I would like to have it combine all "IPs" that have the same "Audit ID" then display a count of the "IP" in the section with the header of "NumVuln"

    In the section with the header of IP I want it to display each unique IP associated with the IP in the same cell but separated by a semi-colon( grouped based on what the "AuditID" is.

    If someone could point me in the right direction I would really appreciate it. I'm not a SQL or ASP guy but am trying to tackle this project because if I can get it to work it will cut about 40 hours of work a month. Thanks!

    I hope I was able to do a good enough job clarifying my problem.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,557 Times in 4,521 Posts
    Show us the basic SQL query you are currently using.

    And, if possible, the ASP code you are currently using to display this.

    Question: If two records have the same AuditID, then will they *ALSO* have all the same values for Risk, SevCode, Vendor, Vulnerability, Status, and FixAction?

    If they don't, then how do you choose which of several Risk values (just to pick an example) to display with the single AuditID?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Question: If two records have the same AuditID, then will they *ALSO* have all the same values for Risk, SevCode, Vendor, Vulnerability, Status, and FixAction?

    If they don't, then how do you choose which of several Risk values (just to pick an example) to display with the single AuditID?
    To answer your question, yes they will all have the same values. The only value that should be different will be the IPs. Thanks for your help!



    I think I may be on the right track but still having issues displaying any data besides "Audit ID" and "IPnum" I'm posting my 2 recordset SQL statements and then the ASP code.

    This 1st section of code is the recordset I used initially just to display data but at the moment I'm not using it
    Code:
    SELECT *
    FROM Retina
    WHERE Retina.Risk = 'High'
    This is the SQL statement I just got to work and it does some of what I want (displaying AuditID and counting the number of IPs associated with each AuditID)
    Code:
    SELECT AuditID, COUNT(DISTINCT IP)AS IPNum
    FROM retina
    WHERE Risk = 'High'  GROUP BY AuditID
    Here is my ASP page.
    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <!--#include file="Connections/connSQL.asp" -->
    <%
    Dim rsHigh
    Dim rsHigh_cmd
    Dim rsHigh_numRows
    
    Set rsHigh_cmd = Server.CreateObject ("ADODB.Command")
    rsHigh_cmd.ActiveConnection = MM_connSQL_STRING
    rsHigh_cmd.CommandText = "SELECT * FROM Retina WHERE Retina.Risk = 'High'" 
    rsHigh_cmd.Prepared = true
    
    Set rsHigh = rsHigh_cmd.Execute
    rsHigh_numRows = 0
    %>
    <%
    Dim rsCount
    Dim rsCount_cmd
    Dim rsCount_numRows
    
    Set rsCount_cmd = Server.CreateObject ("ADODB.Command")
    rsCount_cmd.ActiveConnection = MM_connSQL_STRING
    rsCount_cmd.CommandText = "SELECT AuditID, COUNT(DISTINCT IP)AS IPNum FROM retina WHERE Risk = 'High'  GROUP BY AuditID" 
    rsCount_cmd.Prepared = true
    
    Set rsCount = rsCount_cmd.Execute
    rsCount_numRows = 0
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index
    
    Repeat1__numRows = -1
    Repeat1__index = 0
    rsCount_numRows = rsCount_numRows + Repeat1__numRows
    %>
    <%
    '  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
    
    Dim rsHigh_total
    Dim rsHigh_first
    Dim rsHigh_last
    
    ' set the record count
    rsHigh_total = rsHigh.RecordCount
    
    ' set the number of rows displayed on this page
    If (rsHigh_numRows < 0) Then
      rsHigh_numRows = rsHigh_total
    Elseif (rsHigh_numRows = 0) Then
      rsHigh_numRows = 1
    End If
    
    ' set the first and last displayed record
    rsHigh_first = 1
    rsHigh_last  = rsHigh_first + rsHigh_numRows - 1
    
    ' if we have the correct record count, check the other stats
    If (rsHigh_total <> -1) Then
      If (rsHigh_first > rsHigh_total) Then
        rsHigh_first = rsHigh_total
      End If
      If (rsHigh_last > rsHigh_total) Then
        rsHigh_last = rsHigh_total
      End If
      If (rsHigh_numRows > rsHigh_total) Then
        rsHigh_numRows = rsHigh_total
      End If
    End If
    %>
    <%
    ' *** Recordset Stats: if we don't know the record count, manually count them
    
    If (rsHigh_total = -1) Then
    
      ' count the total records by iterating through the recordset
      rsHigh_total=0
      While (Not rsHigh.EOF)
        rsHigh_total = rsHigh_total + 1
        rsHigh.MoveNext
      Wend
    
      ' reset the cursor to the beginning
      If (rsHigh.CursorType > 0) Then
        rsHigh.MoveFirst
      Else
        rsHigh.Requery
      End If
    
      ' set the number of rows displayed on this page
      If (rsHigh_numRows < 0 Or rsHigh_numRows > rsHigh_total) Then
        rsHigh_numRows = rsHigh_total
      End If
    
      ' set the first and last displayed record
      rsHigh_first = 1
      rsHigh_last = rsHigh_first + rsHigh_numRows - 1
      
      If (rsHigh_first > rsHigh_total) Then
        rsHigh_first = rsHigh_total
      End If
      If (rsHigh_last > rsHigh_total) Then
        rsHigh_last = rsHigh_total
      End If
    
    End If
    %>
    
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>High Retina Vulnerabilities</title>
    <style type="text/css">
    <!--
    .style20 {font-family: Arial, Helvetica, sans-serif; font-weight: bold; font-size: 12px; }
    .style35 {	font-family: Arial, Helvetica, sans-serif;
    	font-size: 12px;
    }
    .style36 {font-size: 12px}
    .style37 {font-family: Arial, Helvetica, sans-serif}
    -->
    </style>
    </head>
    
    <body>
    <p class="style36">&nbsp;Total # of High Vulnerabilities - </p>
    <table width="100%" border="0">
      <tr bgcolor="#FFFF00">
        <td width="11%" height="15" class="style20"><span class="style20">Audit ID</span></td>
        <td class="style20"><strong>ICVA</strong></td>
        <td class="style20"><strong>Risk Level</strong></td>
        <td width="6%" class="style20"><span class="style20">Severity Code</span></td>
        <td width="4%" class="style20"><span class="style20">Vendor</span></td>
        <td width="8%" class="style20"><span class="style20">Vulnerability</span></td>
        <td width="9%" class="style20"><span class="style20"># Vulnerable</span></td>
        <td width="16%" class="style20"><span class="style20">IP address(es)    Affected</span></td>
        <td width="10%" class="style20"><span class="style20">Status    (Mitigated Y/N)</span></td>
        <td width="21%" class="style20"><span class="style20">Fix Action to    be applied or applied (Notes)</span></td>
      </tr>
      <% 
    While ((Repeat1__numRows <> 0) AND (NOT rsCount.EOF)) 
    %>
        <tr>
          <td class="style36"><%=(rsCount.Fields.Item("AuditID").Value)%></td>
          <td width="9%" class="style36">&nbsp;</td>
          <td width="6%" class="style36">&nbsp;</td>
          <td class="style36"><span class="style36">
            <%
    Dim SevCode 
    SevCode = (rsHigh.Fields.Item("SevCode").Value)
    %>
            <%
    Select Case SevCode
    	Case "Category I"
    			Response.write("High")
    	Case "Category II"
    			Response.write("Medium")
    	Case "Category III"
    			Response.write("Low")
    	Case "Category IV"
    			Response.write("Information")
    End Select
    %>
                </span></td>
          <td class="style36"><span class="style36"></span></td>
          <td class="style36"><span class="style36"></span></td>
          <td class="style36"><%=(rsCount.Fields.Item("IPNum").Value)%></td>
          <td class="style36"><span class="style36"></span></td>
          <td class="style36">&nbsp;</td>
          <td class="style36"><span class="style36"></span></td>
        </tr>
        <% 
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      rsCount.MoveNext()
    Wend
    %>
    </table>
    </body>
    </html>
    <%
    rsHigh.Close()
    Set rsHigh = Nothing
    %>
    <%
    rsCount.Close()
    Set rsCount = Nothing
    %>

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,557 Times in 4,521 Posts
    Oh...DrunkWalker code!

    Ugh. I will have to get back to you a bit later. And I hope you don't mind that I utterly ignore the DreadedWeaver code there.

    One thing: You don't really need the COUNT(*) stuff. Not sure yet if I'll use it or not.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    synapses (01-26-2012)

  • #5
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Haha yeah sorry about the dreamweaver code. Like I said I'm inexperienced

    Thanks for your help though!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,557 Times in 4,521 Posts
    Okay, here goes.

    I couldn't find a value for the one field, so it is left as ????.

    And I took you at your word here:
    Question: If two records have the same AuditID, then will they *ALSO* have all the same values for Risk, SevCode, Vendor, Vulnerability, Status, and FixAction?

    Answer: yes they will all have the same values. The only value that should be different will be the IPs.
    So...give it a shot:
    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
                          "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>High Retina Vulnerabilities</title>
    <style type="text/css">
    * { 
        font-family: Arial, Helvetica, sans-serif; font-size: 14px;
    }
    tr.dataRow td {
        font-size: 12px;
    }
    tr.headerRow {
       background-color: #FFFF00; 
    }
    </style>
    </head>
    <body>
    <!--#include file="Connections/connSQL.asp" -->
    <%
    ' first, set up a simple table of severity code translations for later efficiency
    Set SevCodes = Server.CreateObject("Scripting.Dictionary")
    SevCodes.Add "Category I","High"
    SevCodes.Add "Category II","Medium"
    SevCodes.Add "Category III","Low"
    SevCodes.Add "Category IV","Information"
    
    
    Set conn = Server.CreateObject ("ADODB.Connection")
    conn.Open MM_connSQL_STRING
    
    SQL = "SELECT AuditID, Risk, SevCode, Vendor, Vulnerability, NumVuln, IP, Status, FixAction " _
        & " FROM Retina " _
        & " WHERE Retina.Risk = 'High' " _
        & " ORDER BY AuditID, IP "
    
    Set RS = conn.Execute( SQL )
    
    %>
    <table width="100%" border="0">
      <tr class="headerRow" >
        <td width="11%">Audit ID</td>
        <th>ICVA</td>
        <th>Risk Level</td>
        <td width="6%">Severity Code</td>
        <td width="4%">Vendor</td>
        <td width="8%">Vulnerability</td>
        <td width="9%"># Vulnerable</td>
        <td>IP address(es) Affected</td>
        <td width="10%">Status (Mitigated Y/N)</td>
        <td width="21%">Fix Action to be applied or applied (Notes)</td>
      </tr>
    <% 
    curid = ""
    iplist = ""
    numRecords = 0
    
    Do While True
        done = RS.EOF 
        If Not done Then done = ( curid <> RS("AuditID")
        If done AND curid <> "" Then
            ' we have collected all IP addresses for this one Audit ID, 
            ' so now we can dump out a row of data:
    %>
        <tr class="dataRow">
          <td><%=curid%></td>
          <td width="9%">????</td>
          <td width="6%"><%=risk%></td>
          <td><%=severity%></td>
          <td><%=vendor%></td>
          <td><%=vuln%></td>
          <td><%=numVuln%></td>
          <td><%=iplist%></td>
          <td><%=status%></td>
          <td><%=fixAction%></td>
        </tr>
    <%
            ' if the reason for the dump was EOF, then quit
            If RS.EOF Then Exit Do
    
            ' restart the list of ips
            iplist = ""    
        End If
    
        ' so regardless of whether we dumped or not, 
        ' collect the information...
        ' all records with same AuditID are supposed to be *IDENTICAL*
        ' except for IP address, so we only collect them when 
        ' the list of IP's is empty:
        If iplist = "" Then
            curid = RS("AuditID")
            risk = RS("Risk")
            severity = SevCodes(RS("SevCode"))
            Vendor = RS("Vendor")
            Vuln = RS("Vulnerability")
            NumVuln = RS("NumVuln")
            status = RS("Status")
            fixAction = RS("FixAction")
            iplist = RS("IP") ' first ip address, no comma
        Else
            ' if we have already started a list, just tack on to it:
            iplist = iplist & "," & RS("IP")
        End If
        ' and go do the next record:
        RS.MoveNext
        numRecords = numRecords + 1
    Loop
    
    RS.Close
    Conn.Close
    %>
    </table>
    <p>&nbsp;Total # of High Vulnerabilities - <%=numRecords%></p>
    
    </body>
    </html>
    Last edited by Old Pedant; 01-26-2012 at 04:58 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    synapses (01-26-2012)

  • #7
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I don't go back to work until Friday so I'll try it then. Thanks so much for your help!

  • #8
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Everything worked fine after adding a missing ) thanks for your help!

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,557 Times in 4,521 Posts
    Oh, yeah. Here:
    Code:
        If Not done Then done = ( curid <> RS("AuditID") )
    Well, since it was 100% off the top of my head, I'll accept a missing ) as within reason.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •