Custom.Windows.DataAccess.yaml

name: Custom.Windows.DataAccess
author: Phalgun Kulkarni and Kostya Ilioukevitch
description: |
   This artifact combines a variety of data access artifacts and provides normalized results that can be used for data access report generation.
   It also enhances investigations by saving the time used to normalize and filter useful information from multiple sources.
   
   Please note: Execute `Server.Import.ArtifactExchange` and `Server.Import.Extras` in Servers Artifact before importing/creating this artifact.
   
   The following data access sources are included within the artifact:
   
    - `LNK` (shortcut) files
    - `Jumplists`
    - `Shellbags`
    - `MRU` (Most Recently Used files and directories)
    - `Local files` accessed via Browsers
    - `WinScp Registry Key` consisting of paths traversed<br />
  
  
   We provide two options for parsing LNK, JumpLists, ShellBags, and Browsers:
    
    - Using `built-in` artifacts
    - Using `third-party` tools
   
   We use built-in artifacts by default but if you wish to use third-party tools, please note the following:
   
    - For `LNK`, `JumpLists`, and `Shellbags`, we are using Zimmerman tools (LECmd, JLECmd, and SBECmd respectively), we recommended that you upload `.NET version 4` binaries in the tools section to avoid potential issues regarding unavailablity of .NET version 6 or 9 on endpoints.
   
    - For `Browsers`, we are using Nirsoft's BrowsingHistoryView64, which is often tagged as **`MALWARE`** by Endpoint Detection and Response tools - `this may cause alerts`.
    
    - Please upload tools **(.exe and not .zip files)** and set the setting to serve locally to avoid issues regarding internet access.
   
   We have **disabled** `MRU` parsing by default as it often contains duplicate data.
   
   Artifacts used as inspiration or wrapper:
   
   - Windows.Forensics.Lnk
   - Windows.Applications.LECmd
   - Windows.Forensics.JumpLists
   - Windows.Applications.JLECmd
   - Windows.Forensics.Shellbags
   - Windows.Applications.SBECmd
   - Windows.Applications.NirsoftBrowserViewer
   - Generic.Forensic.SQLiteHunter
   - Windows.Registry.NTUser
   - Windows.Registry.RecentDocs
   
   
tools:
  - name: LECmd
  - name: JLECmd
  - name: SBECmd
  - name: NirsoftBrowsingHistoryView64
  
type: CLIENT

parameters:
  - name: UserAccountRegex
    description: Regex to only include events tied to specific users
    type: regex
    default: .
    
  - name: DateAfter
    type: timestamp
    description: Search for events after this date. YYYY-MM-DDTmm:hh:ssZ
    
  - name: DateBefore
    type: timestamp
    description: Search for events before this date. YYYY-MM-DDTmm:hh:ssZ

  - name: UseBuiltInArtifacts
    type: bool
    default: Y
    description: Checked by default, runs built-in artifacts instead of using third-party tools
    
  - name: RecentDocsMRU
    type: bool
    default: N
    description: If checked, runs Windows.Registry.RecentDocs artifact
  
  - name: BrowserAccessNirsoft
    type: bool
    default: N
    description: If checked, runs Nirsoft's BrowsingHistoryView. **NOTE - The binary is often flagged by EDRs as `malicious` and may cause alerts**  

precondition: SELECT OS From info() where OS = 'windows'

sources:

  - name: LNK
    description: Parses Windows LNK or Shortcut files to extract evidence of data access.
      
    query: |
       LET hostname <= SELECT Hostname
         FROM info()
       
       LET DateAfterTime <= if(condition=DateAfter,
                               then=timestamp(epoch=DateAfter),
                               else=timestamp(epoch="1600-01-01"))
       
       LET DateBeforeTime <= if(condition=DateBefore,
                                then=timestamp(epoch=DateBefore),
                                else=timestamp(epoch="2200-01-01"))
       
       LET tmpdir <= tempdir()
       
       SELECT *
       FROM if(condition=UseBuiltInArtifacts,
               then={
           SELECT SourceFile.Mtime AS EventTime,
                  hostname[0].Hostname AS Host,
                  split(string=SourceFile.OSPath, sep="\\\\")[2] AS UserAccount,
                  if(condition=StringData.TargetPath,
                     then=StringData.TargetPath,
                     else=LinkTarget.LinkTarget) AS Path,
                  ShellLinkHeader.FileSize AS FileSize,
                  "LNK" AS Artifact,
                  "File_Modification" AS Time_Type,
                  SourceFile.OSPath AS ArtifactSource,
                  
                  ExtraData AS Additional_Info
           FROM Artifact.Windows.Forensics.Lnk()
         },
               else={
           SELECT *
           FROM chain(a={SELECT * FROM foreach(row={SELECT OSPath FROM Artifact.Generic.Utils.FetchBinary(ToolName="LECmd", IsExecutable=FALSE)
                        },
                        query={SELECT * FROM execve(argv=[OSPath, "-d", "C:\\", "--csv",
                         tmpdir + "lecmd", "--csvf", "results.csv"])
                        })},
                      b={SELECT SourceModified AS EventTime,
                         hostname[0].Hostname AS Host,
                         split(string=SourceFile, sep="\\\\")[2] AS UserAccount,
                         if(condition=LocalPath, then=LocalPath, else=RelativePath) AS Path,
                         FileSize,
                         "LNK" AS Artifact,
                         "File_Modification" AS Time_Type,
                         SourceFile AS ArtifactSource,
                         dict(`SourceCreated`=SourceCreated,
                              `WorkingDirectory`=WorkingDirectory,
                              `RelativePath`=RelativePath,
                              `Arguments`=Arguments) AS Additional_Info
                         FROM parse_csv(filename=tmpdir + "lecmd" + "\\results.csv")
         })
         })
       WHERE UserAccount =~ UserAccountRegex
        AND EventTime > DateAfterTime
             AND EventTime < DateBeforeTime

    notebook:
      - type: md
        template: |
          # Master Data Access Sheet

      - type: vql
        name: Master Data Access Workbook
        template: |
          SELECT *
          FROM chain(a={
            SELECT *
            FROM source(artifact="Custom.Windows.DataAccess/LNK")
            LIMIT 50
          }, b={
            SELECT *
            FROM source(artifact="Custom.Windows.DataAccess/Jumplists")
            LIMIT 50
          }, c={
            SELECT *
            FROM source(artifact="Custom.Windows.DataAccess/Shellbags")
            LIMIT 50
          }, d={
            SELECT *
            FROM source(artifact="Custom.Windows.DataAccess/Browser Access Nirsoft")
            LIMIT 50
          }, e={
            SELECT *
            FROM source(artifact="Custom.Windows.DataAccess/Browser Access SQLiteHunter")
            LIMIT 50
          }, f={
            SELECT *
            FROM source(artifact="Custom.Windows.DataAccess/WinSCP Remote Path")
            LIMIT 50
          }, g={
            SELECT *
            FROM source(artifact="Custom.Windows.DataAccess/MRU")
            LIMIT 50
          })
      - type: md
        template: |
          # Custom.Windows.DataAccess/LNK
      - type: vql
        template: |
          SELECT *
          FROM source(artifact="Custom.Windows.DataAccess/LNK")
          LIMIT 50

  - name: Jumplists
    description: Parses Windows Jumpliists or automaticdestinations and customdestinations `(only with JLECmd)` files to extract evidence of data access.
    query: |
       LET hostname <= SELECT Hostname
         FROM info()
       
       LET DateAfterTime <= if(condition=DateAfter,
                               then=timestamp(epoch=DateAfter),
                               else=timestamp(epoch="1600-01-01"))
       
       LET DateBeforeTime <= if(condition=DateBefore,
                                then=timestamp(epoch=DateBefore),
                                else=timestamp(epoch="2200-01-01"))
       
       LET tmpdir <= tempdir()
       
       LET x = scope()
       
       SELECT *
       FROM if(condition=UseBuiltInArtifacts,
               then={
           SELECT WriteTime AS EventTime,
                  hostname[0].Hostname AS Host,
                  split(string=AutomaticDestinationsPath, sep="\\\\")[2] AS UserAccount,
                  LinkTarget AS Path,
                  FileSize,
                  "Jumplists" AS Artifact,
                  "File_Modification" AS Time_Type,
                  AutomaticDestinationsPath AS ArtifactSource,
                  dict(`Application`=Application,
                       `ApplicationId`=ApplicationId,
                       `CreationTime`=CreationTime) AS Additional_Info
           FROM Artifact.Windows.Forensics.JumpLists()
         },
               else={
           SELECT *
           FROM chain(a={SELECT * FROM foreach(row={SELECT OSPath FROM Artifact.Generic.Utils.FetchBinary(ToolName="JLECmd",IsExecutable=FALSE)
                        },
                        query={SELECT * FROM execve(argv=[OSPath, "-d", "C:\\", "--fd", "--csv",
                         tmpdir, "--csvf", "results.csv"])
                     })},
                      b={SELECT * FROM foreach(row={SELECT OSPath FROM glob(globs="results_*.csv", root=tmpdir)
                     },
                        query={SELECT x.SourceModified AS EventTime,
                               hostname[0].Hostname AS Host,
                               split(string=x.SourceFile, sep="\\\\")[2] AS UserAccount,
                               if(condition=x.LocalPath, then=x.LocalPath, else=x.Path) AS Path,
                               x.FileSize AS FileSize,
                               "Jumplists" AS Artifact,
                               "File_Modification" AS Time_Type,
                               x.SourceFile AS ArtifactSource,
                               dict(`x.SourceCreated`=SourceCreated,
                                    `x.AppIdDescription`=AppIdDescription,
                                    `x.TargetIDAbsolutePath`=TargetIDAbsolutePath,
                                    `x.RelativePath`=RelativePath,
                                    `x.Arguments`=Arguments) AS Additional_Info
                               FROM parse_csv(filename=OSPath)
         })
         })
         })
       WHERE UserAccount =~ UserAccountRegex
        AND EventTime > DateAfterTime
             AND EventTime < DateBeforeTime


  - name: Shellbags
    description: Parses Windows Shellbags to extract evidence of data access.
    query: |
       LET hostname <= SELECT Hostname
         FROM info()
       
       LET DateAfterTime <= if(condition=DateAfter,
                               then=timestamp(epoch=DateAfter),
                               else=timestamp(epoch="1600-01-01"))
       
       LET DateBeforeTime <= if(condition=DateBefore,
                                then=timestamp(epoch=DateBefore),
                                else=timestamp(epoch="2200-01-01"))
       
       LET tempfolder <= tempdir()
       
       
       LET UserProfiles <= SELECT Uid,
                                  Name,
                                  Directory,
                                  UUID,
                                  Mtime
         FROM Artifact.Windows.Sys.Users()
       
       SELECT *
       FROM if(condition=UseBuiltInArtifacts,
               then={
           SELECT ModTime AS EventTime,
                  hostname[0].Hostname AS Host,
                  split(string=Hive, sep="\\\\")[2] AS UserAccount,
                  regex_replace(source=FullPath, replace="\\", re=" -> ") AS Path,
                  "N/A" AS FileSize,
                  "Shellbags" AS Artifact,
                  "Key_Modification" AS Time_Type,
                  Hive AS ArtifactSource,
                  Description AS Additional_Info
           FROM Artifact.Windows.Forensics.Shellbags()
         },
               else={SELECT * FROM
                     chain(a={SELECT * FROM foreach(row={SELECT OSPath FROM Artifact.Generic.Utils.FetchBinary(ToolName="SBECmd", IsExecutable=TRUE)},
                              query={SELECT * FROM foreach(row=UserProfiles,
                              query={SELECT *, Name FROM execve(argv=[OSPath, "-d", Directory, "--csv",
                                     tempfolder + "\\" + Name, "--dedupe"])}
         )})}
         ,
                      b={SELECT * FROM foreach(row={SELECT OSPath FROM glob(globs="**\\Deduplicated.csv", root=tempfolder)
                        },
                        query={SELECT if(condition=LastInteracted,
                                      then=LastInteracted,
                                      else=LastWriteTime) AS EventTime,
                                      hostname[0].Hostname AS Host,
                                      split(string=OSPath, sep="\\\\")[5] AS UserAccount,
                                      AbsolutePath AS Path,
                                      "N/A" AS FileSize,
                                      "Shellbags" AS Artifact,
                                      "Key_Modification" AS Time_Type,
                                      SourceFile AS ArtifactSource,
                                      dict(`Value`=Value,
                                           `ChildBags`=ChildBags,
                                           `CreatedOn`=CreatedOn,
                                           `ModifiedOn`=ModifiedOn,
                                           `FirstInteracted`=FirstInteracted,
                                           `MRUPosition`=MRUPosition,
                                           `HasExplored`=HasExplored) AS Additional_Info
                                      FROM parse_csv(filename=OSPath)
         })}
         )}
         )
       WHERE UserAccount =~ UserAccountRegex
        AND EventTime > DateAfterTime
             AND EventTime < DateBeforeTime

      
  - name: Browser Access Nirsoft
    description: Parses Windows Browsers to extract evidence of local data access via Nirsoft's NirsoftBrowserViewer.
    query: |
       LET hostname <= SELECT Hostname
         FROM info()
       
       LET DateAfterTime <= if(condition=DateAfter,
                               then=timestamp(epoch=DateAfter),
                               else=timestamp(epoch="1600-01-01"))
       
       LET DateBeforeTime <= if(condition=DateBefore,
                                then=timestamp(epoch=DateBefore),
                                else=timestamp(epoch="2200-01-01"))
       
       SELECT *
       FROM if(condition=BrowserAccessNirsoft,
               then={
           SELECT timestamp_format(time=`Visit Time`, format="RFC3339") AS EventTime,
                  hostname[0].Hostname AS Host,
                  `User Profile` AS UserAccount,
                  if(condition=URL =~ "file:///",
                     then=split(string=URL, sep_string="file:///")[1]) AS Path,
                  'N/A' AS FileSize,
                  `Web Brower` + "Access" AS Artifact,
                  "Visit Time" AS Time_Type,
                  `History File` AS ArtifactSource,
                  dict(`Visit Count`=`Visit Count`,
                       `Visit Type`=`Visit Type`,
                       `Visit Duration`=`Visit Duration`) AS Additional_Info
           FROM Artifact.Windows.Applications.NirsoftBrowserViewer(PARSE_TZ='GMT')
         })
       WHERE UserAccount =~ UserAccountRegex
        AND EventTime > DateAfterTime
             AND EventTime < DateBeforeTime
                  AND Path =~ ".+"

    
  - name: Browser Access SQLiteHunter
    description: Parses Windows Browsers to extract evidence of local data access.
    query: |
       LET hostname <= SELECT Hostname
         FROM info()
       
       LET DateAfterTime <= if(condition=DateAfter,
                               then=timestamp(epoch=DateAfter),
                               else=timestamp(epoch="1600-01-01"))
       
       LET DateBeforeTime <= if(condition=DateBefore,
                                then=timestamp(epoch=DateBefore),
                                else=timestamp(epoch="2200-01-01"))
       
       LET Chromium <= SELECT
           VisitTime AS EventTime,
           hostname[0].Hostname AS Host,
           split(string=OSPath, sep="\\\\")[2] AS UserAccount,
           if(condition=URL =~ "file:///",
              then=split(string=URL, sep_string="file:///")[1]) AS Path,
           'N/A' AS FileSize,
           "Chromium Browser access" AS Artifact,
           "Visit Time" AS Time_Type,
           OSPath AS ArtifactSource,
           dict(`VisitCount`=`VisitCount`,
                `TypedCount`=`TypedCount`,
                `VisitDurationInSeconds`=`VisitDurationInSeconds`,
                `Hidden`=`Hidden`) AS Additional_Info
         FROM Artifact.Generic.Forensic.SQLiteHunter(
           source='Chromium Browser History_Visits')
       
       LET FireFox <= SELECT
           LastVisitDate AS EventTime,
           hostname[0].Hostname AS Host,
           split(string=OSPath, sep="\\\\")[2] AS UserAccount,
           if(condition=URL =~ "file:///",
              then=split(string=URL, sep_string="file:///")[1]) AS Path,
           'N/A' AS FileSize,
           "FireFox Browser access" AS Artifact,
           "Visit Time" AS Time_Type,
           OSPath AS ArtifactSource,
           dict(`VisitCount`=`VisitCount`,
                `Title`=`Title`,
                `Typed`=`Typed`,
                `Hidden`=`Hidden`) AS Additional_Info
         FROM Artifact.Generic.Forensic.SQLiteHunter(source='Firefox Places_History')
       
       LET Edge <= SELECT
           `Last Visited Time` AS EventTime,
           hostname[0].Hostname AS Host,
           split(string=OSPath, sep="\\\\")[2] AS UserAccount,
           if(condition=URL =~ "file:///",
              then=split(string=URL, sep_string="file:///")[1]) AS Path,
           'N/A' AS FileSize,
           "Edge Browser access" AS Artifact,
           "Visit Time" AS Time_Type,
           OSPath AS ArtifactSource,
           dict(`VisitCount`=`VisitCount`, `Title`=`Title`) AS Additional_Info
         FROM Artifact.Generic.Forensic.SQLiteHunter(
           source='Edge Browser Navigation History_Navigation History')
       
       SELECT *
       FROM chain(a={
           SELECT *
           FROM Chromium
         },
                  b={
           SELECT *
           FROM FireFox
         },
                  c={
           SELECT *
           FROM Edge
         })
       WHERE UserAccount =~ UserAccountRegex
        AND EventTime > DateAfterTime
             AND EventTime < DateBeforeTime
                  AND Path =~ ".+"

  - name: WinSCP Remote Path
    description: Parses WinScp RemoteTarget registry key to extract evidence of data access.
    query: |
       LET hostname <= SELECT Hostname
         FROM info()
       
       LET DateAfterTime <= if(condition=DateAfter,
                               then=timestamp(epoch=DateAfter),
                               else=timestamp(epoch="1600-01-01"))
       
       LET DateBeforeTime <= if(condition=DateBefore,
                                then=timestamp(epoch=DateBefore),
                                else=timestamp(epoch="2200-01-01"))
       
       SELECT Mtime AS EventTime,
              hostname[0].Hostname AS Host,
              Username AS UserAccount,
              regex_replace(source=Data.value, re='%5C', replace='/') AS Path,
              'N/A' AS FileSize,
              "WinSCP Remote Path" AS Artifact,
              "Key_Modification" AS Time_Type,
              OSPath AS ArtifactSource,
              dict(Directory=`Directory`) AS Additional_Info
       FROM Artifact.Windows.Registry.NTUser(
         KeyGlob="Software\\Martin Prikryl\\WinSCP 2\\Configuration\\History\\RemoteTarget\\*")
       WHERE UserAccount =~ UserAccountRegex
        AND EventTime > DateAfterTime
             AND EventTime < DateBeforeTime

  - name: MRU
    description: Parses RecentDocs registry keys to extract evidence of data access.
    query: |
       LET hostname <= SELECT Hostname
         FROM info()
       
       LET DateAfterTime <= if(condition=DateAfter,
                               then=timestamp(epoch=DateAfter),
                               else=timestamp(epoch="1600-01-01"))
       
       LET DateBeforeTime <= if(condition=DateBefore,
                                then=timestamp(epoch=DateBefore),
                                else=timestamp(epoch="2200-01-01"))
       
       SELECT *
       FROM if(condition=RecentDocsMRU,
               then={
           SELECT LastWriteTime AS EventTime,
                  hostname[0].Hostname AS Host,
                  Username AS UserAccount,
                  MruEntries AS Path,
                  'N/A' AS FileSize,
                  "MRU" AS Artifact,
                  "Key_Modification" AS Time_Type,
                  HiveName AS ArtifactSource,
                  dict(Key=`Key`, UUID=`UUID`) AS Additional_Info
           FROM Artifact.Windows.Registry.RecentDocs()
         })
       WHERE UserAccount =~ UserAccountRegex
        AND EventTime > DateAfterTime
             AND EventTime < DateBeforeTime