Splunk Search

Returning Non-Duplicates from a table

brajaram
Communicator

My data tells me if counts on a specific server are timing out, and we are trying to set up an alert for when this occurs.

Our query currently is:

index=... responseTime>5000
| bin span=15m _time
| stats count by host serviceName _time
| search count > 50

This tells us when, for a given timeframe, if a given set of events by any service has more than 5s response time(timeouts). However, what we really want is to identify when this occurs multiple times across a larger time window. Our goal is to have this query run for the last 30 minutes, every 30 minutes, and let us know when a host shows up more than once.

The result tables looks like this:

host serviceName _time count
Host1 Service1 12:00 80
Host1 Service1 12:15 75
Host2 Service1 12:15 55
Host3 Service1 12:00 80
Host3 Service2 12:00 80

What we want is to pull out only the duplicated hosts from this table - that indicates hosts that are timing out across this window, or hosts that are timing out across multiple services, both of which are important to us to identify. The rows I want returned are bolded. How do I remove all values from this table that do not duplicate?

Tags (2)
0 Karma
1 Solution

brajaram
Communicator

Found the solution. If I append

| eventstats count AS countHost by host
| search countHost>1
| fields – countHost

It adds the count of each host name to a new field in the table, which I can then filter on.

View solution in original post

brajaram
Communicator

Found the solution. If I append

| eventstats count AS countHost by host
| search countHost>1
| fields – countHost

It adds the count of each host name to a new field in the table, which I can then filter on.

cpetterborg
SplunkTrust
SplunkTrust

I'd try to do the simplest method for this which I think would be:

... | stats count as cat by Name, serviceName | search cnt>1

This would take your table and do a count by the Name and serviceName, and then return only those that have more than one instance in the table. You would lose the _time and count, however, in your result.

You may also want to adjust your search so that you have overlapping searches (like every 15 minutes using the last 30 minutes of data). That will prevent lose where you have one on one side of the search period and one on the other.

brajaram
Communicator

Very good idea about the overlapping searches, will definitely do that. I do need the _time and count field unfortunately. I did find a solution around it, however, with eventstats

0 Karma

sushantmhatre
Explorer

Can you provide more clarification on this question

0 Karma

brajaram
Communicator

Essentially, if I have a table that contains 3 elements Name, Value, Count, I want to return only the rows in which Name occurs more than once. Edited the question to be hopefully more clear.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...