Live Discover Query - General IT queries

Machine is NOT fully patched. I know I can look for a specific missing patch, but would like to list all machines that are out of date I guess you've found the patches table: select hotfix_id, caption, description, installed_by, installed_on from patches; +-----------+--------------------------------------------+-----------------+---------------------+--------------+ | hotfix_id | caption                                    | description     | installed_by        | installed_on | +-----------+--------------------------------------------+-----------------+---------------------+--------------+ | KB4552925 | support.microsoft.com/ | Update          | NT AUTHORITY\SYSTEM | 5/14/2020    | | KB4534170 | support.microsoft.com/ | Update          | NT AUTHORITY\SYSTEM | 1/16/2020    | | KB4537759 | support.microsoft.com/ | Security Update | NT AUTHORITY\SYSTEM | 2/15/2020    | | KB4545706 | support.microsoft.com/ | Security Update | NT AUTHORITY\SYSTEM | 3/13/2020    | | KB4557968 | support.microsoft.com/ | Security Update | NT AUTHORITY\SYSTEM | 5/14/2020    | | KB4556803 | support.microsoft.com/ | Update          | NT AUTHORITY\SYSTEM | 5/14/2020    | +-----------+--------------------------------------------+-----------------+---------------------+--------------+ Out of date is a tricky one as what is the authority on up to date?  If it's your own update source.  I might have to do some Googling on if such a state is exposed via the registry and therefore queryable with Live Query.  I'm sure using the API to interact with the Windows Update client this state could be determined but that's not going to help us. User logged on with admin rights This might help you get going: https://community.sophos.com/intercept-x-endpoint/i/query-forum/live-discover-query---uac-check-and-no-need-to-re-invent-the-wheel  Machines with X software installed. QuickTime would be a good example in my case  Retrieve software version. I was thinking about Acrobat DC as Application Control can only block DC as a whole, not a certain version. I need to find all the out of date DC This might cover these: https://community.sophos.com/intercept-x-endpoint/i/query-forum/live-discover-query---software-version-check might be worth a read. When did the user last use a certain application I could remove it, if they weren't using it I guess this is tricky, what does it mean to "use" an application might depend on the application.  If it's using the main EXE for example then that could be achieved if you know what to check for.  The sophos_process_journal table might be of help to get processes over time.  select datetime(processstarttime,'unixepoch') ProcessStartTime, cmdline from sophos_process_journal where cmdline like '%wbem%' and processstarttime > strftime('%s','now','-48 hours') ; Might help as a starting point. I.e get a list of processes where the cmdline contains wbem that have run in the last 48 hours Regards, Jak

Machine is NOT fully patched. I know I can look for a specific missing patch, but would like to list all machines that are out of date I guess you've found the patches table: select hotfix_id, caption, description, installed_by, installed_on from patches; +-----------+--------------------------------------------+-----------------+---------------------+--------------+ | hotfix_id | caption                                    | description     | installed_by        | installed_on | +-----------+--------------------------------------------+-----------------+---------------------+--------------+ | KB4552925 | support.microsoft.com/ | Update          | NT AUTHORITY\SYSTEM | 5/14/2020    | | KB4534170 | support.microsoft.com/ | Update          | NT AUTHORITY\SYSTEM | 1/16/2020    | | KB4537759 | support.microsoft.com/ | Security Update | NT AUTHORITY\SYSTEM | 2/15/2020    | | KB4545706 | support.microsoft.com/ | Security Update | NT AUTHORITY\SYSTEM | 3/13/2020    | | KB4557968 | support.microsoft.com/ | Security Update | NT AUTHORITY\SYSTEM | 5/14/2020    | | KB4556803 | support.microsoft.com/ | Update          | NT AUTHORITY\SYSTEM | 5/14/2020    | +-----------+--------------------------------------------+-----------------+---------------------+--------------+ Out of date is a tricky one as what is the authority on up to date?  If it's your own update source.  I might have to do some Googling on if such a state is exposed via the registry and therefore queryable with Live Query.  I'm sure using the API to interact with the Windows Update client this state could be determined but that's not going to help us. User logged on with admin rights This might help you get going: https://community.sophos.com/intercept-x-endpoint/i/query-forum/live-discover-query---uac-check-and-no-need-to-re-invent-the-wheel  Machines with X software installed. QuickTime would be a good example in my case  Retrieve software version. I was thinking about Acrobat DC as Application Control can only block DC as a whole, not a certain version. I need to find all the out of date DC This might cover these: https://community.sophos.com/intercept-x-endpoint/i/query-forum/live-discover-query---software-version-check might be worth a read. When did the user last use a certain application I could remove it, if they weren't using it I guess this is tricky, what does it mean to "use" an application might depend on the application.  If it's using the main EXE for example then that could be achieved if you know what to check for.  The sophos_process_journal table might be of help to get processes over time.  select datetime(processstarttime,'unixepoch') ProcessStartTime, cmdline from sophos_process_journal where cmdline like '%wbem%' and processstarttime > strftime('%s','now','-48 hours') ; Might help as a starting point. I.e get a list of processes where the cmdline contains wbem that have run in the last 48 hours Regards, Jak