Browse Live Discover and Response Queries by Category
Approved

Decode encoded powershell

With the common use of powershell by cobalt strike and every other threat actor, I though it would be nice to have a query that detects and decodes encoded powershell commands.

The first query will simply decode base 64 encoded data (As you would see in powershell scripts)

The query takes three variables.

VARIABLES:
Description  TYPE Default 
Base64 Encoded data
 STRING 

WITH RECURSIVE test(c,cur) AS (
   select '', (replace(hex(from_base64('$$Base64 Encoded data$$') ),'00','') ) 
      UNION ALL
   select c || char((case substr(cur,1,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substr(cur,1,1) end)*16 +
                    (case substr(cur,2,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substr(cur,2,1) end)), substr(cur,3)
   FROM test where length(cur)>0
   )
SELECT c Decoded_value FROM test 
WHERE length (cur) = 0

The next one is more interesting it will find encoded cmdline data in powershells that have run in the past and decode the info for you.

VARIABLES:
Description  TYPE Default 
ALL powershell Commands (1) or just encoded powershell(0) String 0
From N days ago String 20
To N days ago String 0 

-- Go find processes that have command lines with encodedcommand data
WITH Encoded_Data AS (
   SELECT
      datetime(time,'unixepoch') DATE_TIME,
--      replace(pathname, rtrim(pathname, replace(pathname, '\', '')), '') ProcessName,
      cmdline,
      sophospid,
      replace(substr(cmdline, instr(cmdline, ' -e')+2, length(cmdline)),rtrim(substr(cmdline, instr(cmdline, ' -e')+2, length(cmdline)),replace(substr(cmdline, instr(cmdline, ' -e')+2, length(cmdline)),' ','')),'') Encoded_Data,
      sid,
      parentSophosPID,
      u.username
   FROM sophos_process_journal spj LEFT JOIN users u ON  spj.sid = u.uuid
   WHERE pathname LIKE '%powershell%' AND eventtype = 0 AND time > strftime('%s','now','-$$From N days ago$$ days') and time < strftime('%s','now','-$$To N days ago$$ days')
      AND  ( cmdline LIKE '% -e %'  OR cmdline LIKE '% -en %' OR cmdline LIKE '% -enc %' OR cmdline LIKE '% -enco %' OR cmdline LIKE '% -encod %' OR cmdline LIKE '% -encode %' OR cmdline LIKE '% -encoded %'
             OR cmdline LIKE '% -encodedc %' OR cmdline LIKE '% -encodedco %' OR cmdline LIKE '% -encodedcom %' OR cmdline LIKE '% -encodedcomm %' OR cmdline LIKE '% -encodedcomma %'
             OR cmdline LIKE '% -encocodedcomman %' OR cmdline LIKE '% -encodedcommand %')
)

-- With a table of process information that includes encoded data in the command lines, we will convert the base64 encoding to text
-- WARNING this is ugly as we have to handle embeded NULLS and to 'see' them we have to convert to HEX and back to characters
SELECT
   CAST(ed.DATE_TIME AS TEXT) Date_Time,
--   CAST(ed.ProcessName AS TEXT) ProcessName,
   CAST(ed.cmdline AS TEXT) CmdLine,
   CAST(ed.sophospid AS TEXT) SophosPID,
--------------- CONVERT ENCODED BASE 64 DATA FROM A POWERSHELL TO DECODED DATA
   CAST ( (
   WITH RECURSIVE test(c,cur) AS (
      select '', (replace(hex(from_base64(ed.Encoded_Data) ),'00','') )
         UNION ALL
      select c || char((case substr(cur,1,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substr(cur,1,1) end)*16 +
                       (case substr(cur,2,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substr(cur,2,1) end)), substr(cur,3)
      FROM test where length(cur)>0
      )
   SELECT c Decoded_value FROM test WHERE length (cur) = 0
   )  AS TEXT) Decoded_base64_section,
--------------- END CONVERSION
   CAST(ed.sid AS TEXT) SID,
   CAST(ed.parentSophosPID AS TEXT) ParentSophosPID,
   CAST(ed.username AS TEXT) Username

FROM Encoded_Data ed
WHERE length(Decoded_base64_section) > 0

UNION ALL

SELECT
   datetime(time,'unixepoch') DATE_TIME,
--   replace(pathname, rtrim(pathname, replace(pathname, '\', '')), '') ProcessName,
   cmdline,
   sophospid,
   '' Encoded_Data,
   sid,
   parentSophosPID,
   u.username
FROM sophos_process_journal spj LEFT JOIN users u ON  spj.sid = u.uuid
WHERE $$ALL powershell Commands (1) or just encoded powershell(0)$$ = 1 AND
   pathname LIKE '%powershell%' AND eventtype = 0 AND time > strftime('%s','now','-$$From N days ago$$ days') and time < strftime('%s','now','-$$To N days ago$$ days')
   AND NOT ( cmdline LIKE '% -e %'  OR cmdline LIKE '% -en %' OR cmdline LIKE '% -enc %' OR cmdline LIKE '% -enco %' OR cmdline LIKE '% -encod %' OR cmdline LIKE '% -encode %' OR cmdline LIKE '% -encoded %'
          OR cmdline LIKE '% -encodedc %' OR cmdline LIKE '% -encodedco %' OR cmdline LIKE '% -encodedcom %' OR cmdline LIKE '% -encodedcomm %' OR cmdline LIKE '% -encodedcomma %'
          OR cmdline LIKE '% -encocodedcomman %' OR cmdline LIKE '% -encodedcommand %')

Karl_Ackerman
Parents

  • Thanks for this, Karl. This is really useful and quite awesome.  Just one minor change; the first variable is different in the variables list from the one used in the query.

    List: "ALL powershell Commands (1) or just encoded powershell(0)"

    Query: "Set to 1 for Just Encoded Powershell (1)"

Comment

  • Thanks for this, Karl. This is really useful and quite awesome.  Just one minor change; the first variable is different in the variables list from the one used in the query.

    List: "ALL powershell Commands (1) or just encoded powershell(0)"

    Query: "Set to 1 for Just Encoded Powershell (1)"

Children
Unfiltered HTML