https://github.com/osquery/osquery logo
Powered by
Title
f

fritz

11/18/2020, 2:40 PM
Good morning @Nakul, You can use the 
magic
table or the 
signature
table to discern architecture, both of these operations are relatively expensive (signature being much worse) Example of magic: 
WITH 
-- retrieve paths of app binaries and get magic data
app_paths AS (
  SELECT 
    REGEX_SPLIT(path, '/Contents/', 0) AS path, 
    data AS arch 
  FROM magic AS m 
  WHERE m.path IN (
    SELECT file.path 
    FROM file JOIN apps 
    WHERE file.path LIKE apps.path || "/Contents/MacOS/%" 
    AND file.type = 'regular') 
-- omit files that are not binaries
    AND mime_encoding = 'binary' 
-- omit symlinks
  AND mime_type != 'inode/symlink'), 
-- condense down result set with group by
reduce_set AS (
  SELECT path, arch 
  FROM app_paths 
  GROUP BY path, arch) 
-- rejoin on apps
SELECT name, arch FROM apps a CROSS JOIN reduce_set rs on a.path = rs.path;

+--------------------------------------------+---------------------------------+
| name                                       | arch                            |
+--------------------------------------------+---------------------------------+
| 1Password 7.app                            | Mach-O 64-bit executable x86_64 |
| A Better Finder Rename <http://10.app|10.app>              | Mach-O 64-bit executable x86_64 |
| Acrobat <http://Distiller.app|Distiller.app>                      | Mach-O 64-bit executable x86_64 |
| Adobe <http://Acrobat.app|Acrobat.app>                          | Mach-O 64-bit executable x86_64 |
| Adobe After Effects <http://2020.app|2020.app>               | Mach-O 64-bit executable x86_64 |
| Adobe Audition <http://2020.app|2020.app>                    | Mach-O 64-bit executable x86_64 |
| Adobe DNG <http://Converter.app|Converter.app>                    | Mach-O 64-bit executable x86_64 |
| Adobe <http://Illustrator.app|Illustrator.app>                      | Mach-O 64-bit executable x86_64 |
| Analyze <http://Documents.app|Documents.app>                      | Mach-O 64-bit executable x86_64 |
| Make <http://Calendar.app|Calendar.app>                          | Mach-O 64-bit executable x86_64 |
| Contact <http://Sheets.app|Sheets.app>                         | Mach-O 64-bit executable x86_64 |
| Web <http://Gallery.app|Gallery.app>                            | Mach-O 64-bit executable x86_64 |
| Adobe InDesign <http://2021.app|2021.app>                    | Mach-O 64-bit executable x86_64 |
| Adobe <http://Lightroom.app|Lightroom.app>                        | Mach-O 64-bit executable x86_64 |
| Adobe Media Encoder <http://2020.app|2020.app>               | Mach-O 64-bit executable x86_64 |
| Adobe Photoshop <http://2020.app|2020.app>                   | Mach-O 64-bit executable x86_64 |
| Adobe Premiere Pro <http://2020.app|2020.app>                | Mach-O 64-bit executable x86_64 |
| Alfred 3.app                               | Mach-O 64-bit executable x86_64 |
| <http://Atom.app|Atom.app>                                   | Mach-O 64-bit executable x86_64 |
| Backup and <http://Sync.app|Sync.app>                        | Mach-O 64-bit executable x86_64 |
| Capture One <http://10.app|10.app>                         | Mach-O 64-bit executable x86_64 |
| Capture One <http://12.app|12.app>                         | Mach-O 64-bit executable x86_64 |
| Capture One <http://20.app|20.app>                         | Mach-O 64-bit executable x86_64 |
| <http://Charles.app|Charles.app>                                | Mach-O 64-bit executable x86_64 |
| CorelDRAW Graphics Suite <http://2020.app|2020.app>          | Mach-O 64-bit executable x86_64 |
| Corel Font Manager <http://2020.app|2020.app>                | Mach-O 64-bit executable x86_64 |
| Corel PHOTO-PAINT <http://2020.app|2020.app>                 | Mach-O 64-bit executable x86_64 |
| CorelDRAW <http://2020.app|2020.app>                         | Mach-O 64-bit executable x86_64 |
| <http://CraftManager.app|CraftManager.app>                           | Mach-O 64-bit executable x86_64 |
bundle id:          Sketch (0xcad4)
class:              kLSBundleClassApplication (0x2)
container:          / (0x4)
mount state:        mounted
Mach-O UUIDs:       E20FFBBF-B5CD-3684-8582-4E314B2D09CC
sequenceNum:        51924
path:               /Applications/Sketch.app (0xeec8)
directory:          /Applications
name:               Sketch
displayName:        Sketch
localizedName:      "LSDefaultLocalizedValue" = "Sketch"
localizedShortName: "LSDefaultLocalizedValue" = "Sketch"
teamID:             WUGMZZ5K46
category:           public.app-category.graphics-design (0x2b78c)
identifier:         com.bohemiancoding.sketch3
version:            83088.0 ({length = 32, bytes = 0x90440100 00000000 00000000 00000000 ... 00000000 00000000 })
versionString:      83088
displayVersion:     57.1
mod date:           2019-08-15 13:17 (POSIX 1565889429)
reg date:           2020-11-06 09:43 (POSIX 1604673830)
type code:          'APPL' (4150504c)
creator code:       '????' (3f3f3f3f)
bundle flags:       has-display-name (0000000000000002)
plist flags:        has-custom-bindings (0000000000010000)
icon flags:         relative-icon-path (0000000000000001)
arch flags:         x86_64 (0000000000000008)
item flags:         package  application  container  native-app  scriptable  extension-hidden (000000000010088e)
Magnified:          capable  can-change (0000000000000005)
App Nap:            capable (0000000000000001)
eGPU:               capable  can-change (0000000000000005)
platform:           native
icons:              Contents/Resources/app.icns
executable:         Contents/MacOS/Sketch
inode:              247849832
exec inode:         247849849
min version:        10.13.4 ({length = 32, bytes = 0x0a000000 00000000 0d000000 00000000 ... 00000000 00000000 })
execSDK ver:        10.14 ({length = 32, bytes = 0x0a000000 00000000 0e000000 00000000 ... 00000000 00000000 })
mach min ver:       10.13.4 ({length = 32, bytes = 0x0a000000 00000000 0d000000 00000000 ... 00000000 00000000 })
plistCommon:        23 values (115416 (0x1c2d8))
                    {
                        CFBuildNumber = 109;
                        CFBundleDevelopmentRegion = English;
                        CFBundleDisplayName = Sketch;
                        CFBundleExecutable = Sketch;
                        CFBundleIconFile = "app.icns";
                        CFBundleIdentifier = "com.bohemiancoding.sketch3";
                        CFBundleInfoDictionaryVersion = "6.0";
                        CFBundleName = Sketch;
                        CFBundlePackageType = APPL;
                        CFBundleShortVersionString = "57.1";
                        CFBundleSignature = "????";
                        CFBundleSupportedPlatforms =     (
                            MacOSX
                        );
                        CFBundleVersion = 83088;
                        NSAppTransportSecurity =     {
                            NSAllowsArbitraryLoads = 1;
                            NSAllowsArbitraryLoadsInWebContent = 1;
                            NSAllowsLocalNetworking = 1;
                        };
                        NSAppleEventsUsageDescription = "This allows Sketch to communicate with the app.";
                        NSAppleScriptEnabled = 1;
                        NSHumanReadableCopyright = "\U00a9 2009-2019 Sketch B.V.";
                        NSMainNibFile = MainMenu;
                        NSPhotoLibraryUsageDescription = "You\U2019ll be able to browse and import your photos into Sketch.";
                        NSPrincipalClass = NSApplication;
                        NSSupportsAutomaticGraphicsSwitching = 1;
                        NSUbiquitousContainers =     {
                            "WUGMZZ5K46.com.bohemiancoding.sketch" =         {
                                NSUbiquitousContainerIsDocumentScopePublic = 1;
                                NSUbiquitousContainerName = Sketch;
                                NSUbiquitousContainerSupportedFolderLevels = Any;
                            };
                        };
                        NSUserActivityTypes =     (
                            "com.bohemiancoding.sketch.play.handoff"
                        );
                    }
library:            Contents/Library/
library items:      QuickLook/SketchQuicklook.qlgenerator/
activityTypes:      WUGMZZ5K46:com.bohemiancoding.sketch.play.handoff, NOTIFICATION#WUGMZZ5K46:com.bohemiancoding.sketch3, ua-9d1f73d2782cb4, pv-f23e896627771a
claimed UTIs:       com.bohemiancoding.sketch.plugin.bundle, com.adobe.pdf, public.image, public.webp, com.bohemiancoding.sketch.drawing.single, com.bohemiancoding.sketch.plugin, com.bohemiancoding.sketch.drawing, com.bohemiancoding.sketch.clouddrawing.single, com.adobe.encapsulated-postscript
claimed schemes:    sketch-nonappstore:, sketch-v57.1:, sketch:
entitlements:       7 values (115420 (0x1c2dc))
                    {
                        "com.apple.security.app-sandbox" = 0;
                        "com.apple.security.application-groups" =     (
                            "WUGMZZ5K46."
                        );
                        "com.apple.security.automation.apple-events" = 1;
                        "com.apple.security.cs.allow-jit" = 1;
                        "com.apple.security.cs.allow-unsigned-executable-memory" = 1;
                        "com.apple.security.cs.disable-library-validation" = 1;
                        "com.apple.security.personal-information.photos-library" = 1;
                    }
If someone was significantly motivated one path forward to improving the 
apps
table would be relying on data from 
lsregister
instead which contains an arch flag:
n

Nakul

11/19/2020, 3:35 AM
hello @fritz, thank you for your reply. Let me study the info you have given and get back. From an initial POV, magic table approach seems to be sufficient for me. But let me take a deeper look and get back. Thanks again for the information. Cheers
6 Views
#generalJoin Slack
Powered by