This page is also available in: 
Deutsch (German)
Deutsch (German) 
This article was created with the help of a translation tool. Although we check the translation, errors in the content cannot be ruled out. Please bear this in mind when using the content. Thank you for your understanding.
As expected, the data for the individual services and functions is stored in databases in SFOS. Here you will find a list of the Sophos Firewall databases that I know of and instructions on how to read the data from the tables.
Status: 10.01.2025
Introduction
In my past projects, I have repeatedly encountered issues where it is necessary to access the database - be it for information purposes, to write scripts, or to correct errors that can no longer be corrected in the GUI. There are several examples. During my research on the Internet, I never really found a complete overview. One is curious. 😉
Using the command lines provided by the support team and a little digging through the files in the firewall's file system, I was able to gather some information about databases and tables. With the help of the tables I also wrote a script which makes a reference search for the created users and groups - unfortunately it is not possible in GUI until SFOS 21.0.0.
Thanks to our reader Markus (from the comments) I was able to complete the list and add more databases.
A big THANK YOU again! 😀
As the collection has now grown considerably, I have decided to update the article and present the information differently.
The following information refers to the version SFOS 21.0.0. Some tables may not exist in older versions.
What do you use the tables for? Feel free to write us your purposes or further insights in the comments!
Please note that improper use of SQL commands and changes in the databases can lead to errors or total failures of the firewall! Therefore, please use the information with the utmost care and always use a demo environment for testing! Use at your own risk.
I do not guarantee the completeness of the information.
What databases are there?
I know of a total of three databases so far. Due to the sheer number of tables, I will not list what is ultimately in which table. You will have to find out for yourself what information you are looking for. Many of the tables are also simply irrelevant for the possible purposes of end users and admins. In most cases, the names of the tables are descriptive enough to find out the purpose. But just have a look for yourself.
PostgreSQL "corporate
This database mainly contains information about the configuration of the firewall. So rules, users, objects, etc...
Tables
PostgreSQL database "corporate
tblaccesspoint
tblaccesspolicy
tblacldefinition
tblactive_threat_response
tblactivity
tblactivityrule
tblactivityrule_file
tblactivityrule_url
tblactivityrule_webcat
tbladmin_dsthost_rel
tbladmin_host_rel
tbladmin_offload_disable
tbladmin_service_access
tbladmin_service_rel
tbladsauthserver
tbladsdomainsearchqueryrel
tbladsserverdomainrel
tblalertconfigdetail
tblallow_domain
tblapiconfig
tblappbwrel
tblappclassification
tblappclassificationrel
tblappfilterpolicy
tblappfilterpolicydetail
tblapplianceproperties
tblapplication_pr_rel
tblapplicationobject
tblappstoeps
tblatpconfiguration
tblatphostexceptionrel
tblauthenticatedrelay
tblauthserverconfig
tblauthsettings
tblautopurge
tblavasconf
tblbackupmaildetail
tblbandwidthpolicy
tblbandwidthpolicydetail
tblbookmark
tblbookmarkdomains
tblbookmarkgroup
tblbookmarkgrouprelation
tblbridge_ethertype_rel
tblbridgedetail
tblbridgevlanfilter
tblbrstaticentry
tblbypassfirewall
tblcacheconfiguration
tblccl
tblccldescrel
tblcclnamerel
tblcclrawregexesrel
tblcclregexrel
tblcentralsynchronization
tblcertificatesan
tblchromebookssoconfig
tblclientlogintemplate
tblclientservices
tblconfiginfo
tblconfiguration
tblconnectwiseconfiguration
tblcrlinfo
tblcrtrafficnat
tblctacollector
tblcustomappcategory
tblcustomization_assets
tblcustomizeddeniedmsg
tblcvreportgroup
tblcvreportgrouprel
tbldashboarduser
tbldata_config
tbldataconfig
tbldatatransferpolicy
tbldatetimeconf
tbldb9users
tblddnsaccountdetail
tbldeanonymizationinfo
tbldecryptionprofilecipherrel
tbldecryptionprofiletlsoptions
tbldefaultdeniedmsg
tbldefaultinternetscheme
tbldefaultwebfilternotificationsettings
tbldelegatedconf
tbldevmapinfo
tbldgdconf
tbldhcpconf
tbldhcpconf_dynamiclease_rel
tbldhcpconf_staticlease_rel
tbldhcpoptions
tbldhcpoptionval
tbldhcprelay
tbldhcprelayiprel
tbldhcpv6clientconf
tbldkimsigning
tbldkimverification
tbldlp
tbldlpsignature
tbldnsconf
tbldnsrbl_zones
tbldnsrequestroute
tbldnsrequestrouteprop
tbldospolicy
tbldosrules
tbldpp
tbldppsignature
tbldstnet_pr_rel
tbleacappcache
tbleacapplications
tbleacendpoints
tbleacsearchcategories
tbledirauthserver
tblemailarchiver
tblemailuserlist
tblemailusersallowed
tblemailusersblocked
tblentitymonitor
tblepcattocatmapping
tblethtoolconf
tbleventnotification
tbleventnotificationgroup
tblexception_policy
tblexception_rcpt_addresses
tblexthreatfeedconfig
tblexthreatfeedpriority
tblfeedbackchannel
tblfiletype_extension
tblfiletype_mimeheader
tblfiletypecat
tblfirewallgroup
tblfirewallrule
tblfirewallscanrel
tblfirewalltelemetry
tblformtemplate_assets
tblfullbr_domains
tblfwdest
tblfwdest_exception
tblfwdestzone
tblfwdestzone_exception
tblfwexception
tblfwgroupdstrel
tblfwgrouprel
tblfwgroupsrcrel
tblfwservice
tblfwservice_exception
tblfwsource
tblfwsource_exception
tblfwsourcezone
tblfwsourcezone_exception
tblfwuser
tblgateway
tblgatewaymlm
tblgeneralrule
tblgeneralrulebypass
tblgeoip_codes
tblgreroute
tblgretunnel
tblgroupdetail
tblgwfoc
tblhaconfig
tblhaconfig_admin_ip_port_rel
tblhaencryption
tblhalbsconfig
tblhasetupdetail
tblhbcloudcredential
tblheartbeatzonerel
tblhost
tblhostgrouprel
tblhotspot
tblhotspotfirewallrel
tblhotspotsadvancedvoucher
tblhotspotslogincertrel
tblhotspotsvoucher
tblhotspotswalledrel
tblhotspotvoucherrel
tblhttpconfiguration
tblhttpproxysafeport
tblhttpsdecryptionprofile
tblicapprofile
tblicapserver
tblidpcustomsignature
tblidppolicy
tblidppolicydetail
tblidpssorolemapping
tblidpssoserver
tblimcontact
tblimcontactgrouprel
tblimloggingrule
tblimrule
tblinboundallowedhost
tblinboundblockedhost
tblinitstring
tblinterfacerel
tblinternetscheme
tblipaddresses
tbliptunnel
tbliviewbookmark
tbliviewbookmarkgroup
tblkernelmodules
tbll2tpconf
tbll2tpusers
tbllagdetail
tblletsencryptsettings
tbllist
tbllistdetail
tbllivesslvpnusers
tblloadbalancemethod_rel
tbllocalservicedetails
tbllocalzoneacl
tbllogtypeplugincall
tbllogtypes
tblmacs
tblmailproxyconf
tblmailschedule
tblmailsignature
tblmcastrouting
tblmcastroutingdetail
tblmesh_ap_rel
tblmessagecat
tblmessages
tblmigrated_policyroutes
tblmlmloadbalancemethods
tblmonitor
tblmonitordetail
tblmtalist
tblmtalistdetail
tblmtaspxconfigrel
tblmtaspxconfiguration
tblmtaspxtemplates
tblmultiuserhost
tblnatinboundinterfacerel
tblnatinterfacewiserel
tblnatoriginaldestrel
tblnatoriginalservicerel
tblnatoriginalsourcerel
tblnatoutboundinterfacerel
tblnatrule
tblnetflowserver
tblnetworkzone
tblnonstdportservice
tblotp
tblotpsettings
tbloutboundallowedhost
tbloutboundblockedhost
tbloverridepolicy
tblpimconfig
tblpimgroupinfo
tblpiminterface
tblpolicy
tblpolicyroute
tblpolicytestermethod
tblpolicytesterrel
tblpopimap_srcip
tblpopimaprule
tblpppoeconf
tblpptpconf
tblpptpusers
tblprivilegedusers
tblprohibitedcategories
tblprohibiteddomains
tblproxyneighbour
tblpuawhitelist
tblpubkeyauthconf
tblraclientconf
tblradiusauthserver
tblradiusclients
tblradiusserverattributes
tblradiusserverattrtypes
tblradvdconfig
tblradvdconfigprefixdetails
tblrealwebserver
tblred
tblred_interface_relation
tblreddevice
tblrequiredtlsdomain
tblrequiredtlshost
tblrole
tblrootcadetail
tblrootcainfo
tblroutingauth
tblroutinginfo
tblschedule
tblscheduledetail
tblscusertoken
tblsdwan_gw_rel
tblsdwanprofile
tblsecpolicyrulecclrel
tblsecpolicyruleuserrel
tblsecpolicywebcategoryitemrel
tblsecpolicywebcategoryitemrel_activity
tblsecpolicywebcategoryitemrel_file
tblsecpolicywebcategoryitemrel_url
tblsecpolicywebcategoryitemrel_webcat
tblsecpolicywebcategoryrel
tblsecuritypolicy
tblservice
tblservice_pr_rel
tblservicedetail
tblservicegrouprel
tblservicescanrel
tblskipquarantinereports
tblskiptlshost
tblsmarthostsetting
tblsmarthostsettingrel
tblsmsgwprofile
tblsmsgwreqrespparam
tblsmtpprofile
tblsmtpprofile_blockfiletype
tblsmtpprofile_domain_rel
tblsmtpprofile_rbl_rel
tblsmtpprofile_routing_rel
tblsmtpprofile_whitelisttype
tblsnmpagentconf
tblsnmpmanagerconf
tblsnmpv3trapiplist
tblspamadvancedrule
tblspamcheckexceptions_allowdomain
tblspamcheckexceptions_allowipfqdn
tblspamcheckexceptions_blockdomain
tblspampolicy
tblspamrule
tblspamruledestip
tblspamrulesrcip
tblsplitnetworks_relation
tblspoofing
tblspx_templates
tblspxconfigrel
tblspxconfiguration
tblsrcnet_pr_rel
tblsshpubkey
tblsslvpnclientconn
tblsslvpnconn_local_net_rel
tblsslvpnconn_remote_net_rel
tblsslvpnglobalconf
tblsslvpnparameters
tblsslvpnpolicy
tblsslvpnpolicyresourcerel_fullaccess
tblsslvpnpolicyresourcerel_webaccess
tblsslvpnsrvconn
tblstaticdnsentry
tblstaticdnsentryprop
tblstaticneighbourentry
tblsupportaccess
tblsyshostname
tblsyslogconfigure
tblsyslogrelation
tblsystemservice
tbltacacsauthserver
tblthreatexception
tbltlsrule
tbltlsrule_dest_network_rel
tbltlsrule_dest_zone_rel
tbltlsrule_services_rel
tbltlsrule_src_network_rel
tbltlsrule_src_user_rel
tbltlsrule_src_zone_rel
tbltlsrule_syncapp_rel
tbltlsrule_syncfilter_rel
tbltlsrule_websites_rel
tbltlssettings
tbltrustedentry
tbluicertificaterel
tblunicastrouting
tblup2dateclientconfig
tblurlgroup
tblurlgroupmembers
tblurlregexs
tblurlscheme
tblusbmodemconf
tbluser
tbluser_pr_rel
tbluseraccounting
tbluserapploginiprel
tblusergrouprel
tbluseriprel
tblusermacrel
tbluserrel
tbluserrolerelation
tblvdslconf
tblvirtualwebserver
tblviruspolicy
tblviruspolicyblockfiletype
tblviruspolicynotifyaction
tblviruspolicywhitelisttype
tblvoucher
tblvpnawsbgprel
tblvpnbackuplink
tblvpncertificate
tblvpncertificatesubaltname
tblvpnciscoconnection
tblvpnconndomaindetails
tblvpnconnection
tblvpnconnectiondetail
tblvpnconngroup
tblvpnconngroupdetails
tblvpnconnhostrel
tblvpnconnstatus
tblvpncsrdetail
tblvpnletsencryptcsrdetail
tblvpnpolicy
tblvpnproperties
tblvpnreservedip
tblvpnroutes
tblvpnsscertdetail
tblwaf_real_location_rel
tblwaf_virtual_location_rel
tblwafadvanceconfig
tblwafauth_profile
tblwafauth_profile_user_rel
tblwafblockcountries
tblwafexception
tblwafexception_path
tblwafexception_skip_custom_threat_cat_rel
tblwafexception_skip_threat_cat_rel
tblwafexception_source
tblwafexceptionrel
tblwaffilter
tblwafformtemplate
tblwaflocation
tblwaflocation_allowednet
tblwaflocation_deniednet
tblwafsecprofile_custom_threat_cat_rel
tblwafsecprofile_skiprules
tblwafsecprofile_threat_cat_rel
tblwafsecprofile_urls
tblwafsecurityprofile
tblwafslowhttpsettings
tblwafslowhttpskipnetworks
tblwaftlssetting
tblwebaccess_token
tblwebaccess_token_detail
tblwebcategory
tblwebcategorykeyworddetail
tblwebcategoryurldetails
tblwebclientportalsetting
tblwebexceptiondomainitems
tblwebexceptiondomainitems_dstip
tblwebexceptiondomainitems_srcip
tblwebexceptiondomainitems_urlregex
tblwebexceptiondomainitems_webcat
tblwebexceptions
tblwebfilterruledetails
tblwebserverdomain
tblwebtempaccess
tblwlanglobal
tblwlanroguescan
tblwlap
tblwlap_schedule_rel
tblwlap_wlnet_rel
tblwlglobalsettings
tblwlgroup
tblwlgroup_ap_rel
tblwlgroup_wlnet_rel
tblwlnet_schedule_rel
tblxauthusers
available_login_eventv6_1718817634
available_login_eventv6_1719915092
last_login_event
tbl_available_login_event
tbl_sar_dynamic_query
tbl_used_login_event
tblalertconfig
tblalertmsg
tblalertmsgdefaultparam
tblappcached_config
tblappliancespecificparam
tblapplicationcomponent
tblapplicationname
tblattributes
tblaudit
tblawscfg
tblcentralgroups
tblcentralmgmt
tblcfrstats
tblcipheralgorithms
tblcolumndetail
tblcompany
tblcontrolcenterpdf
tblcountrylonglatinfo
tblcrevent
tblctavpnnetwork
tbldashboardmenu
tbldatagrid
tbldatagridcolumn
tbldatalink
tbldatalink_version
tblddnsserviceprovider
tbldefaultconfigini18n
tblenckeydata
tblentity
tblentitygrouprelation
tblexecutivesummaryreport
tblexthreatfeedglobaldata
tblexthreatfeedstate
tblfwcmfrpcd
tblfwnewpolicy
tblgraph
tblgraphformat
tblhapairparams
tblhaparam
tblhastatus
tblhavmac
tblicmpcode
tblicmptype
tblimlogginglevel
tblimprotocol
tblinterface
tblipaddress
tblipprotocol
tbliviewconfig
tbliviewmenu
tbliviewmenuv1
tbllanguage
tbllocalservice
tbllocalservicetype
tbllogoutput
tbllogseverity
tbllogviewerfields
tbllogviewerfiltercondition
tbllogviewerfilterconfig
tbllogviewerfiltertype
tblmanage_fqdn_ip
tblmanagecolumn
tblmodemautodetail
tbloperation
tblprotocolgroup
tblprotocolidentifier
tblregistration
tblreport
tblreportcolumn
tblreportgroup
tblreportgroupquery
tblreportgroupqueryrel
tblreportgrouprel
tblreportquery
tblreportqueryconfig
tblreportqueryrel
tblreverse_proxy_data
tblsitetositeremoteroute
tblspamcheckexceptions_migrate
tblstatusmappings
tbltelemetry
tbltheme
tbltoptenapps
tbltrafficliveconnection
tblurlacl
tblvlantagrules
tblweekday
tblztnaconnector
localap_wlnet_rel
localap_wlnet_rel_1 Expand
You can also get the list using this command in the advanced shell:
Advanced Shell - Display of the "corporate" database tables
psql -U pgroot -d corporate -c '\dt'You can obtain the content of a single table as follows. Replace "tbluser" from the example with one of the tables listed above.
Advanced Shell - Listing of a PostgreSQL table
# Tabellenansicht
# Table view
psql -U pgroot -d corporate -c "select * from tbluser;"
# Listenansicht
# List view
psql -U pgroot -d corporate -c "select * from tbluser;" xPostgreSQL "iviewdb"
These databases mainly contain data on the reports and logs of the firewall. However, there are also tables with information on mail quarantine (tblquarantinemailmergev6), mail spool (tblmailspool), mail logs (tblmaillog) or the firewall log, for example.
Tables
The list of tables is so large (6434) that I will not list them here.
You can also get the list using this command in the advanced shell:
Advanced Shell - Display of the "iviewdb" database tables
psql -U nobody -d iviewdb -p 5433 -c '\dt'You can obtain the content of a single table as follows. Replace "tblquarantinemailmergev6" from the example with one of the tables listed above.
Advanced Shell - Listing of a PostgreSQL table
# Tabellenansicht
# Table view
psql -U nobody -d iviewdb -p 5433 -c "select * from tblquarantinemailmergev6;"
# Listenansicht
# List view
psql -U nobody -d iviewdb -p 5433 -c "select * from tblquarantinemailmergev6;" xSQLite
The SQLite database is the smallest database and is file-based. It contains, for example, information on the currently active users.
Tables
You can list the content as described below. As there are only a few tables, I will list the commands for all of them together.
Advanced Shell - SQLite tables
sqlite_client 127.0.0.1 6061 1 "select * from tblliveuser"
sqlite_client 127.0.0.1 6061 1 "select * from tblliveuser_otherattr"
sqlite_client 127.0.0.1 6061 1 "select * from tblliveuseraccounting"
sqlite_client 127.0.0.1 6061 1 "select * from tbladminaccess"
sqlite_client 127.0.0.1 6061 1 "select * from tblwifiliveconnections"I have not yet found a way to display the contents with column names. With this command you can list the column names and then have to set them manually against the contents.
Advanced Shell - SQLite table column names
sqlite_client 127.0.0.1 6061 1 "PRAGMA table_info(tblliveuser);"
Genial! So eine Übersicht habe ich schon länger sucht. Danke für die Auflistung.
Hallo Max,
sehr gerne!
Gruß,
Sebastian
Hallo Sebastian,
weisst du zufällig in welcher Datenbank bzw. Tabelle die STAS LiveUser gespeichert werden?
Leider kann man über die API immer nur 50 abrufen.
Ich habe dank deiner Tabellenübersicht leider nur die tbllivesslvpnusers gefunden.
Danke dir für die tolle Arbeit!
Markus
Hallo Markus,
nein, eine Tabelle ist mir dazu nicht bekannt. Sorry. Adhoc fällt mir auch kein anderer Weg ein.
Mit welchem Aufruf holst du die Infos über die API?
Gruß,
Sebastian
userpassword
Die 50 sind hartcodiert, dass hat Sophos leider inzwischen bestätigt und man kann auch keine Filter anwenden, dass ist ein known issue 🙁
Hallo Markus,
OK, das ist natürlich ärgerlich. Aber eie weitere Idee hab ich dann auch nicht mehr. Sorry.
Gruß,
Sebastian
sqlite_client 127.0.0.1 6061 1 "select * from tblliveuser"Danke für die Info! Scheinbar gibt es noch eine zweite Datenbank mit SQLite. War mir garnicht bewusst bisher. Es gibt hier auch nicht viele Tabellen wie es aussieht. Diese hier habe ich gefunden:
tblliveuser
tblliveuser_otherattr
tblliveuseraccounting
tbladminaccess
tblwifiliveconnections
Mit diesem Befehl lässt sich herausfinden was die Spaltennamen sind:
sqlite_client 127.0.0.1 6061 1 "PRAGMA table_info(tblliveuser);"Hallo Sebastian,
es gibt insgesamt sogar 3 Datenbanken.
Die Tabellen der 3. und größten DB kannst du abfragen mit:
psql -U nobody -d iviewdb -p 5433 -c '\dt'Hilfreich ist hier zB die Tabelle tblquarantinemailmergev6 um Mails in der Quarantäne abfragen zu können.
GrußMarkus
Hallo Markus,
wow, danke für den Hinweis!
Die DB hats mal echt in sich. Ich passe gleich mal den Artikel an.
Viele Grüße,
Sebastian