Browse Live Discover and Response Queries by Category
Approved

NOTES: Answer in comment

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
Gerald Szakal1
  • in reply to Karl_Ackerman

    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

Unfiltered HTML