Monthly Archives: June 2019

Sophos

Migrating the Sophos Enterprise Console Databases to a New Server

Basic Information
⦁ Sophos Enterprise Console (SEC) 5.5.0
⦁ One Management/Console Server
⦁ Database on a remote SQL Server – default instance

Database names
⦁ SOPHOS550
⦁ SOPHOSPATCH52
⦁ SophosSecurity

Database and Log File Names
⦁ SOPHOS550.mdf and SOPHOS550_log.LDF
⦁ SOPHOSPATCH52.mdf and SOPHOSPATCH52_log.LDF
⦁ SophosSecurity.mdf and SophosSecurity_log.LDF

SQL Server Settings
⦁ Database installed in the default MSSQLSERVER instance and not a SOPHOS instance – note the commands used with the batch files to backup and restore.
⦁ Sophos management user account called sopmgmt and this account has “sa” rights for SQL and is a member of the local administrators account on the server.
⦁ SQL Browser service running (install will not start if this service is not running).
⦁ Created a folder called C:\Temp\SophosBackups on both the new and existing SQL servers.

Note – to complete the install of the Sophos databases requires the SQL Server server to restarted.

Installation
Install the databases
⦁ Log on the new SQL Server server using the sopmgmt account.
⦁ Run the SEC installer and select Database Only from the options.
⦁ Change the instance name to MSSQLSERVER (can type free text in the dropdown box).
⦁ Set the account (used the logged on user – sopmgmt)
⦁ Restart the SQL Server server.

Stop Management Server Services
On the Management Server server stop the following services:
⦁ Sophos Management Service
⦁ Sophos Encryption Business Logic Service
⦁ Sophos Patch Endpoint Communicator Service
⦁ Sophos Patch Server Communicator

As this install was using a test environment the PowerShell command below was used to stop all Sophos Services:

get-service | ?{$_.DisplayName -ilike “Sophos*”} | stop-service

Backups
Note – use .\ to indicate the databases exist in the default SQL Server instance – the backup and restore batch files both default to a SQL instance named Sophos.

⦁ On the existing database server open an elevated Command Prompt and run the following command:
CD C:\Program Files\Sophos\Enterprise Console\DB\Core

Run the following in turn, assuming the backup location C:\Temp\SophosBackups exists:

⦁ backupdb.bat C:\Temp\SophosBackups\SOPHOS550db.bak .\ SOPHOS550
⦁ backupdb.bat C:\Temp\SophosBackups\SOPHOSPATCH52db.bak .\ SOPHOSPATCH52
⦁ backupdb.bat C:\Temp\SophosBackups\SophosSecuritydb.bak .\ SophosSecurity
⦁ Copy the backup files to C:\Windows\Temp\SophosBackups on the new SQL server.

Restores
⦁ On the new database server open an elevated Command Prompt and run the following command:
CD C:\Program Files\Sophos\Enterprise Console\DB\Core

Run the following in turn, assumng the backup location C:\Temp\SophosBackups exists:

⦁ restoredb.bat C:\Temp\SophosBackups\SOPHOS550db.bak .\ SOPHOS550
⦁ restoredb.bat C:\Temp\SophosBackups\SOPHOSPATCH52db.bak .\ SOPHOSPATCH52
⦁ restoredb.bat C:\Temp\SophosBackups\SophosSecuritydb.bak .\ SophosSecurity

Repoint the Management Server
On the Management Server change the server reference in the following registry locations to reflect the name of the new database server:

⦁HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Sophos\EE\Management Tools\DatabaseConnectionMS
⦁HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Sophos\ServerSecurity\DatabaseConnection
⦁HKEY_LOCAL_MACHINE\SOFTWARE\Sophos\Patch\SophosPatchConnectionString
⦁HKEY_LOCAL_MACHINE\SOFTWARE\Sophos\ServerSecurity\DatabaseConnection

⦁ Restart the Management Server server.

Reference: https://community.sophos.com/kb/en-us/17323
Reference: https://community.sophos.com/kb/en-us/110380

SCCM CMPivot

CMPivot Operators

== Equals
!= Not equals
like LHS contains a match for RHS
!like LHS doesn’t contain a match for RHS
contains RHS occurs as a subsequence of LHS
!contains RHS doesn’t occur in LHS
startswith RHS is an initial subsequence of LHS
!startswith RHS isn’t an initial subsequence of LHS
endswith RHS is a closing subsequence of LHS
!endswith RHS isn’t a closing subsequence of LHS

Reference: https://docs.microsoft.com/en-us/sccm/core/servers/manage/cmpivot

SCCM, SCCM CMPivot

CMPivot Examples

To accesss CMPivot, highlight a Collection containing the machines you wish to query against and then click Start CMPivot from the ribbon or right-click context menu.

The CMPivot query below returns all VMware devices in the collection used to run CMPivot against:

device | where ManuFacturer == ‘VMware, Inc.’ and (Device like ‘%CM%’ and Model like ‘%VM%’)

Notes

  • where and like are case sensitve
  • Device, manuFacturer, ‘VMware, Inc.’,, ‘%CM%’, ‘%svR%’ and ‘%VM%’ are not case sensitive.
  • Need to use a double == for equals.
  • Use % as a wildcard.
  • Text in single snicketts.
  • Use of () is for nesting but not required if nesting not used.

Count the number if devices per manufacturer.
Device | summarize dcount (Device) by Manufacturer

Below shown to indicate presentation – does not need to be all one line.
Device
| where (Manufacturer == ‘VMware, Inc.’)
| where (Device == ‘CM01’)

  • AD Security Groups that are members of local admins groups.

Administrators | where (PrincipalSource == ‘ActiveDirectory’ and ObjectClass == ‘User’)

  • Collection members where the SMS Executive service is running.

Service | where Name == ‘SMS_EXECUTIVE’ | where (State == ‘Running’)

  • Count the different OS version of the devices in the colllection and then display (render) as a bar chart.

OperatingSystem | summarize dcount (Device) by Caption | render barchart

  • Disk space with limited result set using the project operator to define the displayed columns.

LogicalDisk
| where (Description == ‘Local Fixed Disk’)
| project DeviceID, FreeSpace
| order by FreeSpace desc
| where (DeviceID == ‘C:’)

  • Installed software on all devices.  In the resultset note you can click on the underlined number to drill down.

InstalledSoftware | summarize dcount(Device) by ProductName

  • More specific – specifc application on a device.

InstalledSoftware
| where (ProductName == ‘Microsoft Monitoring Agent’)
| where (Device == ‘OM01’)

  • Count all installed software that does not have Microsoft in the product name using !like as the not like operator.

InstalledSoftware 
| where ProductName !like ‘%Microsoft%’
| summarize dcount(Device) by ProductName