Post reply

Simplify result set

  • March 4, 2012 at 1:02 pm

    #74408

    I am reporting on some auditing activity from a sql trace table for the last 7 days. Current query (below) shows alot of duplication. I would like to report on access to say once every hour, and especially if its a different table being accessed.

    Here's the query:

    USE SQLPERF

    SELECT LOGINNAME, HOSTNAME, APPLICATIONNAME, STARTTIME, OBJECTNAME, DATABASENAME

    FROM SQLTRACE WHERE STARTTIME > DATEADD(d,-7,GETDATE())

    AND LOGINNAME IN ('DOMAIN\HRTS' )

    AND OBJECTNAME IS NOT NULL

    ORDER BY LOGINNAME, STARTTIME

    Here is a sample of the result set:

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-25 23:31:24.820PS_JOBHR83PRD

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-25 23:45:07.247PS_JOBCODE_TBLHR83PRD

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-25 23:45:07.247PS_JOBCODE_TBLHR83PRD

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-26 23:00:21.107PS_JOBHR83PRD

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-26 23:00:21.107PS_JOBHR83PRD

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-26 23:00:21.107PS_JOBHR83PRD

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-26 23:00:21.107PS_JOBHR83PRD

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-26 23:00:21.107PS_JOBHR83PRD

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-26 23:00:21.107PS_JOBHR83PRD

    Ideal result set:

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-25 23:31:24.820PS_JOBHR83PRD

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-25 23:45:07.247PS_JOBCODE_TBLHR83PRD

    DOMAIN\HRTSServer1A Microsoft SQL Server2012-02-26 23:00:21.107PS_JOBHR83PRD

    Appreciate help

  • March 4, 2012 at 1:16 pm

    #1454599

    Wouldn't a simple DISTINCT do the job?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply