Parse The Appx Programs In Autopsy
Have you recently look at the Settings → Apps → Apps and Features and then compared it to the Program and Features found in the control panel? If you have you may have noticed there are more program that are listed in one but not the other.
As you can see from the above screen shots there are more installed programs based on the Settings → Apps → Apps and Features vs the Control Panel → Programs and Features.
Now if you have taken a test image of Windows 10, process it in Autopsy, run the recent activity ingest module, you should see a list of installed programs, does that list look like it is complete? It should look like the list from Control Panel → Program and Features. This list is based on information that is found in the registry. Now how do you get the list that looks like the one from Settings → Apps → Apps and Features? If the data is not stored in the registry where is it stored? To find that list of apps you need to look at the following file: /ProgramData/Microsoft/Windows/AppRepository/StateRepository-Machine.srd, you can also find the same file in the /ProgramData/Microsoft/Windows/AppRepository/Downlevel directory as well. This file is actually a SQLite database that holds information about all the Windows Universal Apps installed on the system.
When you open the SQLite database StateRepository-Machine.srd you will find that there are 68 tables in this database. Of those 68 tables we are going to look at only 4 of these tables. The tables are PackageUser, Package, PackageFamily, PackageLocation. The reason that I am using these tables is that I can recreate most of the same information that the powershell cmdlet get-AppxProgram does. The following SQL is used to create the data needed:
select distinct * from (Select packfam.name, packfam.publisher, packfam.publisherid, packuser.user, case Architecture when 0 then “X64” when 9 then “x86” when 11 then “Neutral” else Architecture end Architecture, pack.ResourceId, substr(pack.packageFullName, instr(pack.packageFullName, “_”) + 1, instr(substr(pack.packageFullName, instr(pack.packageFullName, “_”) + 1), “_”) — 1) version, packfam.packageFamilyname, pack.packageFullName, “??” isFramework, “??” PackageUserInformaton, “??” isResourcePakage, “??” IsBundle, “??” IsDevelopment, “??” Dependicies, “??” IsPartiallyStaged, case SignatureOrigin when 3 then “System” when 2 then “Store” else “Unknown” end SignatureKind, packuser.PackageStatus Status, datetime(substr(packuser.installTime,1,11) -11644473600, ‘unixepoch’), packloc.installedLocation from PackageUser packuser, package pack, packageFamily packfam, packageLocation packloc where packuser.package = pack._PackageId and pack.packageFamily = packfam._PackagefamilyId and packloc.package = pack._packageId and (pack.resourceId is null or pack.resourceId = “neutral”));
I apologize for the mess that the query looks like, there is no easy way to format the query and make it look nice in this medium. Now looking at the SQL you can see that there is quite a bit of information being pulled back that is not being used in the plugin. The reason I left the whole SQL statement intact was so if someone wanted to have the information and bring it into Autopsy they could. For this plugin I am only utilizing 2 columns, Name and Install Time, the reason for this is so it the data will populate the programs installed in extracted content and you do not need to search two (2) different areas to find all of the installed programs.
Within the SQL statement there are several columns that do not have any data for them, they are denoted with ??, this is data that I have not quite figured out yet but I am working on it. If someone wants to fill in this information please let me know or modify the python plugin.
You can find the plugin here. If you have any questions or comments please leave them below, or on my github repository or email at the email address in the plugin. Comments and suggestions good or bad welcome. Enjoy!
