3 Replies Latest reply on Aug 22, 2013 1:38 PM by sroering

    Web Gateway - Content Security Reporter

    hudsy

      Hello,

       

      I am not sure if this is the correct area to put this but it seems that there is no other areas for CSR.

       

      I am trying to run a query that will simply list all of the usernames that have accessed my web gateway and been logged.  I am on version 1.0 of CSR and there is no 'unique' checkboxes to get a simple list.  If I run a query for usernames it will produce a huge list of usernames with each one repeated for each row that was logged in the database.

       

      Is there a way to modify the underlying SQL query to add in the "is unique' syntax?

       

      Thanks

        • 1. Re: Web Gateway - Content Security Reporter
          nate.hall

          I'm assuming you are running the query from ePO? I have something similar setup like this:

           

          Create a new web query - choose CSR for the database and select web summary

           

          CSR1.jpg

          Click Next. Choose a bar graph and be sure the labels are selected to be username:

           

          CSR2.jpg

           

          Click Next - I didnt change any of the columns on the 3rd step.

           

          Click Next again - On step 4 all I did was change the log source to be a particular MWG I wanted to run the report on:

           

          CSR3.jpg

           

          You should end up a report similar to this:

           

          CSR4.jpg

           

           

          Hopefully this is what you were looking for. Obviously you can tweak the report to better suit your needs but this should give you the general information you need. Also if you click options in the top right corner of the above screenshot you can export the data in a CSV.

          • 2. Re: Web Gateway - Content Security Reporter
            hudsy

            Yes, I have been there however the limit to the number of bar graphs in this example is 200 and that is the maximum that it will return.  If I do a table there is no way to get it to calculate a single field so that the username is unique (only shown once).

             

            What I ended up doing was going directly to the SQL server and running a SQL query on the username table and returned all the rows and then copy/pasted this into an excel spreadsheet to get the user list.

            • 3. Re: Web Gateway - Content Security Reporter
              sroering

              Also, reports are only going to show users that have data.  If you want a list of all users to pass through the system, then you need to query the DB directly, but this isn't really a report.

               

              1) Go to Menu > Configuration > Report Server Settings

              2) Click on Database from the left menu, and click edit in the lower-right corner

              3) Click "Execute SQL" button and run this query.

               

              Select user_name from csr_dim_user order by user_name asc