Endpoint Protection

 View Only
  • 1.  Need query to get PTP definitions from SQL

    Posted Aug 11, 2011 03:24 AM

    Hi buddies,

    Seems that I've lost the query that I used in the past for extracting status of  PTP defs from the SQL. Anyobody has it ? I'm running SEPM RU7.

    BR// Johnny



  • 2.  RE: Need query to get PTP definitions from SQL

    Posted Aug 11, 2011 03:28 AM

    check this document

     

    Symantec™ Endpoint Protection Manager Database Schema Reference

     

    http://www.symantec.com/business/support/index?page=content&id=DOC2410



  • 3.  RE: Need query to get PTP definitions from SQL

    Posted Aug 11, 2011 04:04 AM

    Thanks Rafeeq,

    I'm sure that the key for the SQL cottage is hidden in the pdf you've linked, poor me - not an SQL expert :(

    I got it from the support once before, described very simple like " This is the query, paste it there and enjoy "

    Seems like I'll have to bother them again :|



  • 4.  RE: Need query to get PTP definitions from SQL

    Posted Aug 11, 2011 04:43 AM

    I have to google forum now, let me check :) 



  • 5.  RE: Need query to get PTP definitions from SQL



  • 6.  RE: Need query to get PTP definitions from SQL

    Posted Aug 11, 2011 05:42 AM

    Hi Johnny,

    You may wish to cast your vote in favor of this enhancement request:

    Add PTP Information to SEPM Computer Status Reports (https://www-secure.symantec.com/connect/ideas/add-ptp-information-sepm-computer-status-reports )

    If the idea gathers enough votes, the feature may be built into a future release of SEP.

    Thanks and best regards,

    Mick
     



  • 7.  RE: Need query to get PTP definitions from SQL
    Best Answer

    Posted Aug 11, 2011 07:28 AM

    Hi Rafeeq,

     

    Thanks for the update - I actually found the info I needed.

    SELECT SEM_COMPUTER.COMPUTER_NAME, PATTERN.PATTERN_TYPE, PATTERN.PATTERNDATE, PATTERN.REVISION
    FROM SEM_COMPUTER INNER JOIN
    SEM_AGENT ON SEM_COMPUTER.COMPUTER_ID = SEM_AGENT.COMPUTER_ID INNER JOIN
    SEM_CONTENT ON SEM_AGENT.AGENT_ID = SEM_CONTENT.AGENT_ID INNER JOIN
    PATTERN ON SEM_CONTENT.PATTERN_IDX = PATTERN.PATTERN_IDX
    WHERE (PATTERN.PATTERN_TYPE = 'SYKNAPPS_CAL')

    @Mick 2009 - you got a thumbs up for that, but isn't it so that RU7 is the last one of the SEP11 family ?

    I barely believe that there will be some further development in that direction.



  • 8.  RE: Need query to get PTP definitions from SQL

    Posted Aug 11, 2011 08:07 AM

    Thumbs up from me on this Idea



  • 9.  RE: Need query to get PTP definitions from SQL

    Posted Aug 11, 2011 11:07 PM

    The ideas section is not restricted to one version only.

    If it gets enough votes, hopefully they'll implement it in a future version.

     

    Please vote on the idea.



  • 10.  RE: Need query to get PTP definitions from SQL

    Posted Aug 11, 2011 11:29 PM

    Hi.

    How confident are you in that query? When I run it I get two rows per computer most times and the PATTERNDATE is has a value for yesterday & today. This is noticeable when you use the ORDER BY clause.

    SELECT SEM_COMPUTER.COMPUTER_NAME
      , PATTERN.PATTERN_TYPE
      , PATTERN.PATTERNDATE
      , PATTERN.REVISION
    FROM SEM_COMPUTER INNER JOIN
      SEM_AGENT ON SEM_COMPUTER.COMPUTER_ID = SEM_AGENT.COMPUTER_ID
      INNER JOIN SEM_CONTENT ON SEM_AGENT.AGENT_ID = SEM_CONTENT.AGENT_ID
      INNER JOIN PATTERN ON SEM_CONTENT.PATTERN_IDX = PATTERN.PATTERN_IDX
    WHERE (PATTERN.PATTERN_TYPE = 'SYKNAPPS_CAL')
    ORDER BY SEM_COMPUTER.COMPUTER_NAME, PATTERN.PATTERN_TYPE, PATTERN.PATTERNDATE, PATTERN.REVISION
    

    PS I like my results nicely ordered.