Notifications are a feature added in Windows 8 and 10, displaying text and images either in the start menu tiles or on the right side of the screen. These notifications can hold interesting data such as popup messages or text snippets from applications. This may allow you to recover data even when its source has been deleted. The notification database storing this information is located here:
%APPDATA%\Local\Microsoft\Windows\Notifications\wpndatabase.db
Related pictures are stored in this subfolder:
%APPDATA%\Local\Microsoft\Windows\Notifications\wpnidm\
On current Windows versions the database is in an SQLite format and can be inspected by any SQLite tool. For the Windows 10 notification database prior to Windows 10 build 1607 (Aug. 2016) have a look at Yogesh Khatri’s blog post.
The database contains several tables from which the Notification and NotificationHandler seems to be the most interesting:
Notification table:
- HandlerId: That’s the reference to the RecordId NotificationHandler table. Allows to look up the application which created the notification.
- Payload: This is the actual content of the notification. Here you might find interesting information.
- ArrivalTime: The time when the notification was received.
- ExpiryTime: The time when the notification will be deleted from the database.
The time stamps in this notification table are in the Windows filetime format. You can use this CyberChef recipe to convert them to a human readable format.
NotificationHandler table:
- PrimaryId: References via RecordId to the HandlerId of the Notification table. Shows the application name which created a notification.
Convert to CSV
Sometimes it’s useful to use a tool like Excel to inspect and sort the data. The following is a small script which takes a wpndatabase.db as input and returns a tab-separated file with the most important information.
#!/usr/bin/env python3
import sqlite3
import csv
import sys
def generateCSV(csr, outputfilename):
csr.execute("""SELECT n.'Order', n.Id, n.Type, nh.PrimaryId AS HandlerPrimaryId, nh.CreatedTime AS HandlerCreatedTime, nh.ModifiedTime AS HandlerModifiedTime, n.Payload,
CASE WHEN n.ExpiryTime != 0 THEN datetime((n.ExpiryTime/10000000)-11644473600, 'unixepoch') ELSE n.ExpiryTime END AS ExpiryTime,
CASE WHEN n.ArrivalTime != 0 THEN datetime((n.ArrivalTime/10000000)-11644473600, 'unixepoch') ELSE n.ArrivalTime END AS ArrivalTime
FROM Notification n
INNER JOIN NotificationHandler nh ON n.HandlerID = nh.RecordID""")
result = csr.fetchall()
with open(outputfilename, "w", newline='', encoding="utf-8") as f:
writer = csv.writer(f, delimiter='\t')
writer.writerow(list(map(lambda x: x[0], csr.description)))
for line in result:
lst = list(line)
# In case the payload should be a string, uncomment the next line
#lst[-3] = str(lst[-3], "UTF-8")
lst[-2] = "" if lst[-2] == 0 else lst[-2]
lst[-1] = "" if lst[-1] == 0 else lst[-1]
writer.writerow(lst)
def printMetainfo(csr):
csr.execute("SELECT * FROM Metadata")
result = csr.fetchall()
print("""\n
Notifications - Metadata
------------------------
""")
for line in result:
print("\t" + line[0] + ": " + str(line[1]))
print()
if __name__ == "__main__":
if len(sys.argv) == 3:
conn = sqlite3.connect(sys.argv[1])
csr = conn.cursor()
printMetainfo(csr)
generateCSV(csr, sys.argv[2])
else:
print("""\n
Windows 10 Notifications to CSV
-------------------------------
This script processes the wpndatabase.db notifications database from Windows 10
and gives a truncated, tab-delimited file as output.
File location: %APPDATA%\Local\Microsoft\Windows\\Notifications\wpndatabase.db
Usage:
WPNtoCSV.py inputDB outputCSV
Example:
WPNtoCSV.py wpndatabase.db notifications.csv
""")