Sqlcmd commands for accessing the Sophos core database and extracting information

  • Article ID: 34657
  • Rating:
  • 4 customers rated this article 2.3 out of 6
  • Updated: 08 Jan 2014

This article provides a list of useful Structured Query Language (SQL) commands that can be used with the Sophos core database on the command line.

Note:

  • We recommend you make a backup of the database using either DataBackupRestore.exe or BackupDB.bat before running any SQL command.
  • This article is provided as is and for information only.
  • If you experience a problem connecting to your SQL Server consult Microsoft SQL Server documentation.
  • For extracting information from the console we recommend using the Sophos Reporting Interface (SRI).  See article 112873 for further details.

Applies to the following Sophos product(s) and version(s)

Not product specific

Contents

  1. OSQL vs. SQLCMD
  2. Accessing a SQL Server
    1. Explanation of parameters
    2. Example usage
  3. Find the SQL Server version
  4. List databases in a SQL Server instance
  5. Backing up and restoring a database
    1. Backing up a database
    2. Close connections to a database
    3. Restoring a database
    4. Restoring a database to a different folder
    5. Allow connections to a restored database
  6. Dropping (deleting) a database
  7. Attaching a database
  8. Commands to extract database information
    1. Extract a list of computers that are not reporting to the console
    2. Extract a list of computers that are pending a reboot
    3. Extract a list of computers that are shown as disconnected in the console
    4. Extract a list of computers (IP addresses and hostnames) that are unmanaged
    5. Extract a list of computers running a version of SAV9.x
    6. Extract a list of computer where the on-access scanner is inactive
    7. Extract the computer name, HIPS detection and file path for a computer
    8. Extracting information regarding Sophos Patch
    9. Output the error ID based on a computer name
    10. Output the entire contents of a table
  9. Commands to update database information
    1. Use PurgeDB.exe
  10. Troubleshooting common errors and problems

OSQL vs SQLCMD

Always use sqlcmd for SQL Server 2005 or higher.

Sqlcmd has all the features which osql has to offer, additionally sqlcmd has many added feature over osql. Osql was introduced in SQL Server 2000 version. Sqlcmd was added in SQL Server 2005 and offers additionally functionality which SQL Server 2005 provides.  Sqlcmd is also available in SQL Server 2008 and higher.

Accessing a SQL Server

To access a SQL Server instance from the command prompt you need to call the program (sqlcmd) and specify a series of parameters.  Some examples are shown below.

Note: All parameters are case sensitive.

Explanation of parameters

Parameter Description
-E The connection to the SQL Server is trusted (i.e., using Windows authentication).  If you don't use -E you have to use -U and specify a SQL user account your database admin has created.
-S Specifies the server name and instance name to connect to.  To connect to the local server you can rename the server name with a dot (.).  The default SQL Server instance name for our installers is 'sophos'. Example: servername\instancename, .\sophos
-d Specifies the database to connect to insider the SQL Server instance (one SQL Server instance can host multiple databases).  If you connect to a SQL Server instance but do not specify a particular database you have to type:
use <databaseName>
go

...before you can run commands against the database.  Hence it is simpler to specify the '-d' parameter when initially accessing the SQL Server. 
-Q Specifies the actual SQL query that will be executed against the database.  The SQL query is enclosed in double quotes.

Example usage

Command Description
sqlcmd -E -S .\sophos Access a local SQL Server instance called 'sophos' but do not connect to a particular database.
sqlcmd -E -S myServer\sophos Access a SQL Server instance called 'sophos' located on a computer called 'myServer'.  Note: Though you can access a SQL Server instance from another computer both computer must have Microsoft SQL Server installed - i.e., if you where you type sqlcmd into a command prompt on an ordinary endpoint computer the command would not be recognized as the sqlcmd.exe binary would not be found.
sqlcmd -E -S .\sophos -d sophos52
Access a local SQL Server instance called 'sophos' and connect to the sophos52 database hosted inside the instance. For a list of console versions and associated database names see article 17323.
sqlcmd -E -d sophos52 Access the default SQL Server instance on the local computer (not available with SQL Server Express) and connect to the sophos52 database.

Find the SQL Server version

sqlcmd -E -S .\sophos -Q "select @@version"

For more information see: How to determine the version and edition of SQL Server and its components

List databases in a SQL Server instance

The full command from outside of the SQL Server instance (i.e., you have not yet connected to the SQL Server) is:

sqlcmd -E -S .\sophos -Q "select name from sysdatabases"

If you have already connected to the SQL Server you can enter:

select name from sysdatabases
go

In either case a list of all databases that are attached (the SQL Server instance knows about) is shown.

Note: Though a database may be attached to a SQL Server instance and hence returned in the command this does not imply that the physical files are still available on disk or that the database is accessible.

Backing up and restoring a database

Backing up a database

We recommend you make a backup of the database using either DataBackupRestore.exe or BackupDB.bat but an alternative is to run the following command:

sqlcmd -E -S .\sophos -Q "backup database sophos52 to disk = 'C:\Windows\Temp\sophos52db.bak'"

In the example above the 'sophos52' database is backed up to the file sophos52db.bak in the Windows\Temp\ folder.

Note: Backing up a database to a folder other than \Windows\Temp\ can fail due to permissions.  It is therefore recommended you backup to the \Windows\Temp\ folder and then copy the file from there to a secure location.

Close connections to a database

If you want to forcibly close all open connections to the database use:

sqlcmd -E -S .\sophos -d "master" -n -b -Q "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = sophos52) BEGIN ALTER DATABASE sophos52 SET OFFLINE WITH ROLLBACK AFTER 5 END"

Restoring a database

To restore a database run:

sqlcmd -E -S .\sophos -d "master" -n -b -Q "RESTORE DATABASE sophos52 FROM DISK = C:\sophos52db.bak WITH REPLACE, RECOVERY"

In the example above the 'sophos52' database is restored from the backed up copy located in the file C:\sophos52db.bak

Restoring a database to a different folder

If you are restoring a database to a different folder path from the one the original database was backed up from (e.g., you original database was on the C:\ drive and you are restoring to a folder on another drive such as E:\) you will need to specify the path to restore the database files to:

sqlcmd -E -S .\sophos -Q "restore database sophos52 from disk='C:\sophos52db.bak' WITH REPLACE, MOVE 'sophos52' to 'E:\Program Files\Microsoft SQL Server\MSSQL\data\SOPHOS52.MDF', Move 'SOPHOS52_Log' to 'E:\Program Files\Microsoft SQL Server\MSSQL\data\SOPHOS52_Log.LDF'"

Note: You will have to change the 'E:\<path>...' to the required path location.  Be careful of the single and double quotes as these are important.

Allow connections to a restored database

If required you can set the database to be online (accessible by applications) with:

sqlcmd -E -S .\sophos -d "master" -n -b -Q "ALTER DATABASE sophos52 SET ONLINE"

Dropping (deleting) a database

Warning: Before dropping/deleting a database we recommend you make a backup of the database using either DataBackupRestore.exeBackupDB.bat or use the command mentioned in Backing up and restoring a database before attempting this section.

To drop a database from a SQL Server instance run:

sqlcmd -S .\sophos -d master -E -Q "drop database sophos52"

Once dropped (deleted) the database will no longer be attached to the SQL Server instance (see List databases in a SQL Server instance above).  This command can be useful when an existing database, that is no longer required, is causing an installation or upgrade to fail.

Attaching a database

Attaching a database can be done graphically using Microsoft Management Studio Express (a free tool) and is recommend over the command line version.  The command to run is:

sqlcmd -E -S .\sophos -Q "EXEC sp_attach_db @dbname = sophos52, @filename1 = 'yourLocalFilePathToTheMDFfile\sophos52.mdf', @filename2 = 'yourLocalFilePathToTheLDFfile\sophos52_log.ldf'"

In the example above we are making use of the inbuilt stored procedure (SP) 'sp_attach_db'.  The 'sophos52' database is attached to the local 'sophos' SQL Server instance using this stored procedure.  Note: You will have to change the yourLocalFilePathToThe... part to the correct path and the MDF and LDF files may not be in the same folder.

Commands to extract database information

In this section are some example commands to directly export information from the Sophos database.  However you should also consider exporting computer information with:

  1. In console version 4.7 (and higher) you can export information directly from the console by copying and pasting.
    1. Select the group level, filter the view and sort the column as required.
    2. Select all the contents of the the computer list (Ctrl+A).
    3. Copy the information (Ctrl+C).
    4. Open Notepad.exe and paste (Ctrl+V) in the information.
    5. Save the file with a .txt file extension.
    You can then open/import the file into a spreadsheet as a tab delimited file.
  2. Advanced third-party reporting can used with the Sophos core database by using the Sophos Reporting Interface.  For more information see article 112873.

Extract a list of computers that are not reporting to the console

To export a list of computer names to a text file of computers that have not reported to the console in some time use:

sqlcmd -E -S .\sophos -d sophos52 -Q "select name from computersanddeletedcomputers where managed=1 and lastmessagetime<'MM/DD/YYYY'" > C:\nonReportingComputerList.txt

In the example above change MM/DD/YYYY to a suitable date (i.e., a date occurring a few months from the current date).

Extract a list of computers that are pending a reboot

To export a list of computer names to a text file of computers that show an outstanding reboot required alert use:

sqlcmd -E -S .\sophos -d sophos52 -Q "select distinct c.name from computersanddeletedcomputers as c inner join errors as e on e.id = c.lastauerroralert where e.Number = 109 and e.outstanding = 1" > C:\rebootRequiredComputers.txt

Extract a list of computers that are shown as disconnected in the console

When a computer is disconnected a small red cross is shown next to the computer's hostname in the console.  To export a list of these computers use:

sqlcmd -E -S .\sophos -d sophos52 -Q "select name from computersanddeletedcomputers where managed=1 and connected = 0" > C:\nonDisconnectedComputerList.txt

Extract a list of computers (IP addresses and hostnames) that are unmanaged

sqlcmd -E -S .\sophos -d sophos52 -Q "Select CAST(IPADDRESS / 256 / 256 / 256 % 256 AS VARCHAR) + '.' + CAST(IPADDRESS / 256 / 256 % 256 AS VARCHAR) + '.' + CAST(IPADDRESS / 256 % 256 AS VARCHAR) + '.' + CAST(IPADDRESS % 256 AS VARCHAR) AS IPAddress, NAME AS Computername From ComputersANDDeletedComputers where managed=0 and deleted=0 Order by IPAddress" -w 1000 -o C:\ipAddressHostnameListOfUnmanagedComputer.txt

Extract a list of computers running a version of SAV9.x

sqlcmd -E -S .\sophos -d sophos52 -Q "SELECT computersanddeletedcomputers.name, packages.savversion FROM computersanddeletedcomputers, packages WHERE computersanddeletedcomputers.deleted=0 and computersanddeletedcomputers.packageid = packages.id and packages.savversion like '9%'" -w 1000 -o C:\computersRunningSAV9List.txt

Extract a list of computer where the on-access scanner is inactive

sqlcmd -E -S .\sophos -d sophos52 -Q "select name, savonaccess from computersanddeletedcomputers where managed=1 and savonaccess=0" -w 1000 -o c:\onaccessIsOff.txt

Extract the computer name, HIPS detection and file path for a computer

sqlcmd -E -S .\sophos -d sophos52 -Q "select Name, Threats.ThreatName, Threats.FullFilePath FROM Threats inner join ComputersandDeletedComputers on Threats.ComputerID = ComputersandDeletedComputers.ID WHERE Threats.ThreatName like 'HIPS%'" -w 1000 -o C:\HIPSReport.txt

Extracting information regarding Sophos Patch

The following will return the names of computers that have Patch installed, ran an assessment and are fully patched:

select DISTINCT s.ComputerName as "Computer name" from SOPHOS52.dbo.ComputerListData2 as S with (nolock) INNER JOIN SOPHOSPATCH.dbo.Computer as SP with (nolock) ON S.ComputerName = SP.name INNER JOIN SOPHOSPATCH.dbo.AssessmentReport as SPA with (nolock) on SP.computerID = SPA.computerID where S.PmaInstalled = '1' and S.PmaAssessmentResult > '0' and SPA.computerID IS NULL

The command shown above can be slightly altered to return computers that are missing patches (as per the Patch Assessment Event Viewer 'Computers missing patches' tab):

select DISTINCT S.ComputerName as "Computer name" from SOPHOS52.dbo.ComputerListData2 as S with (nolock) INNER JOIN SOPHOSPATCH.dbo.Computer as SP with (nolock) ON S.ComputerName = SP.name INNER JOIN SOPHOSPATCH.dbo.AssessmentReport as SPA with (nolock) on SP.computerID = SPA.computerID where S.PmaInstalled = '1' and S.PmaAssessmentResult > '0' and SPA.computerID IS NOT NULL

The following command includes the last assessment time performed on the computer:

select DISTINCT S.ComputerName as "Computer name", SP.sessionTimestamp as "Last assessment (UTC)" from SOPHOS52.dbo.ComputerListData2 as S with (nolock) INNER JOIN SOPHOSPATCH.dbo.Computer as SP with (nolock) ON S.ComputerName = SP.name INNER JOIN SOPHOSPATCH.dbo.AssessmentReport as SPA with (nolock) on SP.computerID = SPA.computerID where S.PmaInstalled = '1' and S.PmaAssessmentResult > '0' and SPA.computerID IS NULL

Output the error ID based on a computer name

sqlcmd -E -S .\sophos -d sophos52 -Q "select errors.number, errors.source, errors.alerttime from errors inner join computersanddeletedcomputers on errors.computerid = computersanddeletedcomputers.id where computersanddeletedcomputers.name='myComputerName'"

Output the entire contents of a table

To output the entire contents of a particular table into a .csv file that can be imported into Microsoft Excel use:

sqlcmd -E -S .\sophos -d sophos52 -Q "set nocount on; select * from tableName" -h-1 -s"," -w 10000 -o output.csv

In the example above change 'tableName' to the required table name.

Commands to update database information

Use PurgeDB.exe

Warning: Ensure you have a backup of the database before writing to the database.

We do not recommend any direct SQL commands, that write to the Sophos database, are used.  For clearing alerts and removing old computers use the PurgeDB.exe utility.

Troubleshooting common errors and problems

If you need to troubleshoot a error or problem we recommend you search Microsoft documentation for the best answer.  Example: Troubleshooting: Timeout Expired.

 
If you need more information or guidance, then please contact technical support.

Rate this article

Very poor Excellent

Comments