For query assistance, please see the following Best Practices guide
With XDR we have access to the OSQuery supported tables and the ability to write our own SQL queries that can include variables. One of the tables available is a YARA table. This allows you to supply a signature file and path and the underlying engine in OSQuery will perform a search for files that match the signature. Very old skool malware hunting I know, but folks find it useful for emerging threats where vendors may not have updated their proprietary malware protection engines yet.
To learn more about YARA rules check out the 'readthedocs' https://yara.readthedocs.io/en/stable/ and here https://virustotal.github.io/yara/
Threat intelligence forums provide YARA rules to define the Indicator of Compromise they have associated to malicious activity. One popular public location for YARA rules is in a GIT repo.
https://github.com/Yara-Rules/rules
The current version of OSQuery included with XDR is 4.6 for Mac and LINUX and 4.7 for Windows This fall we are going to upgrade to OSQuery version 4.9 where a new YARA rules engine will be supported on windows, so I started thinking about how to write a YARA scanning engine that would take a YARA rule and perform the scan. To my surprise the YARA table is available and appears to work fine in version 4.7.
I wanted to build a few tools that could leverage the support for OSQuery and the public repository of rules. Below are the new queries I wrote. For folks looking to use this I strongly encourage you use your own hosting site for your YARA rules and examine any rule before you run it so you understand what it is looking for. A 'hit' with a YARA rule is not a confirmation of malware it is only confirmation that whatever the rule was found a match and the ability to accurately classify malware will only be as good as the rule. 
Search for A Yara rule:
This query will download the Index file of current yara rules from ttps://github.com/Yara-Rules/rules and then search for any rules that match the variable. We automatically add the wildcards '%' to the string for a broader match capability. The results are the Yara rule URL and the signature YARA file itself. This is a live query so it is running on the device you selected and it takes a while to execute. It has to download a few KB of data that it converts to a virtual table that it then searches. If it finds any matches it has to go back to the website to pull the actual file down for the results we will show.
-- Search for YARA RULES --
-- VARIABLE $$Yara rule file name$$ STRING
WITH RECURSIVE
-- Build a counter from 1 to 500
Counter(x) AS (VALUES (1) UNION ALL SELECT x+1 FROM Counter WHERE x < 500),
-- GET THE index of rules from the GIT REPO
Index_File(index_of_rules) AS (
SELECT REPLACE(REPLACE(result,'include ".',''),'"','')
FROM curl
WHERE url = 'https://raw.githubusercontent.com/Yara-Rules/rules/master/index.yar'
),
-- Build a list of the yara rules files from the public git repo based on the current index of rules
Rule_Files(Yara_Rule_URL) AS (
SELECT SPLIT(index_of_rules,CHAR(10),x) Yara_Rule_URL
FROM Index_file, Counter
WHERE Yara_Rule_URL LIKE '%$$Yara rule file name$$%'
),
-- Get the relevant YARA Signature Rules
Signature_Rules(Yara_Rule_URL, Yara_Sig_Rule ) AS (
SELECT Yara_Rule_URL, CAST('/*'||Yara_Rule_URL||'*/'||CHAR(10)||result AS TEXT) -- Insert the URL for the RULE into the Rule (HACK) We need it for the displayed results
FROM Rule_Files
JOIN curl ON url = 'https://raw.githubusercontent.com/Yara-Rules/rules/master/'||Yara_Rule_URL
)
SELECT
replace(Yara_Rule_URL, rtrim(Yara_Rule_URL, replace(Yara_Rule_URL, '/', '')), '') YARA_File,
'https://raw.githubusercontent.com/Yara-Rules/rules/master/'||Yara_Rule_URL,
YARA_Sig_Rule
FROM Signature_Rules
Perform a SCAN using an online YARA File.
The returned data above includes the URL for the raw rule file and with that you can PIVOT to the next query that will take that URL and an admin supplied path to perform the scan.
This one is MUCh faster as you already know the location of the YARA signature file you want to run.
SEARCH (for 'malware' + PIVOT on 'eicar' + RUN THE SCAN 'for the target directory and sub directories''
Now run the Scan for the specified path.
Source Code:
/********************************************************************************************\
| The admin will supply the URL for an online YARA file and path to search |
\********************************************************************************************/
-- YARA SCANNER FROM ON-LINE YARA RULES GIT REPO
-- VARIABLE $$Search File Path$$ FILE PATH
-- VARIABLE $$YARA File URL$$ URL
WITH
-- Get the relevant YARA Signature Rules
Signature_Rules(Yara_Rule_URL, Yara_Sig_Rule ) AS (
SELECT '$$YARA File URL$$' Yara_Rule_URL,
CAST('/*'||'$$YARA File URL$$'||'*/'||result AS TEXT) Yara_Sig_Rule-- Insert the URL for the RULE into the Rule (HACK) We need it for the displayed results
FROM curl
WHERE url = '$$YARA File URL$$'
)
SELECT
replace(replace(path, rtrim(path, replace(path, '/', '')), ''), rtrim(replace(path, rtrim(path, replace(path, '/', '')), ''), replace(replace(path, rtrim(path, replace(path, '/', '')), ''), '\', '')), '') File_Name,
matches,
count,
path,
replace(SPLIT(sigrule,'*\',1), rtrim(SPLIT(sigrule,'*\',1), replace(SPLIT(sigrule,'*\',1), '/', '')), '') YARA_File,
'$$YARA File URL$$' YARA_Rule_URL
FROM yara WHERE Path LIKE '$$Search File Path$$' AND sigrule IN (SELECT Yara_Sig_Rule FROM Signature_Rules)
ORDER BY count DESC, matches ASC, YARA_File ASC, File_Name ASC
For the last bit of work I wanted a scanner that could run a rule by name on a path without having to first search for the rule. This one takes the approximate name of a rule, searches the index of currently published rules and finds any matches. For each matching YARA Signature it will run that on the specified path. This way you can run multiple YARA rules on a file or directory and sub directories. DO NOTE that the watchdog might kill the query if it starts to consume too much memory or CPU. You can use wildcards so you can ask the system to scan with all 480 yara rules across the entire drive, but the watchdog will surely be unhappy and kill the query to ensure the end user experience is acceptable.
/********************************************************************************************\
| We will refrence the public GIT Repo for Yara rules at https://github.com/Yara-Rules/rules |
| The GIT repo is activily being updated and we want access to current published rules so |
| we will first get the index of rules and see which of those match our desired rule name |
| With the list of yara files that match what we are looking for we will then pull the |
| signature rule for each of them and execute a YARA search for each rule in the target file |
| path selected by the admin. The current repo has only 480 rules in it. |
\********************************************************************************************/
-- YARA SCANNER FROM ON-LINE YARA RULES GIT REPO
-- VARIABLE $$Search File Path$$ FILE PATH
-- VARIABLE $$Yara rule file name$$ STRING
WITH RECURSIVE
-- Build a counter from 1 to 500
Counter(x) AS (VALUES (1) UNION ALL SELECT x+1 FROM Counter WHERE x < 500),
-- GET THE index of rules from the GIT REPO
Index_File(index_of_rules) AS (
SELECT REPLACE(REPLACE(result,'include ".',''),'"','')
FROM curl
WHERE url = 'https://raw.githubusercontent.com/Yara-Rules/rules/master/index.yar'
),
-- Build a list of the yara rules files from the public git repo based on the current index of rules
Rule_Files(Yara_Rule_URL) AS (
SELECT SPLIT(index_of_rules,CHAR(10),x) Yara_Rule_URL
FROM Index_file, Counter
WHERE Yara_Rule_URL LIKE '%$$Yara rule file name$$%'
),
-- Get the relevant YARA Signature Rules
Signature_Rules(Yara_Rule_URL, Yara_Sig_Rule ) AS (
SELECT Yara_Rule_URL, CAST('/*'||Yara_Rule_URL||'*/'||result AS TEXT) -- Insert the URL for the RULE into the Rule (HACK) We need it for the displayed results
FROM Rule_Files
JOIN curl ON url = 'https://raw.githubusercontent.com/Yara-Rules/rules/master/'||Yara_Rule_URL
)
SELECT
replace(replace(path, rtrim(path, replace(path, '/', '')), ''), rtrim(replace(path, rtrim(path, replace(path, '/', '')), ''), replace(replace(path, rtrim(path, replace(path, '/', '')), ''), '\', '')), '') File_Name,
matches,
count,
path,
replace(SPLIT(sigrule,'*\',1), rtrim(SPLIT(sigrule,'*\',1), replace(SPLIT(sigrule,'*\',1), '/', '')), '') YARA_File,
'https://raw.githubusercontent.com/Yara-Rules/rules/master/'||SPLIT(sigrule,'*\\',1) YARA_Rule_URL
FROM yara WHERE Path LIKE '$$Search File Path$$' AND sigrule IN (SELECT Yara_Sig_Rule FROM Signature_Rules)
ORDER BY count DESC, matches ASC, YARA_File ASC, File_Name ASC
SEE ALSO the recommended reading on how to use a query for classification on powershell scripts. :
Have fun, I am off to write a MITRE TTP Scanner for NIX (Linux and MAC OS)
