XQL query language question - removing duplicates from results

[kenlacrosse]

Just getting into querying the datasets in the Cortex Data Lake, good stuff and lots of fun.  With many of my queries I get tons of duplicate hostname results (example searching for all host running IIS - w3wp.exe - there are multiple processes running on a single host and consequently I get many rows of results for the same host) and I want to either eliminate the duplicates (using the agent_hostname column) from the results set.  Anyone know of a way to accomplish this?

Thanks

[lucas85moose · Accepted solution]

When querying datasets in Cortex Data Lake, especially when searching for specific processes like w3wp.exe (used by IIS), it's common to encounter multiple entries for the same host. This happens because multiple instances of the process may run on a single machine, leading to duplicate results based on the agent_hostname. To eliminate these duplicates and return only unique hostnames, you can use the dedup operator on the agent_hostname field, which filters the results to one row per hostname. Alternatively, if you want to see how many times a process appears per host, you can use a group by clause to aggregate results and count occurrences. If you need only the most recent instance for each host, combining sort on the timestamp with dedup gives you the latest entry per hostname. These techniques help streamline your results and focus your analysis more effectively.   

[eluis · Accepted solution]

Hi Kenlacrosse, 

To remove duplicates on queries please have a look at the dedup stage in XQL: 

https://docs-cortex.paloaltonetworks.com/r/Cortex-XDR/Cortex-XDR-3.x-Documentation/dedup

If you feel this has answered your query, please let us know by clicking like and on "mark this as a Solution". Thank you.

KR, 

Luis