Approved

This query is one you are recommended to read the FULL content of the post to use. It is not simply a copy and paste query, like others in the forum. It is valuable in the right situation.

Live Discover Query - General IT queries

Hi,

I have been looking at Live Discover and like the look of it. I am not an expert in Threat Hunting, but I was hoping I could use Live Discover to help me with my day to day IT tasks. I was thinking along the lines of the following.

  • 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
  • User logged on with admin rights
  • 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
  • When did the user last use a certain application. I could remove it, if they weren't using it

I am not sure where to start. Could someone please give me some guidance.

Best wishes Michael 

  • Thanks for helping others Jak.. Great to have you on the board
  • Thanks Jak, I will take a look at those. As for out of day, I was thinking if Windows Update returns any update available it is not up to date. I assume if I know the path to a certain application (QuickTime) I could query for that. When I mean use I was assuming the main exe. Let's say Visio. User says he uses it "all the time". I want to find out if that is true and if not I will have the license back :)
  • 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