Please Help! XLS Filter problem

Hello experts,

I have a script that I run for some vulnerability reports that parses and filters XML information into a handy table.  I have used it for Nessus data in the past without issues.  However, now we are utilizing Retina Vulnerability scanners that export an XML structure a little different.

I manipulated the XSL file to accomodate the XML for Retina, BUT, I am having issues displaying the correct data.  For instance, I used to have an XML node called "<severity>" that was numerical (1 through 3).  Now, with the new XML structure, the equivalent is called "<pciLevel>" and is not numeric but instead is like this "3 (High)".  I was thinking that perhaps I should utilize the "<risk>" node instead to filter it, but then again, it is not numerical which I am used to.

I had a sort function working with just the numeric version, but with the alpha numeric result the sort function doesn't seem to want to display anything.

I have an IF statement in place "<xsl:if test="pciLevel &gt; 1 (Low)">" which I assume is the culprit.  But, then again, I have another field that filters a little more like this:

<xsl:choose>
      <xsl:when test="pciLevel = 3 (High)">
      <td bgcolor="red" bordercolor="#000000" align="center" valign="middle" width="90px">
      <xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>High</td>
                     </xsl:when>

      <xsl:otherwise>
      <td bgcolor="green" align="center" valign="middle" width="90px">
      <xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>Low</td>
      </xsl:otherwise>
</xsl:choose>


I hope what I am asking makes sense.  Any help would be greatly appreciated.

Thanks,
XML:
 
<scanJob>
 
<hosts>
<host>
<ip>192.168.3.102</ip>
<netBIOSName>N/A</netBIOSName>
<dnsName>unknown</dnsName>
<mac>N/A</mac>
<os>N/A</os>
<audit>
<rthID>3688</rthID>
<cve>CVE-1999-0524</cve>
<name>ICMP Timestamp Request</name>
<description>ICMP Timestamp request is allowed from arbitrary hosts.</description>
<date>06/24/2009</date>
<risk>High</risk>
<pciLevel>5 (Urgent)</pciLevel>
<cvssScore>0 [AV:L/AC:L/Au:N/C:N/I:N/A:N]</cvssScore>
<fixInformation>Filter the ICMP Timestamp request.</fixInformation>
</audit>
</host>
 
<host>
<ip>192.168.3.114</ip>
<netBIOSName>N/A</netBIOSName>
<dnsName>unknown</dnsName>
<mac>N/A</mac>
<os>N/A</os>
<audit>
<rthID>3688</rthID>
<cve>CVE-1999-0524</cve>
<name>ICMP Timestamp Request</name>
<description>ICMP Timestamp request is allowed from arbitrary hosts.</description>
<date>06/24/2009</date>
<risk>Medium</risk>
<pciLevel>4 (Critical)</pciLevel>
<cvssScore>0 [AV:L/AC:L/Au:N/C:N/I:N/A:N]</cvssScore>
<fixInformation>Filter the ICMP Timestamp request.</fixInformation></audit>
</host>
<host>
<ip>192.168.3.215</ip>
<netBIOSName>N/A</netBIOSName>
<dnsName>unknown</dnsName>
<mac>N/A</mac>
<os>N/A</os>
<audit>
<rthID>3688</rthID>
<cve>CVE-1999-0524</cve>
<name>ICMP Timestamp Request</name>
<description>ICMP Timestamp request is allowed from arbitrary hosts.</description>
<date>06/24/2009</date>
<risk>Low</risk>
<pciLevel>1 (Low)</pciLevel>
<cvssScore>0 [AV:L/AC:L/Au:N/C:N/I:N/A:N]</cvssScore>
<fixInformation>Filter the ICMP Timestamp request.</fixInformation>
</audit>
</host>
</hosts>
 
</scanJob>
 
 
 
 
 
 
The XSL:
 
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
	<xsl:output method="html" encoding="UTF-8"/>
 
	<xsl:template name="GetHostName">
		<xsl:param name="rthID"/>
		<xsl:for-each select="//hosts/host">
			<xsl:if test="audit/rthID = $rthID">
				<xsl:value-of select="ip"/>
				<br/>
			</xsl:if>
		</xsl:for-each>
	</xsl:template>
	<xsl:template match="hosts/host/audit">
		<br/>Plugin ID: <xsl:value-of select="rthID"/>
		<xsl:call-template name="GetHostName">
			<xsl:with-param name="rthID" select="rthID"/>
		</xsl:call-template>
	</xsl:template>
	<xsl:key name="pcodes" match="//host/audit" use="rthID"/>
	<xsl:template match="/">
		
 
		<!-- COUNTER SCRIPT -->
		<script type="text/javascript">
		var count=0;
		function incrementCounter() { count++; document.write(count); } 
		</script>
 
 
 
 
 
		<table border="0" style="width: 610px; font-family: Arial;  font-size: 10px;" align="center">
			<tr>
				<th id="cells">Finding Number</th>
				<th id="cells">NIST 800-53/ DHS Control Severity</th>
				<th id="cells">Machine Name</th>
				<th id="cells">Finding</th>
				<th id="cells">Suggested Mitigation </th>
			</tr>
			<xsl:for-each select="//host/audit[generate-id(.)=
        generate-id(key('pcodes', rthID)[1])]">
				<xsl:sort select="pciLevel"/>
				<xsl:for-each select="key('pcodes', rthID)">
					<xsl:sort select="../../host/ip"/>
					
					<xsl:if test="pciLevel &gt; 1 (Low)">
					<tr  style="vertical-align: top; height: 40px;">
 
 
 
						    <xsl:if test="position() = 1">
							
								<td align="center" valign="center" width="90px">
								<xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>
							
									Nessus-<!--xsl:value-of select="position()"/-->
									<script language="javascript">
									incrementCounter();
									</script>
 
								
								</td>
 
 
 
 
 
 
						<xsl:choose>
							<xsl:when test="pciLevel = 3 (High)">
							<td bgcolor="red" bordercolor="#000000" align="center" valign="middle" width="90px">
							   	<xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>
								High
							</td>
							</xsl:when>
						
 
							<xsl:otherwise>
							<td bgcolor="green" align="center" valign="middle" width="90px">
							   	<xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>
								Low
							</td>
							
							</xsl:otherwise>
						</xsl:choose>
 
								<td align="center" valign="middle">
									  
										<xsl:if test="../../host/audit/rthID = rthID">
										<xsl:call-template name="GetHostName">
										<xsl:with-param name="rthID" select="rthID"/>
										</xsl:call-template>
										</xsl:if>
										
	
 
 
 
							<td width="250px">
								<xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>
								<b><xsl:value-of select="name"/> </b><p></p><xsl:value-of select="description"/>
								
 
 
 
							</td>
							
 
							<td align="center" valign="center">
								<xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>
							
									
								
							</td>
 
						
							</td>
						</xsl:if>
					</tr>
					</xsl:if>
				</xsl:for-each>
			</xsl:for-each>
		</table>
	</xsl:template>
</xsl:stylesheet>

Open in new window

shark1998Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert BormansInformation ArchitectCommented:
why don't you filter out everything  that is not numerical from the pciLevel element
you can do that with a simple translate
translate(pciLevel, translate(picLevel, '0123456789', ''), '')
now all non numeric characters are removed
(high and low seem redundant to me)
and you can use the pciLevel element, exactly the same way as you did before with severity
0
shark1998Author Commented:
Can you provide an example of how to accomplish this.  Thank you for your comments.
0
Geert BormansInformation ArchitectCommented:
well, your test should become
<xsl:if test="number(translate(pciLevel, translate(picLevel, '0123456789', ''), '')) > 1">
(simply ignore the "low")

and the sort
<xsl:sort select="number(translate(pciLevel, translate(picLevel, '0123456789', ''), '')) "/>
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Learn More!
shark1998Author Commented:
I am sorry.  But which sections should be replaced.  I have mutliple Sort and if test sections.  
0
shark1998Author Commented:
Would you mind putting it the way it should be in the code that I provided?  It would be appreciated.
0
Geert BormansInformation ArchitectCommented:
well, I assume you know better then I do where you want to sort based on the numerics in pciLevel
and I assume that you know better than I do where you need to test for a numeric in the pciLevel to be > 1

I will have a look though
0
Geert BormansInformation ArchitectCommented: 

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
    <xsl:output method="html" encoding="UTF-8"/>
    
    <xsl:template name="GetHostName">
        <xsl:param name="rthID"/>
        <xsl:for-each select="//hosts/host">
            <xsl:if test="audit/rthID = $rthID">
                <xsl:value-of select="ip"/>
                <br/>
            </xsl:if>
        </xsl:for-each>
    </xsl:template>
    <xsl:template match="hosts/host/audit">
        <br/>Plugin ID: <xsl:value-of select="rthID"/>
        <xsl:call-template name="GetHostName">
            <xsl:with-param name="rthID" select="rthID"/>
        </xsl:call-template>
    </xsl:template>
    <xsl:key name="pcodes" match="//host/audit" use="rthID"/>
    <xsl:template match="/">
        
        
        <!-- COUNTER SCRIPT -->
        <script type="text/javascript">
            var count=0;
            function incrementCounter() { count++; document.write(count); } 
        </script>
        
        
        
        
        
        <table border="0" style="width: 610px; font-family: Arial;  font-size: 10px;" align="center">
            <tr>
                <th id="cells">Finding Number</th>
                <th id="cells">NIST 800-53/ DHS Control Severity</th>
                <th id="cells">Machine Name</th>
                <th id="cells">Finding</th>
                <th id="cells">Suggested Mitigation </th>
            </tr>
            <xsl:for-each select="//host/audit[generate-id(.)=
                generate-id(key('pcodes', rthID)[1])]">
                <xsl:sort select="number(translate(pciLevel, translate(pciLevel, '0123456789', ''), '')) "/>
                <xsl:for-each select="key('pcodes', rthID)">
                    <xsl:sort select="../../host/ip"/>
                    
                    <xsl:if test="number(translate(pciLevel, translate(pciLevel, '0123456789', ''), '')) > 1">
                        <tr  style="vertical-align: top; height: 40px;">
                            
                            
                            
                            <xsl:if test="position() = 1">
                                
                                <td align="center" valign="center" width="90px">
                                    <xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>
                                    
                                    Nessus-<!--xsl:value-of select="position()"/-->
                                    <script language="javascript">
                                        incrementCounter();
                                    </script>
                                    
                                    
                                </td>
                                
                                
                                
                                
                                
                                
                                <xsl:choose>
                                    <xsl:when test="number(translate(pciLevel, translate(pciLevel, '0123456789', ''), '')) = 3">
                                        <td bgcolor="red" bordercolor="#000000" align="center" valign="middle" width="90px">
                                            <xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>
                                            High
                                        </td>
                                    </xsl:when>
                                    
                                    
                                    <xsl:otherwise>
                                        <td bgcolor="green" align="center" valign="middle" width="90px">
                                            <xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>
                                            Low
                                        </td>
                                        
                                    </xsl:otherwise>
                                </xsl:choose>
                                
                                <td align="center" valign="middle">
                                    
                                    <xsl:if test="../../host/audit/rthID = rthID">
                                        <xsl:call-template name="GetHostName">
                                            <xsl:with-param name="rthID" select="rthID"/>
                                        </xsl:call-template>
                                    </xsl:if>
                                    
                                    
                                    
                                    
                                    
                                    <td width="250px">
                                        <xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>
                                        <b><xsl:value-of select="name"/> </b><p></p><xsl:value-of select="description"/>
                                        
                                        
                                        
                                        
                                    </td>
                                    
                                    
                                    <td align="center" valign="center">
                                        <xsl:attribute name="rowspan"><xsl:value-of select="count(key('pcodes', rthID))"/></xsl:attribute>
                                        
                                        
                                        
                                    </td>
                                    
                                    
                                </td>
                            </xsl:if>
                        </tr>
                    </xsl:if>
                </xsl:for-each>
            </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shark1998Author Commented:
Thanks! Now I see what you mean.  Your work is appreciated.
0
Geert BormansInformation ArchitectCommented:
welcome,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.