How do you join tables on a primary key? I'm not seeing any primary index keys in the tables. For example, https://fleetdm.com/tables/plist I could join with a value, but that's less precise and reliable (and generally useful) than joining on a shared key (across tables) like the hostname or serial number.

SELECT pa.value deadline, pb.value os_version
FROM plist pa
INNER JOIN plist pb
    ON split(pa.subkey,'\/',2) = split(pb.subkey,'\/',2)
WHERE pa.path = '/var/db/softwareupdate/SoftwareUpdateDDMStatePersistence.plist'
    AND pb.path = '/var/db/softwareupdate/SoftwareUpdateDDMStatePersistence.plist'
    AND pa.subkey LIKE '%TargetLocalDateTime%'
    AND pb.subkey LIKE '%TargetOSVersion%'

This example is a table self-join (where the indexed primary key would be the best to join on) but even for regular INNER JOINs or LEFT JOINs on other tables, a key to join on would be helpful.

could you provide a copy of that actual plist?

Its prefs would be something like this:

{
    SUCorePersistedStateContentsType = SoftwareUpdateCorePersistedStateFile;
    SUCorePersistedStateCoreFields =     {
    };
    SUCorePersistedStateCoreSecureCodedObjectsFields =     {
    };
    SUCorePersistedStateCoreVersion = "2.1.0";
    SUCorePersistedStatePolicyFields =     {
        Declarations =         {
            "com.apple.RemoteManagement.SoftwareUpdateExtension/ABCDEFG123-1234-ABCD-5678-A1B2C3D4E5:abcdeFghiJKLMNOpqrstUVwxyz1234567890.abcdeFghiJKLMNOpqrstUVwxyz1234567890=" =             {
                RMStoreDeclarationKey = "com.apple.RemoteManagement.SoftwareUpdateExtension/ABCDEFG123-1234-ABCD-5678-A1B2C3D4E5:abcdeFghiJKLMNOpqrstUVwxyz1234567890.abcdeFghiJKLMNOpqrstUVwxyz1234567890=";
                TargetLocalDateTime = "2023-11-14T00:00:00";
                TargetOSVersion = "14.1.1";
            };
        };
    };
    SUCorePersistedStatePolicySecureCodedObjectsFields =     {
    };
    SUCorePersistedStatePolicyVersion = "1.0";
}

More documentation here: https://www.sudoade.com/ddm-software-update-logs/

Hi @aysiu! It sounds like your concern is making sure that the data returned is all associated with the same host. Since the queries are run on the host itself, using only the data available for that host, there’s no need to provide that unique key.

i can't speak to your specific implementation but in house we've used fleed for years and rely on fleet decorator queries to ensure that every event has the same fields (host_hardware_serial, hostname, timestamp) etc. maybe this is what you are after?

Since the queries are run on the host itself, using only the data available for that host, there’s no need to provide that unique key.

That's fine if you query a single table, but when you're joining tables, you have to join on a common key. Do you have examples on how you recommend joining tables? How would you have done the self-join I posted above without using that

split()

workaround?

i can't speak to your specific implementation but in house we've used fleed for years and rely on fleet decorator queries to ensure that every event has the same fields

I'm not familiar with decorator queries. Can you link some documentation that helped you?

agent configuration options in fleet UI https://fleetdm.com/docs/configuration/agent-configuration

Assuming that the plist file would only contain one of those declarations, I'd skip the

ON

entirely.

SELECT pa.value deadline, pb.value os_version
FROM plist pa, plist pb
WHERE pa.path = '/var/db/softwareupdate/SoftwareUpdateDDMStatePersistence.plist'
AND pb.path = '/var/db/softwareupdate/SoftwareUpdateDDMStatePersistence.plist'
AND pa.subkey LIKE '%TargetLocalDateTime%'
AND pb.subkey LIKE '%TargetOSVersion%'

I think that the key piece of information here is that a shared key like hostname or serial number isn't really needed because it would always match across every row in every table because the osquery db exists locally on each host.

I disagree here. Let's say I want to look at the values from that query (which is on the plist table), but I also want to get corresponding values (same hosts) from the os_version table. Can you give an example of how you do that without joining on the hostname or serial number?

i feel like you are trying to force something, if you just want results from multiple tables on the same host with a single query maybe a UNION of multiple queries? but the columns won't match up so you'd probably have to JOIN anyway on some column anyway to make it all work. I don't know SQL expertly but this seems like an anti-pattern to me.

> i feel like you are trying to force something, if you just want results from multiple tables on the same host with a single query maybe a UNION of multiple queries? If I run a query like

SELECT pa.value deadline, pb.value enforced_os_version, NULL current_version
FROM plist pa
INNER JOIN plist pb
    ON split(pa.subkey,'\/',2) = split(pb.subkey,'\/',2)
WHERE pa.path = '/var/db/softwareupdate/SoftwareUpdateDDMStatePersistence.plist'
    AND pb.path = '/var/db/softwareupdate/SoftwareUpdateDDMStatePersistence.plist'
    AND pa.subkey LIKE '%TargetLocalDateTime%'
    AND pb.subkey LIKE '%TargetOSVersion%'
UNION
SELECT NULL deadline, NULL enforced_os_version, version current_version
FROM os_version

I just get results from the first query and then results from the second query (intermixed, of course, but all on separate lines). That's how a UNION works. I don't want a UNION. I want a join. I want to be able to have the current OS version next to the enforced OS version and the deadline for a single machine. I don't think I'm asking for something wild here. Being able to join tables is a fairly standard SQL/MySQL query practice.

right you can do that sure. the issue is trying to do these joins in the context of osquery/fleet is not possible because the tables do not have those shared keys, they are an abstraction that gets added to the "event" that is emitted as a result. so these joins are totally easy to do off platform. we use a data lake, we have decorators that guarantee additional fields in every single event, plus the configurable host_identifier value. so we can join all day because every single event/result in our datalake has hostname, hardware_serial, uuid etc. doing this in the context of osquery is not possible afaik

Give this one a try:

SELECT pa.value deadline, pb.value enforced_os_version, ov.current_version
FROM plist pa
INNER JOIN plist pb
    ON split(pa.subkey,'\/',2) = split(pb.subkey,'\/',2)
WHERE pa.path = '/var/db/softwareupdate/SoftwareUpdateDDMStatePersistence.plist'
    AND pb.path = '/var/db/softwareupdate/SoftwareUpdateDDMStatePersistence.plist'
    AND pa.subkey LIKE '%TargetLocalDateTime%'
    AND pb.subkey LIKE '%TargetOSVersion%'
JOIN os_version ov

your last example is how i did it as well

NIce. That's really readable.