Use Sysmon DNS data for incident response

Sysmon DNS

Recent versions of Sysmon support the logging of DNS queries. This is done via event ID 22 in Applications and Services Log > Microsoft > Windows > Sysmon Operational.

To enable DNS logging, you need to include the section DnsQuery in your Sysmon configuration file. For example via

<Sysmon schemaversion="4.21">
  <DnsQuery onmatch="exclude" />

Note that enabling DNS queries can be noisy. It’s best to apply filtering as proposed by the SwiftOnSecurity sysmon config file and, additionally, filter out the commonly used -internal- hostnames of your environment.

Now, once enabled, what can you do with this data? It’s a great resource for incident response but you can also use it to build a Passive DNS database. If you don’t know Passive DNS, read Passive DNS for Incident Response or
Use Passive DNS to Inform Your Incident Response

Collect Sysmon DNS logs (in SQLite)

My preferred setup for Passive DNS is Bro (Zeek) monitoring the network traffic, storing DNS queries in a SQLite database and then using MISP data to check IP and domain records. To have a similar setup for Sysmon I needed to extend the SQLite database schema with the fields hostname (Windows host doing the queries), status (query status field in the event log) and image (Windows application doing the DNS query). My processing chain then consisted of

  1. Windows clients logging DNS queries via Sysmon;
  2. Events are sent to a central logger via Log Forwarding (you can also use Winlogbeat);
  3. The event logs are daily parsed via a Powershell script started from scheduled job;
  4. The results are stored in a -shared- SQLite database. This database is also used by Bro (INSERT only);
  5. I then have a daily job to verify if the list of queried domains were seen in MISP.

The system that does the parsing must have the SQLite driver for Windows installed. You also need to place Get-WinEventData.ps1 in the same directory as from where you launch the script.

Powershell script: passivedns.ps1

Disclaimer: the current script isn’t complete, it tracks the DNS queries but the correct firstseen, lastseen and count isn’t implemented yet. For now, firstseen is set to the time of the creation of the Windows event.

Building blocks

The script can be found on Github passivedns.ps1. It consists of a couple of blocks

  • createDataBase : this function creates the database, if it doesn’t exist yet. For my setup this was on a shared location where Bro could also write to;
  • queryDatabase : a test function to query the content of the SQLite database. You can also use a graphical browser as DB Browser for SQLite;
  • insertDatabase : function to add a row to the database;
  • Log-It : helper function for logging;
  • fetchEvents : the main function, this fetches the events, does some conversion and writes it to SQLite.


The configuration is inline. You need to change these parameters

  • Add-Type -Path : the path to the SQLite dll (System.Data.SQLite.dll);
  • $LogPath : (in Log-It), the path for the log file;
  • $Today : (in fetchEvents), the timestamp to use to query the events. I have a daily job, so I need to fetch the events -1 day.
  • $DBPath : the path to the SQLite database.

    Running the script

    You can run the script from Powershell

    PS C:\Users\Administrator> C:\Users\Administrator\passivedns.ps1
    DB Exists: Ok

  • One thought on “Use Sysmon DNS data for incident response

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.