SWQL Query Assistance - Reporting on SentinelOne Agent Installs

martian_monster

Greetings -

We are trying to generate a report that will use the Asset Inventory to let us know which nodes do not have SentinelOne installed on.  

I was able to take the Software Inventory report and configure it to report on the nodes that have the SentinelOne agent installed.  I was hoping it would be easy to invert the query and report on node that do not have Sentinel Agent installed.  

SELECT [data].[DisplayName] AS [DisplayName],[data].[InstanceSiteId] AS [InstanceSiteId]
FROM orion.nodes AS data
WHERE
((ISNULL( ([data].[AssetInventory].[Software].[Name]) , '')!='') AND ((([data].[AssetInventory].[Software].[Name]) LIKE '%'+('Sentinel Agent') + '%')))
WITH NOLOCK

We thought we got it to work but it is not reporting correctly 

SELECT

     n.NodeID,

     n.DisplayName,

     n.IPAddress,

     n.MachineType

FROM

     Orion.Nodes   n

LEFT JOIN

     Orion.AssetInventory.Software   s  

    ON  n.NodeID  =  s.NodeID  AND  s.Name  LIKE 'Sentinel Agent%'

WHERE

     s.Name  IS NULL

    AND  n.MachineType  LIKE '%Windows%'

Thanks! Dave

chad.every

I think the JOIN you have is incorrect. You actually don't need the JOIN at all since the Asset Inventory data is already linked in the Orion.Nodes table.

Try this query (I updated the name to 'SolarWinds Agent%' since I don't have Sentinel in my lab.

SELECT
    n.NodeID,
    n.DisplayName,
    n.IPAddress,
    n.MachineType,
    n.AssetInventory.Software.Name,
    n.AssetInventory.Software.Version
FROM Orion.Nodes n
WHERE 1=1
    AND n.AssetInventory.Software.Name LIKE 'SolarWinds Agent%'
    AND n.MachineType LIKE '%Windows%'