Get The Scoop On What's New
Want to know what Plugins4Automate is doing for ConnectWise Automate? Come check out our blog and find out about new products and updates that we are working on! https://www.plugins4labtech.com/blogs/blog

SQL Schema Documentation

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

SQL Schema Documentation

Post by DomoB90 » Tue Sep 10, 2019 1:55 pm

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: 1251
Joined: Tue Dec 29, 2015 7:57 pm
Contact:

Re: SQL Schema Documentation

Post by Cubert » Wed Sep 11, 2019 1:35 pm

Here is some info on the SQL setups for Surflog.
SL-SQL-tab1.PNG
SL-SQL-tab1.PNG (9.41 KiB) Viewed 61 times
SL-SQL-tab2.PNG
SL-SQL-tab2.PNG (6.26 KiB) Viewed 61 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: 5
Joined: Mon Sep 09, 2019 8:19 pm

Re: SQL Schema Documentation

Post by DomoB90 » Wed Sep 11, 2019 1:50 pm

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

Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests