Sorry for the slightly confusing topic title - is it possible to structure an IDR query to find systems, IP addresses etc where an associated event does not exist in a given time range but where other events do exist for the system, IP address?
For example, if we think all our systems should be running MyAppUpdate.exe every hour (which would show in Endpoint Agent >> Process Start logs), is it possible to create a query to find all systems that have Process Start events in the last hour but don’t have a MyAppUpdate.exe event?
Another example would be to show all active source IP addresses in a firewall log that haven’t connected to a specific destination IP in a particular time.
In SQL this would done using a statement something like: SELECT system FROM logs WHERE system NOT IN (SELECT system FROM logs WHERE process = MyAppUpdate.exe)
This will be extremely difficult, due to the fact that the system is based on querying where logs do exist, whereas its going to be nearly impossible (in my opinion) to query for something in a system where it does not exist. Perhaps you could take that query showing all devices that have run the MyAppUpdate process, and finding the Delta between all devices on your network minus the device you show as running it.
Thanks mblough, that’s the same conclusion i have come too unfortunately. I’ve been exporting the data out to CSV as looking for the differences as you suggest, but I was hoping to find some way to automate it.
Hi David, The desired outcome is that we’re trying to find machines that haven’t run an application in a fixed timeframe, but that have run other other programs (i.e. have been active).
The use case for this is that we have multiple applications that must be installed on each system (such as endpoint security agents, asset managements etc). At the moment we’re manually exporting lists from each system and cross-referencing them in Excel to find systems where an application is missing. We’d like a way to at least partially automate some of these to make things more expedient so am looking into ways we could detect active systems that arent’ showing these systems launching expected processes.
Do you have IVM as well? That would make your life so much easier… makes installed software so much more simple and makes it easy to gather reports for devices that are missing critical software.
Yes, we have insightVM and it solves most problems. However, knowing the software is installed isn’t enough - we have to know it’s actually running and doing what it’s supposed too. Also it raises the chicken and egg issue of how do we know that all our systems have the insight agent installed and running?
If a machine is online, you will see it reporting in to the IVM platform every 6 hours ( although that is configurable, I believe). So, we have catch-all asset groups for devices with a last scan date of “more than x days ago” to investigate devices that were possibly refreshed, or possibly have corrupted agents.
Also, if you could, would you mind describing the problem you’re trying to solve here? Are you seeing habitual issues with Agents being installed and not working? Is this a report that is necessary for an audit? Just trying to understand the precedence for this type of report.
Unfortunately aside from scripting something to automate querying and doing a diff check on the outputs what you are describing couldn’t be achieved in log search natively.
Hi mblough, David has already confirmed what i’m trying to do can’t be achieved in insightVM but I’ll describe the problem here, just in case there are any insights (pun intended) into how else i can solve this issue (even if not R7 related). I’ll simplify the actual issue but the basic concept is that we have 4500 workstations that should be in AD, have the R7 agent installed, have an asset management agent installed, have an endpoint security app installed and so on. However, no single system can be assumed to be GOD and contain all systems.
Most systems should have be in AD and have all the apps\agents installed - I want a way to identify workstations that are missing from one or more of these to allow us to mitigate the issues. The way I currently do it is exporting lists of each into Excel, creating a master list and then doing vlookups etc to work out what is missing.
This works but is time-consuming and is therefore not done frequently enough. We’d like to, for example, identify any systems operational but without the endpoint security app installed much more quickly than we currently are, ideally without having to make it too technical or complicated.
I’ve stumbled across a partial solution to this while working on something completely different , so will add this update in case it helps anyone else. It still requires an export to Excel but is a load faster than what i have been doing.
Assumptions are made that you have the endpoint agent sending process activity into IDR and that you can identify a process that almost certainly should be running on every device within the period being checked (such as a normal Windows exe).
Create a query that looks for this ‘always on’ process or updateprocessA or updateprocessB etc, group by hostname and then limit by the number devices expected. For example:
where("process.name" = "commonprocess.exe" or "process.name" = "UpdateA.exe" or "process.name" = "UpdateB.exe" or "process.name" = "UpdateC.exe",loose) groupby("hostname", "process.name") limit(6000)
You can then use the resulting CSV in Excel to pivot the data to find systems that aren’t updating ,so are either broken or don’t have the agent.