Please help me alter this VQL for an artifact that alerts on USB writes to trigger on an amount of files written rather than the size the of the file

[spza2929]

I tried using chat GPT to get this VQL the way I need it, but it keeps using inner joins and other things that dont work in VQL, and my SQL\VQL is terrible (but I know you can't use inner joins in VQL). Basically, I have a client event artifact that will monitor removable drives smaller than 3.2 terabytes when attached to an agent host (artifact 1), and another artifact that will alert on writes larger than 2 GB (artifact 2). The problem I am trying to solve is to trigger when more than 10 files of any size are copied in a period (say 30 seconds) as opposed to what it is doing now, which is triggering on size greater than 2GB. This is basically to monitor for data exfiltration's for a client from employees. (It would also be nice to filter the alerting on file type if you can include and example for that. Also I realize I am no expert in this area, so any additonal items anyone wants to suggest to accomplish this goal that I am not thinking of, by all means, please let me know them. I realize this is a big long annoying question, so thank you very much in advance

ARTFACT1:

name: Custom.Windows.Detection.Thumbdrives.List
description: |
Users inserting Thumb drives or other Removable drive pose a
constant security risk. The external drive may contain malware or
other undesirable content. Additionally thumb drives are an easy way
for users to exfiltrate documents.

This artifact watches for any removable drives and provides a
complete file listing to the server for any new drive inserted. It
also provides information about any addition to the thumb drive
(e.g. a new file copied onto the drive).

We exclude very large removable drives since they might have too
many files.

type: CLIENT_EVENT

parameters:

• name: maxDriveSize
  type: int
  description: We ignore removable drives larger than this size in bytes.
  default: "3200000000000"

sources:

• query: |
  LET removable_disks = SELECT Name AS Drive,
  atoi(string=Data.Size) AS Size
  FROM glob(globs="/*", accessor="file")
  WHERE Data.Description =~ "Removable" AND Size < atoi(string=maxDriveSize)

  LET file_listing = SELECT FullPath,
      Mtime As Modified,
      Size
  FROM glob(globs=Drive+"\\**", accessor="file")
  LIMIT 1000
  
  LET hostname = SELECT Hostname FROM system()
  
  SELECT *, hostname FROM diff(
    query={
       SELECT * FROM foreach(
           row=removable_disks,
           query=file_listing)
    },
    key="FullPath",
    period=10)
    WHERE Diff = "added"
  

---

ARTIFACT 2

name: Custom.Server.Alerts.ThumbDriveUseMorethan2GB
description: |
Send an email if thumb drive use was detected on
any client. This is a server side artifact.

type: SERVER_EVENT

parameters:

• name: EmailAddress
  default: JMS@runwellsolutions.com
• name: MessageTemplate
  default: |
  Thumb drive used

sources:

• query: |
  SELECT * FROM foreach(
  row={
  SELECT * from watch_monitoring(
  artifact='Custom.Windows.Detection.Thumbdrives.List')
  WHERE Size > 2000000000
  },
  query={
  SELECT * FROM mail(
  to=EmailAddress,
  subject='thumb drive alert',
  period=30,
  server="smtp.gmail.com",
  body=format(
  format=MessageTemplate,
  args=[FullPath, Size, ClientId])
  )
  })

[scudette]

What you are asking for is actually quite complex and it is unlikely that chat GPT has seen enough such examples (remember it is basically a search engine not AI :-).

The trick here is that you are asking about correlating multiple events over time - so imagine I am writing 5 files to the removable drive then another 5 - so if your limit is 10 it should trigger right? So what we need here is correlation across time.

So this is how that works. I will show you how to develop this in steps:

1. Figure out what you want to look for. In this case you want to check files in a removable drive. A removable drive is just a directory so lets practice on a directory first, then we can change to the removable drive later.

The first thing is to open a notebook inside velociraptor.exe gui and play with some VQL. Lets start off with the following simple VQL:

SELECT OSPath, Size
FROM glob(globs="C:/watched_dir/*")

Here the query just lists all the files in the watched directory and their size. You can run the query and copy files into it and see the results.

This is not useful because it is not an EVENT query - it just runs once and that's it. So we want to turn a regular query into an event query so we know when something happens.

2. We want to detect changes in this query so we can use the diff() plugin to run the query every few seconds and look for changes. The diff plugin works by looking at a key column and checking if it changes - for example we can look at the combination of filename and size to see if the files appear or disappear or if the file changed size.

SELECT *
FROM diff(
  key="Key",
  period=10,
  query={
    SELECT OSPath, Size,
           format(format="%v_%v",  args=[OSPath, Size]) AS Key
    FROM glob(globs="C:/watched_dir/*")
  })

In the above example I copy notepad.exe and remove notepad.exe from this directory. You can see the diff() plugin tells us if the file was added or removed between periodic checks.

This is great for the query that looks at the size and reports if the size is larger than the limit (which is the example you have seen). But we actually want to correlate rows across time here - if the diff() plugin reports 5 rows added now and 5 rows in 2 minutes, then we are talking about 10 rows all up and we can trigger an alert!

3. To correlate across time we have two possibilities - the fifo() plugin or the sequence() plugin. The fifo() option is older and maybe a bit harder to use so I will show you the sequence way.

The sequence() plugin works by running one or more event queries and storing their rows in an array. For each new row, the plugin runs a specified query over the array and if rows are returned (e.g. an alert is generated) then the sequence is cleared and the row emitted.

To give you an example how that looks like:

LET AddedFiles = SELECT OSPath, Size
FROM diff(
  key="Key",
  period=2,
  query={
    SELECT OSPath, Size,
           format(format="%v_%v", args=[OSPath, Size]) AS Key
    FROM glob(globs="C:/watched_dir/*")
  })
WHERE Diff = "added"

SELECT * FROM sequence(added_files=AddedFiles, max_age=60,
query={
    SELECT SEQUENCE AS Files, len(list=SEQUENCE) AS Total
    FROM scope()
    WHERE Total >= 2
})

In this example I made the numbers really small so it is easy to test:

• Check for changes every 2 seconds (probably would do this less frequently in practice)
• Alert when 2 files are written

So we ran the diff() query we did before, which emits a row for every added file. Say the user wrote 1 file at time 1, and one file at time 3 (the diff plugin will check at time 2 and time 4 - every 2 seconds):

• At time 2, the diff plugin noticed one file was added, then it sends that row to the sequence plugin. Inside the plugin there is an array called SEQUENCE which now has one row. The query SELECT SEQUENCE AS Files, len(list=SEQUENCE) AS Total from scope() WHERE Total >= 2 is now run over all the rows but since there is only 1 row now, the WHERE clause will not be true and there is no output.

• Later on at time 4, the diff plugin will notice another file changed, the row is emitted again into the SEQUENCE array and the query is run again. This time, there are 2 rows in the SEQUENCE and so the WHERE clause will be true. The sequence() plugin emits the rows and clears the SEQUENCE array since the condition is considered "triggered".

Now that we consider than an alert was raised, if the user copies one more file we do not send a new alert (i.e. the counter is reset).

This is still not ideal as if the user copied 100 files we will send 10 alerts (1 for each 10 files) but generally this is how the sequence plugin can correlate results from multiple sources in a time sequence. You can see this effect here:

[scudette]

I just thought I will add the example with using the fifo() as well as that is a bit different

The fifo() plugin also maintains a list of previous rows internally but has more control over how the rows are queried. The fifo is running in the background and just queries over the entire sequence each time.

LET AddedFiles = SELECT OSPath, Size
FROM diff(
  key="Key",
  period=2,
  query={
    SELECT OSPath, Size,
           format(format="%v_%v", args=[OSPath, Size]) AS Key
    FROM glob(globs="C:/watched_dir/*")
  })
WHERE Diff = "added"

LET FifoQuery = SELECT * FROM fifo(flush=TRUE, query=AddedFiles, max_rows=1000, max_age=60)

SELECT * FROM foreach(row={
    SELECT * FROM clock(start=1, period=4)
}, query={
  SELECT enumerate(items=OSPath.Basename) AS Files, count() AS Count 
  FROM FifoQuery
  GROUP BY 1
})
WHERE Count > 10

In the above example, the fifo() array is queried every 4 seconds and we use group by to count the total number of rows in the fifo() at each query. If there are more than 10 rows we report a row. Notive the fifo is reset every 4 seconds so if the user copies 5 files and waits 4 seconds to copy the next 5 we wont detect it.

But the flip side is that we dont send multiple rows if there are many files copies

[spza2929]

Thanks again Mike for the examples. They go a long way helping me understand how this will work. It is very much appreciated!

[spza2929]

Ok, so I have a working version for grabbing more than 10 writes over a period of time to any drive like you had suggested starting with. I tried amending the working VQL below by adding an "AND" clause to the "WHERE" line (the line with a "*" in front of it below.) I added "AND Data.Description =~ "Removable" in front of "WHERE Diff = "added"" line, this was to attempt to filter on external media/USB storage like my previous example had accomplished. But for some reason this breaks my detections, and nothing is ever written to the table. Do you know what changes I need to make to get this artifact working for USB storage only instead of any drive? (again thank you so much for your time, velociraptor is such an incredible tool, I am slowly getting a lot of use out of it) Here is the VQL that works for any drive currently:

    LET AddedFiles = SELECT OSPath, Size, FullPath
    FROM diff(
    key="Key",
    period=25,
    query={
    SELECT OSPath, Size, FullPath,
       format(format="%v_%v", args=[OSPath, Size, FullPath]) AS Key
    FROM glob(globs="*:/*")
    })
  * WHERE Diff = "added"

    LET FifoQuery = SELECT * FROM fifo(flush=TRUE, query=AddedFiles, max_rows=1000, max_age=60)

    SELECT * FROM foreach(row={
    SELECT * FROM clock(start=1, period=25)
    }, query={
    SELECT enumerate(items=OSPath.Basename) AS Files, count() AS Count, FullPath() AS FullPath
    FROM FifoQuery
    GROUP BY 1
    })
    WHERE Count > 10