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.
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
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
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
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