How to edit my search to remove duplicate source, sourcetype, and _time values by host?

[syedsalam]

Hi,

This is my search and need to remove duplicate source, sourcetype, and last_time by host. Please suggest how to do this:

index=windows (search NOT sourcetype=WinHostMon NOT source=Powershell)  |stats list(_time) as last_time,list(source) as source,list(sourcetype) as sourcetype by host | eval latency_minutes=((now()-last_time)/60) | convert ctime(last_time) as last_time | fields + host, sourcetype, source, last_time

When I use the dedup command, duplicate data is not getting removed from source, sourcetype, and last_time by host.

Please find the attached screenshot and help me with removing same.

[somesoni2 · Accepted solution]

Give this a try

index=windows (search NOT sourcetype=WinHostMon NOT source=Powershell)  | stats count by host _time source sourcetype |stats list(_time) as last_time,list(source) as source,list(sourcetype) as sourcetype by host | eval latency_minutes=((now()-last_time)/60) | convert ctime(last_time) as last_time | fields + host, sourcetype, source, last_time

Try this as well

| tstats count WHERE index=windows NOT (sourcetype=WinHostMon source=Powershell)  
by host _time source sourcetype |stats list(_time) as last_time,list(source) as source,list(sourcetype) as sourcetype by host | eval latency_minutes=((now()-last_time)/60) | convert ctime(last_time) as last_time | fields + host, sourcetype, source, last_time

[richgalloway]

Have you tried this?

index=windows (search NOT sourcetype=WinHostMon NOT source=Powershell) | dedup host, sourcetype, source, _time |stats list(_time) as last_time,list(source) as source,list(sourcetype) as sourcetype by host | eval latency_minutes=((now()-last_time)/60) | convert ctime(last_time) as last_time | fields + host, sourcetype, source, last_time

[syedsalam]

Yes, Was not working.

The below comment is working fine, but taking more time to get the result.

index=windows  AND  sourcetype!=WinHostMon AND source!=Powershell |stats max(_time) as last_time by host,source,sourcetype |stats list(last_time) as last_time,list(source) as source,list(sourcetype) as sourcetype by host | eval latency_minutes=((now()-last_time)/60) | convert ctime(last_time) as last_time | fields + host, sourcetype, source, last_time