3 Replies Latest reply on Feb 4, 2008 7:49 AM by damo666

    Reporting when a Dat file has been deployed to a server

      I will get straight to the point. I need to find information from the epo 4.0 database for each server and when each dat file has been deloyed to that server.

      On epo 3 I created a table call porductrelease and then placed all the dats I wanted into this table and then run the below query, which was taken from epo 3. All I wanted to get was if the given Dat file had been deployed with a window of 12 hours when the dat was released. Below is the query


      productrelease table was updated via a perl script that read all mails inbound from mcafee and then released dates where based on the dates of the email. example below.

      5110.0000 VIRUSCAN8600 2007-08-31 16:37:27.000



      select t1.productcode as 'AV Software',
      t1.version as 'DAT Version',
      t1.versiondate as 'Release E-Mail Date',
      firstdeployment as 'First 12hr Deployment',
      lastdeployment as 'Last 12hr deployment',
      scope as '12hr Coverage Target',
      deployments as '12hr Coverage No.',
      deployments*100/scope as '12hr Coverage %'
      from (
      select z.version, z.productcode, z.versiondate, count(*) as scope
      from branchnode w
      join leafnode x on (w.autoid=x.parentid)
      join productproperties y on (x.autoid=y.parentid)
      right outer join productrelease z on (y.productcode=z.productcode)
      group by z.productcode, z.version, z.versiondate
      ) t1
      left outer join
      (
      select a.version, a.productcode, count(b.productcode) as deployments,
      min(b.eventdatetime) as firstdeployment,
      max(b.eventdatetime) as lastdeployment
      from ProductRelease a
      left outer join ProductEvents b
      on (
      a.version=b.version
      and
      a.productcode=b.productcode
      )
      where b.type='DAT'
      and b.error=0
      and b.eventdatetime < (a.versiondate+0.5)
      group by a.version, a.productcode
      ) t2
      on ( t1.version=t2.version
      and t1.productcode=t2.productcode)
      order by t1.versiondate desc



      I have looked through the various queries with epo4.0 but cannot find the tables I need. The only table that for some unknown reason to me that is missing is the "productevents" table.

      Any help is really appreciated.

      Damian:D