/* MULTI - Query for patch level, web shells, and suspicious commands */
SELECT '----------------------' Test, '----------------------' Result, '----------------------' Evidence UNION ALL
-- Check the version of Exchange that is running, to determine if it's patched
SELECT DISTINCT
   'Check Exchange Version to confirm Patch' Test,
   CASE product_version 
      WHEN '15.0.1497.12' THEN 'Patched'
      WHEN '15.1.2106.13' THEN 'Patched'
      WHEN '15.1.2176.9' THEN 'Patched'
      WHEN '15.2.721.13' THEN 'Patched'
      WHEN '15.2.792.10' THEN 'Patched'
      ELSE 'NOT PATCHED'
   END Result,
   'Product_Version: ' || Product_version Evidence
FROM file 
WHERE path = ( (SELECT data FROM registry 
                WHERE key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ExchangeServer\v15\Setup' AND path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ExchangeServer\v15\Setup\MsiInstallPath' 
               )||'bin\Microsoft.Exchange.RpcClientAccess.Service.exe')UNION ALL
-- Identify common webshells which which may exist. Files with creation dates after Feb 28, 2021 should be reviewed.
SELECT DISTINCT
   'List of Suspect Web Shell files (if any).' TEST,
   CAST(GROUP_CONCAT(filename || CHAR(10)) AS TEXT) Result,
   CAST(GROUP_CONCAT('PATH: ' || path || CHAR(10) || 'CREATED ON: ' || DATETIME(btime,'unixepoch') || CHAR(10)) AS TEXT) Evidence
FROM file
WHERE (path LIKE 'C:\inetpub\wwwroot\aspnet_client\%' OR path LIKE 'C:\inetpub\wwwroot\aspnet_client\system_web\%' OR 
       path LIKE 'C:\Program Files\Microsoft\Exchange Server\V15\FrontEnd\HttpProxy\owa\auth\%') AND 
      (filename IN ('web.aspx','help.aspx','document.aspx','errorEE.aspx','errorEEE.aspx','errorEW.aspx','errorFF.aspx','web.aspx',
                    'healthcheck.aspx','aspnet_www.aspx','aspnet_client.aspx','xx.aspx','shell.aspx','aspnet_iisstart.aspx','one.aspx',
                    'errorcheck.aspx','t.aspx','discover.aspx','aspnettest.aspx','error.aspx','RedirSuiteServerProxy.aspx','shellex.aspx',
                    'supp0rt.aspx','HttpProxy.aspx','system_web.aspx','OutlookEN.aspx','TimeoutLogout.aspx','Logout.aspx',
                    'OutlookJP.aspx','MultiUp.aspx','OutlookRU.aspx') OR
       (LENGTH(filename) = 13) )UNION ALL
-- Identify the common pattern for commands being executed from a webshell. This is looking over the last (15 days), but can be adjusted.
SELECT DISTINCT
   'Suspicious Commands Detected as Child Process' TEST,
   'Found a Suspicious Command Which Could Have Spawned from a Web Shell' Result,
   DateTime(time, 'unixepoch') || ',' ||sophosPID || ',' || processname || ',' || cmdline  Evidence
FROM sophos_process_journal spj WHERE LOWER(spj.processname) IN ('cmd.exe','powershell.exe', 'csc.exe') AND time > strftime('%s','now','-15 days') AND 
   (SELECT LOWER(processname) FROM sophos_process_journal spj2 WHERE spj2.sophosPID = spj.parentSophosPID) IN ('w3wp.exe', 'umworkerprocess.exe')