Misc SQL scripts and snippets for ConnectWise Automate (formerly known as LabTech)
- danger folder contains sql that changes the database, use at your own risk
- tested queries on LabTech 11.0, cleaned them up a bit and removed one that doesn't work
- added two new scripts for pulling groups with exclusions and updating auto join searches on groups
An attempt at describing the purpose of each table in LabTech.
| Table Name | Description |
|---|---|
| aditionalschedules | |
| advancedconfig | |
| agentalerts | |
| agentcommands | |
| agentcomputerdata | |
| agentcomputers | |
| agentdefaults | |
| agentignore | |
| agents | Monitor data, can be related to groupmonitors and groupdmonitors. The DriveID field on this table is a collection of group ids that are using the monitor. |
| agenttrendingdata | |
| alerts | |
| alerttemplate | Alert templates. Dashboard -> Management -> Alert Templates |
| alerttemplatemaintenance | |
| alerttemplates | Alert template actions. Can be viewed in the table below a selected alert template. |
| antivirusconfigurationlookup | |
| antivirusconfigurationsettings | |
| antivirusstatuslookup | |
| antivirusstatusvalues | |
| antivirustemplatepolicy | |
| antivirustemplatepolicydata | |
| antivirusthreatactiontakenlookup | |
| antivirusthreatlevellookup | |
| antivirusthreatlookup | |
| antivirusthreats | |
| antivirusthreattargetlookup | |
| antivirusthreattypelookup | |
| applicationblacklist | |
| applicationwhitelist | |
| asp_connectionpool | |
| auditactions | |
| auditactions_plugins | |
| autoagentsoverrides | |
| autofixignore | |
| autogeneratedagents | |
| autoservicesblacklist | |
| autostartup | |
| autostartupdefs | |
| availableapps | |
| backupdestinations | |
| backupjobs | |
| backuplogs | |
| backupstatus | |
| cachecontrol | |
| certificatestore | |
| chattranscript | |
| cim_formfactor | |
| cim_hv_classes | |
| cim_hv_data | |
| cim_hv_element | |
| cim_hv_properties | |
| cim_hv_property | |
| cim_memorytype | |
| cim_processorfamily | |
| cim_sensortype | |
| clienthistory | |
| clients | |
| commandfolders | |
| commands | |
| communityupdatelog | |
| computercollectedsnmp | |
| computerconfig | |
| computerconfigproperties | |
| computermenus | |
| computerroledefinitions | |
| computers | |
| config | |
| configbackup | |
| configoutlook | |
| contactcomputers | |
| contactpwtoken | |
| contacts | |
| contactsessions | |
| controlcenterconfig | |
| customcommands | |
| custommenus | |
| databasealerts | |
| databasealertsignored | |
| databasefails | Failed internal monitors. |
| datacache | |
| datacollectors | |
| dataviewfolders | |
| dataviews | |
| dbaseagentcontrol | |
| dbaseagentstats | |
| defragmentation | |
| dependancy | |
| devicelibrary | |
| diskcleanup | |
| documents | |
| drives | |
| dynamicclients | |
| errmsgs | |
| errorlogs | |
| eventblacklist | |
| eventlogs | |
| extrafield | Extra data fields. |
| extrafielddata | Data for extra data fields. |
| failedemails | |
| filelibrary | |
| founddevice | |
| functions | |
| groupagents | Remote monitors. |
| groupagentscontrol | |
| groupagentscontroldevices | |
| groupbackupjobs | |
| groupdagents | Internal monitors. |
| groupmonitorcontrol | |
| groupmonitors | |
| groupscripts | Contains scripts scheduled at the group level. |
| h_agentdata | |
| h_agentdatadaily | |
| h_agentdatamonthly | |
| h_agentdataweekly | |
| h_agentdatayearly | |
| h_agenthistory | |
| h_agents | All agent history, also used to get failed remote monitors. |
| h_agentsignup | |
| h_apps | |
| h_clientscript | |
| h_clientstats | |
| h_clientstatsdaily | |
| h_clientstatsmonthly | |
| h_clientstatsweekly | |
| h_clientstatsyearly | |
| h_commands | |
| h_computers | |
| h_computerstats | |
| h_computerstatsdaily | |
| h_computerstatshourly | |
| h_computerstatsmonthly | |
| h_computerstatsweekly | |
| h_computerstatsyearly | |
| h_drives | |
| h_drivestats | |
| h_drivestatsdaily | |
| h_drivestatsmonthly | |
| h_drivestatsweekly | |
| h_drivestatsyearly | |
| h_eventlogs | |
| h_extrastats | |
| h_extrastatsdaily | |
| h_extrastatsmonthly | |
| h_extrastatsweekly | |
| h_extrastatsyearly | |
| h_labtech | |
| h_locationstats | |
| h_mobilecommands | |
| h_mobiledata | |
| h_mobiledata_daily | |
| h_mobilelocation | |
| h_patching | |
| h_pendingscripts | |
| h_probecollectedsnmp | |
| h_probecommands | |
| h_probehistory | |
| h_probeinternal_datainterfaces | |
| h_probeinternal_hcsampledinterface | |
| h_probeinternal_hostedresources | |
| h_probeinternal_ifmib | |
| h_probeinternal_printererrors | |
| h_probeinternal_printermarkers | |
| h_probeinternal_printermarkersupplies | |
| h_probeinternal_sampledinterface | |
| h_probeinternal_storageareas | |
| h_probeinternal_systeminfo | |
| h_processes | |
| h_redirector | |
| h_script | |
| h_scripts | |
| h_stats | |
| h_systemtimings | |
| h_users | |
| heartbeatcomputers | |
| hotfix | |
| hotfixdata | |
| hotfixgroups | |
| hotfixscripts | |
| hudmain | |
| huduser | |
| hvabstract | |
| hvcounter | |
| hvcpu | |
| hvdevices | |
| hvhconfig | |
| hvhost | |
| hvmconfig | |
| hvmemory | |
| hvnetwork | |
| hvpci | |
| hvresourcepool | |
| hvstorage | |
| hvstoragedisk | |
| hvstoragetype | |
| hvswitch | |
| hvsysinfo | |
| hypervisor | |
| ignite_propertyinfo | |
| ignite_propertytypes | |
| igniteguids | |
| igniteserviceplantypes | |
| infocategory | Ticket Categories for monitors. Dashboard -> Config -> Configurations -> Information Base Categories |
| informationbase | |
| inv_bios | |
| inv_chassis | |
| inv_devices | |
| inv_memoryslots | |
| inv_networkcard | |
| inv_operatingsystem | |
| inv_probefansensor | |
| inv_processor | |
| inv_systemslots | |
| inv_videocard | |
| iosdevices | |
| iospushcommands | |
| ipcountry | |
| ipprotocol | |
| ipservices | |
| l_mobileandroidperms | |
| labtechlogmessages | |
| languagepacks | |
| licenseavailability | |
| licensemanagement | |
| licensetypelookup | |
| licenseusage | |
| links | |
| localizationresources | |
| localizations | |
| locationbackupjobs | |
| locationbookmarks | |
| locations | |
| lookupdevicecode | |
| lookupiananumbers | |
| lt_languages | |
| lt_loadedoids | |
| lt_news | |
| lt_newsusers | |
| lt_scriptdebugging | |
| lt_scripts | |
| lt_solutions | |
| ltantivirusthreats | |
| mactable | |
| maintenancemode | |
| maintenancemodealerts | |
| maintenancewindow | Contains maintenance windows. Fancy eh? |
| maintenancewindows | |
| managementreport | |
| managementscore | |
| mastergroups | Contains all groups in LabTech. The 'master' portion of the name is not related to the group type; that can be found on mastergroups.GroupType |
| mngd_svc_defs | |
| mngd_svc_services | |
| mngd_svc_servicexrefs | |
| mobileandroidapplookup | |
| mobileandroidappperms | |
| mobileandroidinstalledapps | |
| mobileandroidperms | |
| mobilecommands | |
| mobilecommandslookup | |
| mobileconfig | |
| mobileconfigclass | |
| mobileconfigclassemail | |
| mobileconfigclassexchange | |
| mobileconfigclassldap | |
| mobileconfigclasspasscode | |
| mobileconfigclassrestrictions | |
| mobileconfigclassscep | |
| mobileconfigclassvpn | |
| mobileconfigclasswifi | |
| mobiledeviceinfo | |
| mobiledevices | |
| mobileiosapplookup | |
| mobileioscertificates | |
| mobileiosinstalledapps | |
| mobileiosinstalledprofiles | |
| mobileiosprofilecontent | |
| mobileiosprovisioningprofiles | |
| mobileiosrestrictions | |
| mobilelabtechusers | |
| mobilelocation | |
| mobilemastergroups | |
| mobilemeldedandroidprofile | |
| mobilemeldediosprofile | |
| mobilenetworkinterfaces | |
| mobilepermissions | |
| mobileprocesses | |
| mobileretiredassets | |
| mobilerunningconfig | |
| mobileschedules | |
| mobilesubgroups | |
| mobilesystemlogs | |
| mobiletemplates | |
| monitorcategories | |
| monitortemplate | |
| monitortemplates | |
| msg_messages | |
| msg_questions | |
| msg_responses | |
| network | |
| networkdevices | |
| networkdrives | |
| opportunities | |
| orderparts | |
| outgoingemailattachments | |
| outgoingemails | |
| outgoingsms | |
| passwords | |
| pendingscripts | |
| permissions | |
| plugin_cw_agreements | |
| plugin_cw_agreementtypes | |
| plugin_cw_allcwcompanies | |
| plugin_cw_assettemplateassignments | |
| plugin_cw_assettemplates | |
| plugin_cw_categories | |
| plugin_cw_categorymapping | |
| plugin_cw_clientmapping | |
| plugin_cw_communicationstats | |
| plugin_cw_configquestionpossibleresponses | |
| plugin_cw_configurationtypequestions | |
| plugin_cw_configurationtypes | |
| plugin_cw_contactmapping | |
| plugin_cw_cwstatusmapping | |
| plugin_cw_cwtolabtechservicepriorityxrefs | |
| plugin_cw_deviceapifieldmapping | |
| plugin_cw_devicemappingoverrides | |
| plugin_cw_groupmapoverride | |
| plugin_cw_ignoredentities | |
| plugin_cw_infomessages | |
| plugin_cw_labtechproductmap | |
| plugin_cw_labtechproducttypes | |
| plugin_cw_lastassetsync | |
| plugin_cw_ltproduct_agreementtype | |
| plugin_cw_ltstatusmapping | |
| plugin_cw_managedservicetemplateassignments | |
| plugin_cw_managedservicetemplates | |
| plugin_cw_masterproducts | |
| plugin_cw_members | |
| plugin_cw_possibilityproductmapping | |
| plugin_cw_printercounts | |
| plugin_cw_productclasses | |
| plugin_cw_producttypes | |
| plugin_cw_sectionlookup | |
| plugin_cw_serviceboards | ConnectWise plugin service board definitions |
| plugin_cw_serviceitemrelations | |
| plugin_cw_serviceitems | ConnectWise plugin service board service item definitions |
| plugin_cw_servicepriorities | ConnectWise plugin service board priority definitions |
| plugin_cw_servicepriorityxrefs | |
| plugin_cw_servicestatuses | |
| plugin_cw_servicesubtyperelations | |
| plugin_cw_servicesubtypes | ConnectWise plugin service board service subtype definitions |
| plugin_cw_servicetypes | ConnectWise plugin service board service type definitions |
| plugin_cw_settings | |
| plugin_cw_sitemapping | |
| plugin_cw_streamlinecollectionmapping | |
| plugin_cw_subcategories | |
| plugin_cw_ticket_failures | |
| plugin_cw_worktypes | ConnectWise plugin work type definitions |
| plugin_sap_activities | |
| plugin_sap_activityresults | |
| plugin_sap_applieddeductions | |
| plugin_sap_appliedhealthchecks | |
| plugin_sap_appliedparameters | |
| plugin_sap_appliedstandards | |
| plugin_sap_deductionstemplate | |
| plugin_sap_failedstandards | |
| plugin_sap_healthcheckdeductions | |
| plugin_sap_healthcheckoverrides | |
| plugin_sap_healthcheckparameters | |
| plugin_sap_healthchecks | |
| plugin_sap_healthdeductionoverrides | |
| plugin_sap_healthparameteroverrides | |
| plugin_sap_healthresults | |
| plugin_sap_lookupcategories | |
| plugin_sap_properties | |
| plugin_sap_reportcategoryresults | |
| plugin_sap_reportcheckresults | |
| plugin_sap_snapshotcomputerstats | |
| plugin_sap_snapshotpatchstats | |
| plugin_sap_standardoverrides | |
| plugin_sap_standards | |
| plugin_sap_summarydata | |
| plugin_screenconnect_config | |
| plugin_screenconnect_connectionevents | |
| plugin_screenconnect_entitysettings | |
| plugin_screenconnect_pluginproperties | |
| plugin_screenconnect_scinstalled | |
| plugin_screenconnect_serverinstallupdates | |
| plugin_screenconnect_settings | |
| plugin_webroot_clientsettings | |
| plugin_webroot_computerdata | |
| plugin_webroot_computersettings | |
| plugin_webroot_locationsettings | |
| plugin_webroot_threats | |
| pluginalerts | |
| plugincalls | |
| plugins | |
| possibilities | |
| printers | |
| probe_events | |
| probe_receivedsnmptraps | |
| probealerts | |
| probecollectedsnmp | |
| probecollectionrules | |
| probecollectiontemplates | |
| probecommands | |
| probeconfig | |
| probedetectionrules | |
| probedetectiontemplates | |
| probeinternal_datainterfaces | |
| probeinternal_hcsampledinterface | |
| probeinternal_hostedresources | |
| probeinternal_ifmib | |
| probeinternal_interfacedelta | |
| probeinternal_pingresults | |
| probeinternal_printererrors | |
| probeinternal_printermarkers | |
| probeinternal_printermarkersupplies | |
| probeinternal_sampledinterface | |
| probeinternal_storageareas | |
| probeinternal_systeminfo | |
| proberemotecommands | |
| probewalkresults | |
| processes | |
| processlist | |
| productkeys | |
| products | |
| projects | |
| properties | |
| quickconnect | |
| quotes | |
| redirectionapps | |
| redirectorports | |
| redirectors | |
| regions | |
| remotecommands | |
| remotepluginschedule | |
| reportcategory | |
| reportfilters | |
| reportfolders | |
| reports | |
| reportscheduler | |
| reporttemplates | |
| reporttemplatetypes | |
| retiredassets | |
| roledefinitions | |
| roleremoteplugin | |
| routers | |
| runningscripts | Scripts queued into the script engine. If running=1, the script is running. If running=0, the script is Waiting or Pending, depending on if the target machine is online or offline. |
| scheduledscripts | |
| scheduledtasks | |
| schedules | |
| scriptbreakpoints | |
| scriptdebugginginformation | |
| scriptfolders | |
| scriptlets | |
| scripts | |
| scriptstate | |
| scriptstats | |
| scriptsteps | |
| scripttime | |
| searches | |
| searchfolders | |
| sensorchecks | Contains searches and their SQL. |
| serverfiles | |
| services | |
| signupdata | |
| smartattributenames | |
| smartattributes | |
| snmpchecks | |
| snmptraps | |
| software | Computer screen -> Software tab |
| sphistory | ShadowProtect integration (LTBackup Tab on Computer screen) job history |
| spjobs | ShadowProtect integration (LTBackup Tab on Computer screen) job definitions |
| statusitems | |
| subgroups | Contains the members of groups. |
| subgroupscontacts | |
| subgroupsnetworkdevices | |
| subgroupwchildren | |
| subgroupwchildrencontacts | |
| subgroupwchildrennetworkdevices | |
| syslogtraps | |
| templateavailableproperties | |
| templateproperties | |
| templates | Navigation Tree -> Admin -> Templates |
| ticketdata | |
| ticketdatatypes | |
| ticketpolicyemails | |
| ticketpriority | |
| tickets | |
| ticketstatus | |
| timecategory | |
| timers | |
| timeslips | |
| trackers | |
| userclasses | |
| userfolders | |
| userproperties | |
| users | |
| users_extend | |
| usersec | |
| util_ints | |
| v_alerts | |
| v_androidappstub | |
| v_autostartup | |
| v_cim_hvsensor | |
| v_clientlocationstats | |
| v_clients | |
| v_commandhistory | |
| v_computeralertcounts | |
| v_computerhistory | |
| v_computers | |
| v_defragmentation | |
| v_detectedroles | |
| v_drivesinternal | |
| v_extradataclients | |
| v_extradatacomputers | |
| v_extradatagroups | |
| v_extradatalocations | |
| v_extradatanetdevice | |
| v_extradataprobe | |
| v_extradatatickets | |
| v_hotfixes | |
| v_hvcimdata | |
| v_hvhmemconsumed | |
| v_hvhmemgranted | |
| v_hvhmonitormemory | |
| v_hvhost | |
| v_hvmachine | |
| v_hvmdisk | |
| v_hvmmemconsumed | |
| v_hvmmemgranted | |
| v_hvmmonitormemory | |
| v_internalmonitors | |
| v_iosappstub | |
| v_licensemanagement | |
| v_localip | |
| v_locationobjects | |
| v_locations | |
| v_ltcr_assets | |
| v_ltcr_avcomputers | |
| v_ltcr_avpolicy | |
| v_ltcr_avthreatevents | |
| v_ltcr_avthreats | |
| v_ltcr_backuplogs | |
| v_ltcr_changeaudit | |
| v_ltcr_h_extrastats | |
| v_ltcr_memoryslots | |
| v_ltcr_mobiledataplan | |
| v_memoryboards | |
| v_mngd_svc_clients | |
| v_mngd_svc_computers | |
| v_mngd_svc_contacts | |
| v_mngd_svc_locations | |
| v_mngd_svc_networkdevices | |
| v_mobilecommandstub | |
| v_mobiledata_billingdates | |
| v_mobiledata_dataplan | |
| v_mobiledevices | |
| v_mobilegroupdata | |
| v_mobileinformation | |
| v_networkadapters | |
| v_networkmonitors | |
| v_parallelports | |
| v_patch | |
| v_patches | |
| v_patchhistory | |
| v_powerlevel | |
| v_printers | |
| v_processes | |
| v_processhistory | |
| v_processors | |
| v_productkeys | |
| v_remotecontrols | |
| v_reportstats | |
| v_reportstatscom | |
| v_reportstatsloc | |
| v_scheduledcommands | |
| v_scheduledscripts | |
| v_scsicontrollers | |
| v_serialports | |
| v_serverfreehd | |
| v_serverroles | |
| v_services | |
| v_smartattributes | |
| v_software | |
| v_softwarehistory | |
| v_soundcards | |
| v_stats | |
| v_systemalerts | |
| v_systemmonitors | |
| v_tapedrives | |
| v_tickets | |
| v_time | |
| v_ups | |
| v_usageperiods | |
| v_usbcontrollers | |
| v_users | |
| v_videocards | |
| v_xr_clientosgraph | |
| v_xr_clients | |
| v_xr_clientsummary | |
| v_xr_computers | |
| vendorproducts | |
| vendors | |
| virusscanners | |
| vmcounter | |
| vxr_clientavscanners | |
| vxr_clientpatchscoretop | |
| vxr_clientservices | |
| vxr_clientticketstats | |
| vxr_computercommands | |
| vxr_computermonitors | |
| vxr_computerpatchscores | |
| vxr_drives | |
| vxr_drivestatsweekly | |
| vxr_hardware | |
| vxr_healthcheck | |
| vxr_hotfixes | |
| vxr_hotfixstats | |
| vxr_licenses | |
| vxr_locationstats | |
| vxr_memoryboards | |
| vxr_mobiledevices | |
| vxr_networkdevices | |
| vxr_patchweights | |
| vxr_serverresources | |
| vxr_serverroles | |
| vxr_softwarestats | |
| vxr_ticketdata | |
| vxr_tickets | |
| vxr_ticketstats | |
| vxr_timeslips | |
| webcontrolcenteroptions | |
| webdashboards |