Under Review

Excluding Hashes from various scans

Hello all

I am running a number of scans including but not limited to "Unsigned applications that were run" which I believe I got from this site.  I find the results to be extremely "busy" with so many pages it is almost unusable (155).  I am looking to filter out specific hashes that have been investigated and are considered "safe" hopefully in a list format that can be added to the existing query like

exclude - 7e612ac5cadbf4c1b91f27fa1efd6a781f20a729620537f4756724d27943a2b1

exclude - dc4335464ac739d5ba664dc1c6690938b4d657f62846735aa9ee764747e4f626

The query I am running is 

SELECT
CAST(datetime(spj.time,'unixepoch') AS TEXT) processExecutionTime,
spj.processName,
spj.pathName processPath,
spj.sophosPID,
spj.sha256,
sfp.globalRep,
sfp.localRep,
sfp.mlScore
FROM sophos_process_journal spj
LEFT JOIN users u
ON spj.sid = u.uuid
LEFT JOIN sophos_file_properties sfp
ON sfp.sha256 = (CASE
WHEN spj.sha256 IS NULL OR spj.sha256 = ''
THEN '0000000000000000000000000000000000000000000000000000000000000000'
ELSE
spj.sha256
END
)
WHERE spj.eventType = 0
AND sfp.localRepData != ''
AND json_extract(sfp.localRepData, '$.reputationData.isSigned') = 0
AND spj.time >= CAST(STRFTIME('%s','NOW','-24 HOURS') AS INT)
Please understand that I am not a programmer or very familiar with SQL queries so reply as if to a dummy.  I realize that I can reduce the time period, but don't want to.  I see this functionality as being useful in a number of scans as you build and check your networks baseline
  • Karl

    Thanks, that worked very well shortening the list down to 1 usable page.  For those that may want to use exclusions as well or instead I had a bit of help from our application specialist and was able to get the "exclusions" a little more elegant with the following:

    LEFT JOIN sophos_file_properties sfp
    ON sfp.sha256 = (CASE
    WHEN spj.sha256 IS NULL OR spj.sha256 = ''
    OR spj.sha256 = '160d2b6dc889e41e26b8ae674a74c96b8a0bb92b3960a5e8fd2354cff366f06a'
    OR spj.sha256 = 'dc4335464ac739d5ba664dc1c6690938b4d657f62846735aa9ee764747e4f626'
    THEN '0000000000000000000000000000000000000000000000000000000000000000'
    ELSE
    spj.sha256
    END

    Sorry but the cut and paste stripped out the leading spaces but it shows the functionallity.

    Regards

    Jerry

  • Another way to address these kinds of duplications of results is to add a group by. In this case I group by the process SHA256 and count the number of instances it was seen.

    SELECT
       CAST(datetime(MIN(spj.time),'unixepoch') AS TEXT) FIRST_processExecutionTime,
       CAST(spj.processName AS TEXT) ProcessName,
       CAST(spj.pathName AS TEXT) ProcessPath,
       COUNT(spj.sophosPID) Instances,
       CAST(GROUP_CONCAT(DISTINCT spj.sophosPID) AS TEXT) SophosPID_List,
       spj.sha256,
       sfp.globalRep,
       sfp.localRep,
       sfp.mlScore,
       CAST(datetime(MAX(spj.time),'unixepoch') AS TEXT) FIRST_processExecutionTime
    FROM sophos_process_journal spj
    LEFT JOIN users u
       ON spj.sid = u.uuid
    LEFT JOIN sophos_file_properties sfp
       ON sfp.sha256 = (CASE
          WHEN spj.sha256 IS NULL OR spj.sha256 = ''
          THEN '0000000000000000000000000000000000000000000000000000000000000000'
          ELSE spj.sha256
          END
          )
    WHERE spj.eventType = 0
       AND sfp.localRepData != ''
       AND json_extract(sfp.localRepData, '$.reputationData.isSigned') = 0
       AND spj.time >= CAST(STRFTIME('%s','NOW','-24 HOURS') AS INT)
    GROUP BY spj.sha256

  • I have found a klunky solution,  I am sure there are those out there that will have better ideas.  I modified the "When" line as follows

    LEFT JOIN sophos_file_properties sfp
    ON sfp.sha256 = (CASE
    WHEN spj.sha256 IS NULL OR spj.sha256 = '' OR spj.sha256 = '160d2b6dc889e41e26b8ae674a74c96b8a0bb92b3960a5e8fd2354cff366f06a'
    THEN '0000000000000000000000000000000000000000000000000000000000000000'

    Given one SQL Server process that ran as much as multiple times per second it took the report from 155 pages to 1