About nigsmi51

Incase anyone else has this issue, here is an XQL Query that will result in which DHCP Devices are not in the Cortex Endpoints Dataset dataset = microsoft_dhcp_raw | filter hostName != "" and ipAddress != "" //first few lines are same as OP | alter FormattedName = if (hostname contains ".domain.local",replace(hostname,".domain.local",""),hostname)//replace .domain.local with your domain when running | join conflict_strategy = left type = left (dataset = endpoints ) as ed ed.endpoint_name = FormattedName //left join ensures that all is returned from DHCP, and only matches from Endpoint | alter conditional = if(FormattedName = endpoint_name, 1, 0)//if there is a match, it returns 1, otherwise, 0 | fields FormattedName , endpoint_name, conditional | comp sum(conditional) as totalconnections by FormattedName // by summing on the conditional, if the sum is 0, that means there are 0 logs where DHCP matched with one of your endpoints | filter (totalconnections = 0) // if you changed this to >0, you will get all devices in DHCP that ARE matched in the Cortex List ... View more

Like mentioned in my previous reply, the sum method can be used to identify which Windows DHCP devices don't have Cortex, as shown below.   dataset = microsoft_dhcp_raw | filter hostName != "" and ipAddress != "" //first few lines are same as OP | alter FormattedName = if (hostname contains ".domain.local",replace(hostname,".domain.local",""),hostname)//replace .domain.local with your domain when running | join conflict_strategy = left type = left (dataset = endpoints ) as ed ed.endpoint_name = FormattedName //left join ensures that all is returned from DHCP, and only matches from Endpoint | alter conditional = if(FormattedName = endpoint_name, 1, 0)//if there is a match, it returns 1, otherwise, 0 | fields FormattedName , endpoint_name, conditional | comp sum(conditional) as totalconnections by FormattedName // by summing on the conditional, if the sum is 0, that means there are 0 logs where DHCP matched with one of your endpoints | filter (totalconnections = 0) // if you changed this to >0, you will get all devices in DHCP that ARE matched in the Cortex List   Just to mention, make sure you are retrieving enough data from DHCP for this to be accurate. You may need to adjust limits if you have enough DHCP logs that Cortex automatically stops too early. ... View more

Incase anyone else had the same issue, (needing to Not Match/AntiJoin in XQL), here is a way I did it without using Join. This also finishes OP's question, since the below query also shows which devices do NOT have an event, or when they do NOT have software installed:   Example for Restart event log NOT happening: dataset = xdr_data // Using the xdr dataset | alter conditional = if(action_evtlog_event_id = 1074,1,0) //if restart event happens, 1 is returned, anything else, then 2 is returned. Restart Event could be replaced with anything | fields agent_hostname as hostname, conditional as restartcount // Selecting the relevant fields | comp sum(restartcount) as totalrestarts by hostname // adds up and summarizes all "restartcount" values | filter (totalrestarts = 0) // if 0, that means that the sum of all restart events is 0 (ie. it never restarted)   Example for chrome NOT Installed: dataset = xdr_data // Using the xdr dataset | filter event_type = ENUM.PROCESS and event_sub_type = ENUM.PROCESS_START //makes sure we get program logs | alter conditional = if(action_process_image_name ~= "chrome",1,0)//filters names that include "chrome" | fields action_process_image_path as Process_Path, action_process_image_command_line as Process_CMD, action_process_os_pid as Process_PID, actor_process_image_path as Parent_Process, actor_process_command_line as Parent_CMD, agent_hostname as hostname, conditional as runcount // Selecting some relevant fields | comp sum(runcount) as totalruns by hostname //sums total runs by host | filter (totalruns = 0) // like before, change this to > if you want to see the opposite results   This shows how you check for machines that never had an event/program occur. You can replace Chrome.exe or the Event ID with anything.   Edit: As shown in my next reply, you can combine the above method with Join, and use that to identify items that don't match between 2 datasets (basically an Anti Join) ... View more