Gheek.net

September 12, 2012

iotop2csv – One way to graph the batch output of iotop

Filed under: linux, perl — lancevermilion @ 5:38 pm

I had a need to know what was writing disk, how much it was writing, and how often it was writing. iotop does a pretty good job of giving you a top like view of disk I/O activity. I decided I wanted to graph this to hopefully make it easier for me to see how heavy writing to the disk might correlate to sluggish CLI response.

The output is in CSV so you can drop it in Excel (if you so wish) and create a simple PivotChart with it. You can do so by selecting all data from the csv (after it is imported/converted to rows/columns using data import or text to columns) then choose insert pivotchart.

Choose fields timestamp, tid, read, write and cmd.
Timestamp goes in AxisFields Categories.
Values, cmd, and tid go in Legend Fields
Sum of read and Sum of write goes in Values.

I never quite got to the smoking gun (just yet) but I was able to see what applications were busy little apps writing to the disk.

I do no warranty this code in any way. Use it as you wish. If you find it useful then please write back saying so. If you find it incorrect or improve/fix it please write back on that as well.

Here is the code

#/usr/bin/perl -w
use strict;
# Enabling strict with strict refs breaks the summary output (when ctrl+c is pressed)
# because of the error Can't use string ("") as a HASH ref while "strict refs" in use.
no strict 'refs';

#Initialize an empty hashref
my $hash_ref = {};

#Catch CTRL+C and then call printsummary subroutine
$SIG{'INT'} = \&printsummary;

#Pipe the output from iotop to a file handle
open fh, "iotop -bt|" or die $!;

# Build the initial Total Value key/values
$hash_ref->{'tsample'} = 0;
$hash_ref->{'tread'} = 0;
$hash_ref->{'twrite'} = 0;

# Divider of columns
my $div = ",";

#Print the header
print "#" x 50 . "\n";
print "Output from iotop -bt\n";
print "#" x 50 . "\n";
print "timestamp" . $div . "tid" . $div . "user" . $div . "read" . $div . "total read each tid" . $div . "write" . $div . "total write each tid" . $div . "cmd" . $div . "sample number" . $div . "Percent of Total Read" . $div . "Percent of Total Write" . "\n";

#While data from iotop process it. Since we get lots of 0.00 read and write values
#don't waste time storing/printing values that are useless. Only store lines that
#have a value to read/write.
while(<fh>) {
  my ($ts,$tid,$prio,$user,$read,undef,$write,undef,undef,undef,undef,undef,$cmd) = split(/\s+/, "$_");
  chomp($cmd);

  #Make sure we fill each tid with dummy values if the tid is a real valid number
  if ( !$hash_ref->{$tid} && ( $tid =~ m/^-?\d+$/ || $tid =~ m/^-?\d+[\/|\.]\d+$/ ) )
  {
    $hash_ref->{$tid}->{'user'} = 'null';
    $hash_ref->{$tid}->{'read'} = 0;
    $hash_ref->{$tid}->{'write'} = 0;
    $hash_ref->{$tid}->{'cmd'} = 'null';
    $hash_ref->{$tid}->{'samples'} = 0;
  }

  #Continue if we have already created a key for the tid and the tid is a real valid number
  if ( $hash_ref->{$tid} && ( $tid =~ m/^-?\d+$/ || $tid =~ m/^-?\d+[\/|\.]\d+$/ ) )
  {
    $hash_ref->{$tid}->{'user'} = $user;
    $hash_ref->{$tid}->{'read'} += $read if $read =~ m/^-?\d+$/ || $read =~ m/^-?\d+[\/|\.]\d+$/;
    $hash_ref->{$tid}->{'write'} += $write if $write =~ m/^-?\d+$/ || $write =~ m/^-?\d+[\/|\.]\d+$/;
    $hash_ref->{$tid}->{'cmd'} = $cmd;
    $hash_ref->{$tid}->{'samples'}++;

    # create totals key/value pair
    $hash_ref->{'tsample'}++;
    $hash_ref->{'tread'} += $read if $read =~ m/^-?\d+$/ || $read =~ m/^-?\d+[\/|\.]\d+$/;
    $hash_ref->{'twrite'} += $write if $write =~ m/^-?\d+$/ || $write =~ m/^-?\d+[\/|\.]\d+$/;

    # Print values of the read/write as they happen
    if ( ( $read =~ m/^-?\d+$/ || $read =~ m/^-?\d+[\/|\.]\d+$/ || $write =~ m/^-?\d+$/ || $write =~ m/^-?\d+[\/|\.]\d+$/ ) && ( $read > 0 || $write > 0 ) )
    {
      my $ptr = 0;
      my $ptw = 0;
      $ptr = ( $hash_ref->{$tid}->{'read'} / $hash_ref->{'tread'} ) * 100 if ( $read > 0 ) && ( $hash_ref->{'tread'} > 0 );
      $ptw = ( $hash_ref->{$tid}->{'write'} / $hash_ref->{'twrite'} ) * 100 if ( $write > 0 ) && ( $hash_ref->{'twrite'} > 0 );

      print $ts . "$div";
      print $tid . "$div";
      print $hash_ref->{$tid}->{'user'} . "$div";
      print $read . "$div";
      print "$hash_ref->{$tid}->{'read'}" . "$div";
      print $write . "$div";
      print "$hash_ref->{$tid}->{'write'}" . "$div";
      print $hash_ref->{$tid}->{'cmd'} . "$div";
      print $hash_ref->{$tid}->{'samples'} . "$div";
      print sprintf("%.2f", $ptr) . "$div";
      print sprintf("%.2f", $ptw) . "\n";
    }
  }
}

# Close the filehandle when we are done.
close(fh);

#Sub routine to print out a summary of disk I/O.
sub printsummary {
print "#" x 50 . "\n";
print "Caught Ctrl+C. Printing Disk I/O summary.\n";
print "#" x 50 . "\n";
print "Total Bytes Read:  $hash_ref->{'tread'}\n";
print "Total Bytes Write: $hash_ref->{'twrite'}\n";
print "#" x 50 . "\n";

for my $id ( keys %$hash_ref )
  {
  if ( $hash_ref->{$id}->{'read'} > 0 || $hash_ref->{$id}->{'write'} > 0 )
    {
      my $psptr = 0;
      my $psptw = 0;
      $psptr = ( $hash_ref->{$id}->{'read'} / $hash_ref->{'tread'} ) * 100 if ( $hash_ref->{'tread'} > 0 );
      $psptw = ( $hash_ref->{$id}->{'write'} / $hash_ref->{'twrite'} ) * 100 if ( $hash_ref->{'twrite'} > 0 );

      print "$id" . " ";
      print $hash_ref->{$id}->{'user'} . " ";
      print $hash_ref->{$id}->{'read'} . " ";
      print $hash_ref->{$id}->{'write'} . " ";
      print $hash_ref->{$id}->{'cmd'} . " ";
      print $hash_ref->{$id}->{'samples'} . " ";
      print sprintf("%.2f", $psptr) . " ";
      print sprintf("%.2f", $psptw);
      print "\n"
    }
  }
  print "#" x 50 . "\n";
}

Here is the csv output

sudo perl iotop2csv.pl 
##################################################
Output from iotop -bt
##################################################
timestamp,tid,user,read,total read each tid,write,total write each tid,cmd,sample number,Percent of Total Read,Percent of Total Write
16:27:00,6003,mysql,0.00,0,318.04,318.04,mysqld,2,0.00,100.00
16:27:00,4959,root,0.00,0,3.74,3.74,nailslogd,2,0.00,1.16
16:27:00,5992,mysql,0.00,0,3.74,3.74,mysqld,2,0.00,1.15
16:27:00,5994,mysql,0.00,0,314.30,314.3,mysqld,2,0.00,49.12
16:27:01,511,root,0.00,0,101.83,101.83,[kjournald],3,0.00,13.73
16:27:01,6557,mysql,0.00,0,3.77,3.77,mysqld,3,0.00,0.51
16:27:02,6530,mysql,0.00,0,7.55,7.55,mysqld,4,0.00,1.00
16:27:03,6009,mysql,0.00,0,7.54,7.54,mysqld,5,0.00,0.99
16:27:03,25220,mysql,3.77,3.77,11.31,11.31,mysqld,5,100.00,1.47
16:27:04,6534,mysql,3.77,3.77,97.94,97.94,mysqld,6,50.00,11.26
16:27:04,1864,root,0.00,0,199.65,199.65,[kjournald],6,0.00,18.67

Here is an example graph via Excel

Advertisements

September 4, 2012

Example of an optimized nested SELECT statements to CASE statements in SQL (using SQLite)

Filed under: sql, SQLite — lancevermilion @ 6:26 pm

I had written a webpage that needed to do a single SQL query and return the information. The information had a key column named ‘AssetnameFindingDetailURL’ and computations needed to be done based on a combination of a few other columns ‘StatusDisplayOverride’ and ‘Severity’. To achieve this I originally just did a HUGE (1110 lines) SQL with nested SELECT statements. This proved to be very slow and almost impossible to understand. I decided it was possible to shorten it so i spent about 1 hour having a go at using a CASE statement. The results were quite good. I shortened the execution time from .4 of a second down to .1 of a second and from 1110 lines of SQL to 110 lines of SQL. I am sure this can be shortened even more but it is beyond my interest at the moment to make it shorter without any procedures/etc. Hopefully someone will find this usefule.

How the Syntax changed

ROUND
( 
  (
    (
      (
        SELECT 
          COUNT(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') 
      )*100.0
    )/COUNT(severity)
  ),2
) AS 'Total_%_Done'

Was turned into

ROUND
(
 COUNT
  (
    CASE 
      WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') THEN 
        Severity 
    END
  )*100.00 / COUNT(Severity), 2 
) AS CatAPercent,

Example output

A|54.48|525|286|100.0|64|64|0|0|29|35|53.21|389|207|47|128|60|147|21.43|70|15|36|18|12|3|0.0|2|0|2|0|0|0
B|73.91|23|17|100.0|3|3|0|0|3|0|76.92|13|10|0|2|10|0|57.14|7|4|3|0|4|0||0|0|0|0|0|0
C|41.67|12|5|100.0|5|5|0|0|5|0|0.0|3|0|3|0|0|0|0.0|3|0|3|0|0|0|0.0|1|0|1|0|0|0
D|12.5|16|2|100.0|1|1|0|0|1|0|0.0|6|0|6|0|0|0|12.5|8|1|7|0|1|0|0.0|1|0|1|0|0|0
E|87.5|8|7|100.0|1|1|0|0|1|0|85.71|7|6|0|1|6|0||0|0|0|0|0|0||0|0|0|0|0|0
F|12.5|8|1|100.0|1|1|0|0|1|0|0.0|4|0|4|0|0|0|0.0|3|0|3|0|0|0||0|0|0|0|0|0
G|100.0|13|13|100.0|2|2|0|0|2|0|100.0|9|9|0|0|9|0|100.0|2|2|0|0|2|0||0|0|0|0|0|0
H|81.4|43|35|100.0|12|12|0|0|4|8|81.48|27|22|0|5|17|5|25.0|4|1|2|1|1|0||0|0|0|0|0|0
I|37.5|48|18|100.0|11|11|0|0|3|8|25.0|28|7|18|3|2|5|0.0|9|0|9|0|0|0||0|0|0|0|0|0
J|54.81|312|171|100.0|18|18|0|0|6|12|54.28|269|146|0|117|16|130|28.0|25|7|0|17|4|3||0|0|0|0|0|0
K|40.48|42|17|100.0|10|10|0|0|3|7|30.43|23|7|16|0|0|7|0.0|9|0|9|0|0|0||0|0|0|0|0|0

Here is the Short version of the SQL (CASE statement).

SELECT
  'ALL DEVICES',
  ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') THEN Severity END)*100.00 / COUNT(Severity), 2 ) AS CatAPercent,
  COUNT(Severity) AS CatAT,
  COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted') THEN Severity END ) AS CatAD,
  ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='1-Category I' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ), 2 ) AS Cat1Percent,
  COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ) AS Cat1T,
  COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted') AND Severity='1-Category I' THEN Severity END ) AS Cat1D,
  COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='1-Category I' THEN Severity END ) AS Cat1O,
  COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='1-Category I' THEN Severity END ) AS Cat1PR,
  COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1TBS,
  COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1S,
  ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='2-Category II' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ), 2 ) AS Cat2Percent,
  COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ) AS Cat2T,
  COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='2-Category II' THEN Severity END ) AS Cat2D,
  COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='2-Category II' THEN Severity END ) AS Cat2O,
  COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='2-Category II' THEN Severity END ) AS Cat2PR,
  COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2TBS,
  COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2S,
  ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='3-Category III' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ), 2 ) AS Cat3Percent,
  COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ) AS Cat3T,
  COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='3-Category III' THEN Severity END ) AS Cat3D,
  COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='3-Category III' THEN Severity END ) AS Cat3O,
  COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='3-Category III' THEN Severity END ) AS Cat3PR,
  COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3TBS,
  COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3S,
  ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='4-Category IV' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ), 2 ) AS Cat4Percent,
  COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ) AS Cat4T,
  COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='4-Category IV' THEN Severity END ) AS Cat4D,
  COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='4-Category IV' THEN Severity END ) AS Cat4O,
  COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='4-Category IV' THEN Severity END ) AS Cat4PR,
  COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4TBS,
  COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4S
FROM
  vms
UNION ALL
SELECT
  UPPER(AssetNameFindingDetailURL) as 'Asset Name',
  CatAPercent,
  CatAT,
  CatAD,
  Cat1Percent,
  Cat1T,
  Cat1D,
  Cat1O,
  Cat1PR,
  Cat1TBS,
  Cat1S,
  Cat2Percent,
  Cat2T,
  Cat2D,
  Cat2O,
  Cat2PR,
  Cat2TBS,
  Cat2S,
  Cat3Percent,
  Cat3T,
  Cat3D,
  Cat3O,
  Cat3PR,
  Cat3TBS,
  Cat3S,
  Cat4Percent,
  Cat4T,
  Cat4D,
  Cat4O,
  Cat4PR,
  Cat4TBS,
  Cat4S
FROM
  (
    SELECT
      AssetNameFindingDetailURL,
      ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') THEN Severity END)*100.00 / COUNT(Severity), 2 ) AS CatAPercent,
      COUNT(Severity) AS CatAT,
      COUNT( CASE WHEN (StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted') THEN Severity END ) AS CatAD,
      ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='1-Category I' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ), 2 ) AS Cat1Percent,
      COUNT( CASE WHEN Severity='1-Category I' THEN Severity END ) AS Cat1T,
      COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='1-Category I' THEN Severity END ) AS Cat1D,
      COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='1-Category I' THEN Severity END ) AS Cat1Percent,
      COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='1-Category I' THEN Severity END ) AS Cat1O,
      COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='1-Category I' THEN Severity END ) AS Cat1PR,
      COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1TBS,
      COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='1-Category I' THEN Severity END ) AS Cat1S,
      ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='2-Category II' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ), 2 ) AS Cat2Percent,
      COUNT( CASE WHEN Severity='2-Category II' THEN Severity END ) AS Cat2T,
      COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='2-Category II' THEN Severity END ) AS Cat2D,
      COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='2-Category II' THEN Severity END ) AS Cat2O,
      COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='2-Category II' THEN Severity END ) AS Cat2PR,
      COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2TBS,
      COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='2-Category II' THEN Severity END ) AS Cat2S,
      ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='3-Category III' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ), 2 ) AS Cat3Percent,
      COUNT( CASE WHEN Severity='3-Category III' THEN Severity END ) AS Cat3T,
      COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='3-Category III' THEN Severity END ) AS Cat3D,
      COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='3-Category III' THEN Severity END ) AS Cat3O,
      COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='3-Category III' THEN Severity END ) AS Cat3PR,
      COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3TBS,
      COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='3-Category III' THEN Severity END ) AS Cat3S,
      ROUND( COUNT(CASE WHEN (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity='4-Category IV' THEN Severity END)*100.00 / COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ), 2 ) AS Cat4Percent,
      COUNT( CASE WHEN Severity='4-Category IV' THEN Severity END ) AS Cat4T,
      COUNT( CASE WHEN ( StatusDisplayOverride='S-Submitted' OR StatusDisplayOverride='TBS-To Be Submitted' ) AND Severity='4-Category IV' THEN Severity END ) AS Cat4D,
      COUNT( CASE WHEN StatusDisplayOverride='O-Open' AND Severity='4-Category IV' THEN Severity END ) AS Cat4O,
      COUNT( CASE WHEN StatusDisplayOverride='PR-Pending Research' AND Severity='4-Category IV' THEN Severity END ) AS Cat4PR,
      COUNT( CASE WHEN StatusDisplayOverride='TBS-To Be Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4TBS,
      COUNT( CASE WHEN StatusDisplayOverride='S-Submitted' AND Severity='4-Category IV' THEN Severity END ) AS Cat4S
    FROM
      vms
    GROUP BY
      AssetNameFindingDetailURL
  ) AS ABC;

Here is the Long version of the SQL (nested SELECT statements).

SELECT
  '*ALL DEVICES*' as 'Asset Name',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
      )*100.0
    )/count(severity)
  ),2) as 'Total_%_Done',
  count(severity) as Total,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
  ) as 'Total_Done',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '1-Category I'
  )
  ),2) as '%_Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I'
  ) as Total_Cat1s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
  ) as 'Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat1,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '2-Category II'
  )
  ),2) as '%_Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II'
  ) as Total_Cat2s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
  ) as 'Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat2,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '3-Category III'
  )
  ),2) as '%_Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III'
  ) as Total_Cat3s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
  ) as 'Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat3,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '4-Category IV'
  )
  ),2) as '%_Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV'
  ) as Total_Cat4s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
  ) as 'Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat4
FROM
  vms
UNION ALL
SELECT
  UPPER(AssetNameFindingDetailURL) as 'Asset Name',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/count(severity)
  ),2) as 'Total_%_Done',
  count(severity) as Total,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Total_Done',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat1s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat1,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat2s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat2,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat3s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat3,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat4s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat4
FROM
  vms as vw
GROUP BY AssetNameFindingDetailURL): no such column: vv.AssetNameFindingDetailURL

Array
(
    [0] => Array
        (
            [file] => /var/www/html/itdb/php/reports.php
            [line] => 811
            [function] => db_execute
            [args] => Array
                (
                    [0] => PDO Object
                        (
                        )

                    [1] => SELECT
  '*ALL DEVICES*' as 'Asset Name',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
      )*100.0
    )/count(severity)
  ),2) as 'Total_%_Done',
  count(severity) as Total,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted')
  ) as 'Total_Done',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '1-Category I'
  )
  ),2) as '%_Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I'
  ) as Total_Cat1s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I'
  ) as 'Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat1,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '2-Category II'
  )
  ),2) as '%_Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II'
  ) as Total_Cat2s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II'
  ) as 'Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat2,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '3-Category III'
  )
  ),2) as '%_Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III'
  ) as Total_Cat3s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III'
  ) as 'Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat3,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '4-Category IV'
  )
  ),2) as '%_Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV'
  ) as Total_Cat4s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV'
  ) as 'Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open'
  ) as O_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research'
  ) as PR_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted'
  ) as TBS_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted'
  ) as S_Cat4
FROM
  vms
UNION ALL
SELECT
  UPPER(AssetNameFindingDetailURL) as 'Asset Name',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/count(severity)
  ),2) as 'Total_%_Done',
  count(severity) as Total,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Total_Done',
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat1s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '1-Category I' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat1',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat1,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '1-Category I' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat1,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat2s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '2-Category II' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat2',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat2,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '2-Category II' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat2,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat3s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '3-Category III' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat3',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat3,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '3-Category III' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat3,
  round( (
    (
      (
        SELECT
          count(severity)
        FROM
          vms
        WHERE
          (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
      )*100.0
    )/ (
     SELECT count(severity) FROM vms WHERE Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  )
  ),2) as '%_Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as Total_Cat4s,
  (
      SELECT
        count(severity)
      FROM
        vms
      WHERE
        (StatusDisplayOverride = 'TBS-To Be Submitted' OR StatusDisplayOverride = 'S-Submitted') AND Severity = '4-Category IV' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as 'Done_Cat4',
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'O-Open' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as O_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'PR-Pending Research' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as PR_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'TBS-To Be Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as TBS_Cat4,
  (
    SELECT
      count(severity)
    FROM
      vms
    WHERE
      Severity = '4-Category IV' AND StatusDisplayOverride = 'S-Submitted' AND vv.AssetNameFindingDetailURL = AssetNameFindingDetailURL
  ) as S_Cat4
FROM
  vms as vv
GROUP BY AssetNameFindingDetailURL

Create a free website or blog at WordPress.com.