Page 1 of 1

SQL Schema Documentation

Posted: Tue Sep 10, 2019 1:55 pm
by DomoB90
Hi there,

I contacted support because we are looking to purchase this plugin but needed some information first. We are looking to create automated reports of the browsing history for each client by exporting to a .csv for Client, Location, Computer. I was told this is possible by creating a script.

"The process would be,

Create a script that queries the plugins_sw_surflog_xxx tables in Automate and writes that data out to a file.

That is the simple answer, actually writing the script will require a little bit more tech knowhow. The Automate script will need to craft SQL queries to get the data you want by client and or agent id, loop through each row and add that rows data in CSV format to a text file, afterwards present file(s) to you in some manner. Then assign script to run once a month to each client you want to do this for.

The data is there and available in Automate for you to use as you see fit." - Shannon Anderson

My next question to that response was if there is documentation on the SQL Schema for the plugin and I was told to go to the forums and I could get answers to my questions. Again, we haven't purchased this plugin yet as we want to see if it will suit our purposes and if there would be necessary documentation to back it up so our techs can easily create queries when needed.

Thanks for the help!

-Dominick

Re: SQL Schema Documentation

Posted: Wed Sep 11, 2019 1:35 pm
by Cubert
Here is some info on the SQL setups for Surflog.
SL-SQL-tab1.PNG
SL-SQL-tab1.PNG (9.41 KiB) Viewed 8307 times
SL-SQL-tab2.PNG
SL-SQL-tab2.PNG (6.26 KiB) Viewed 8307 times
There are 3 main tables, 2 are for configuration and exclusions during automation and the logs table contains all data for each agent.

To request data about an agent use the line below changing out the ID numbers. Remove the ComputerID = clause completely to get all agent data for a given client.

Code: Select all

SELECT * FROM plugin_sw_surflog_logs WHERE ClientID = 1 and ComputerID = 18;

When using Automate scripting replace the ClientID and or ComputerID with %clientid% and %computerid% as these are global variables used in Automate scripting to denote the agent or client the script is running on currently.

Code: Select all

SELECT URL, Title, VisitTime FROM plugin_sw_surflog_logs WHERE ClientID = '%clientid%' and ComputerID = '%computerid%';
Running the following SQL query in Automate Scripting using the get dataset function will present you with a total row count value and for each row called you will have the following string values available to script.

@sqlURL@
@sqlTitle@
@sqlVisitTime@


You then call write to file function and pass it @sqlURL@,@sqlTitle@,@sqlVisitTime@ as you loop through each row until max row count is reached.
You will now have a CSV file of the data you seek by client/agent IDwhen ever the script is run. Schedule script to run monthly and you will have a monthly csv of that data.

Re: SQL Schema Documentation

Posted: Wed Sep 11, 2019 1:50 pm
by DomoB90
Thank you! That’s very helpful. I can see that my team will be able to request help from this forum if needed and you all are very responsive. Much appreciated!

Best,
Dominick