Splunk Search

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

syedsalam
New Member

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.

alt text

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

0 Karma

somesoni2
Revered Legend

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
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

syedsalam
New Member

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
0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...