Before I get into my failed script, please allow me to explain what Im attempting to do. We are looking for windows and Macintosh devices with cortex xdr agent NOT installed. To do this we installed the cortex DHCP log collector. It is reporting its findings into asset manager. The problem is that when an IP changes, the agent doesnt show this in the endpoint manager and as a result the cortex asset manager reports devices as without the agent when they actually do (cortex please fix this). So we have hundreds of false positives. So, what we have to do is when DHCP logger reports a device with a new IP we have to look in endpoint manager for the name of the device. If we find it, the agent is installed, if we dont we need to audit the machine and install the agent if need be. Now, I could export both of these into a TSV, bring them into excel and do the work (and I have),.. however, we want this to be a report in the portal and we want to send alerts.. So, went into the xql query builder and tried a bunch of stuff.. Here is my lastest, which shows no errors in the edit box but the report finds nothing.. dataset = microsoft_dhcp_raw //| filter hostName != "" | alter hn1 = split(hostName ,".") / dedup hostName //| dedup ipAddress //| union (dataset = endpoints) //| filter endpoint_name = hn1 |join conflict_strategy = both type = inner (dataset = endpoints ) as EP EP.endpoint_name = hn1 | fields ipAddress,ip_address, hostName,hn1,endpoint_name When this didnt work, I figured it had something to do with my alter statement and used an IP which should surely have a match: dataset = microsoft_dhcp_raw //| filter hostName != "" | alter hn1 = split(hostName ,".") |// dedup hostName | dedup ipAddress | union (dataset = endpoints) //| filter endpoint_name = hn1 |join conflict_strategy = both type = inner (dataset = endpoints ) as EP EP.ip_address = ipAddress | fields ipAddress,ip_address, hostName,hn1,endpoint_name As you can see I played around with UNION as I have no idea what Im doing. I left my commented out statements just to show what Ive played with. Also, this would get me matches if it worked.. so how would I show no matches found for the join? Both show no errors in the editor but when I run it I just get: ERROR: FAILED TO RUN with no result. Im probably going about this all wrong, so any help would be appreciated.
... View more