SQL Schema Documentation

This forum is retired in support of the new SurfLog for Automate plugin Forum. If you have not updated to Build 2.0.0.+ and are a current subscriber, you can log into Plugins4Automate.com and cancel the current subscription and then purchase the new version.

Forum to support the operation and usage of SurfLog plugin for LabTech
Locked
DomoB90
Posts: 20
Joined: Mon Sep 09, 2019 8:19 pm
4

SQL Schema Documentation

Post 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

User avatar
Cubert
Posts: 2430
Joined: Tue Dec 29, 2015 7:57 pm
8
Contact:

Re: SQL Schema Documentation

Post by Cubert »

Here is some info on the SQL setups for Surflog.
SL-SQL-tab1.PNG
SL-SQL-tab1.PNG (9.41 KiB) Viewed 8310 times
SL-SQL-tab2.PNG
SL-SQL-tab2.PNG (6.26 KiB) Viewed 8310 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.

DomoB90
Posts: 20
Joined: Mon Sep 09, 2019 8:19 pm
4

Re: SQL Schema Documentation

Post 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

Locked

Return to “SurfLog Browsing Metrics for Labtech”