topic Re: Can you help me using dedup and count? in Splunk Search

Can you help me using dedup and count?

jwalzerpitt — Wed, 23 Jan 2019 14:33:22 GMT

I have the following search based on F5 logs that count the HTTP POSTs by src in a five-minute bucket:

index=f5 action!=blocked http_method=POST
| bucket _time span=5m
| stats count by _time, src, website
| sort -count
| stats dc(website) as distinct_website, list(website) as Website, list(count) as count, sum(count) as Total by src 
| where distinct_website >= 3
| sort -Total

The problem is, I get an output that lists the same websites multiple times:

If I add '| dedup Website' before the 'where' command, I still see the duplicate websites.

if I add '| dedup website' after the '| stats count by _time, src, website' command, the websites are deduped, but I see the following output that contains a different src and websites altogether:

Do I need the dedup command at all for this search?

Thx

Re: Can you help me using dedup and count?

skoelpin — Wed, 23 Jan 2019 14:43:26 GMT

Try this

index=f5 action!=blocked http_method=POST
 | bucket _time span=5m
 | stats count by _time, src, website
 | sort -count
 | stats dc(website) as distinct_website, list(count) as count, sum(count) as Total by src, website
 | where distinct_website >= 3
 | sort -Total

Re: Can you help me using dedup and count?

vnravikumar — Wed, 23 Jan 2019 14:51:49 GMT

Hi @jwalzerpitt

Try with values(website)

Re: Can you help me using dedup and count?

jwalzerpitt — Wed, 23 Jan 2019 14:55:16 GMT

Thx for the reply and search. Using your suggested search returns the same output as my original search with dupes of websites listed.

As I think through this is adding the Total potentially causing an issue? Maybe I need to sum the count of individual POSTs against each website first to get rid of the dupes so that www.abc.com has a total count of 45 POSTs, and then www.def.com has a total count of 34 POSTs, and so on, with the Total being the aggregate number of POSTs from all websites?

Or perhaps because I'm running the search against a 24 hour time period with five-minute buckets that it's listing the dupes for the websites because there are POSTs scattered among the five-minute buckets throughout the 24 hour time period for the same website?

Re: Can you help me using dedup and count?

skoelpin — Wed, 23 Jan 2019 15:04:37 GMT

I've edited by original answer. Try that one

It's difficult not testing this on my end, but in a nutshell, we are counting by website. In your second stats, you should have the by website clause.

Re: Can you help me using dedup and count?

jwalzerpitt — Wed, 23 Jan 2019 15:18:56 GMT

The edited search returns no results.

Re: Can you help me using dedup and count?

skoelpin — Wed, 23 Jan 2019 15:24:02 GMT

It's going to take a little massaging on your end... You should remove line by line to identify whats breaking the search. I don't have your query in front of me so I can't do it for you..

Re: Can you help me using dedup and count?

jwalzerpitt — Wed, 23 Jan 2019 15:30:15 GMT

it's not that the search is broke, just wondering if the search is the most efficient way to find src IPs with a lot of POSTs in a short amount of time.

I believe the line you suggested, "| stats count values(website) AS website by _time, src" provides a more efficient search, and I go back to a previous anaswer of mine in which I speculated that I might not be able to get rid of the duplicate websites as the POSTs are happening in different five-minute buckets.

I'm just trying to figure out how to total the count per website

Thx for the help

Re: Can you help me using dedup and count?

skoelpin — Wed, 23 Jan 2019 15:38:31 GMT

Each field added after the by clause will decrease efficiency. You're correct about better efficiency with the values(website) this will provide multiple websites by src. the src would be deduped while the website field could be multi-valued. If you were to add that website field after the by clause on your first stats, then website would be deduped.

I'm confused, you said the search isn't broke, but you claimed it wasn't returning results.

Re: Can you help me using dedup and count?

jwalzerpitt — Wed, 23 Jan 2019 15:44:44 GMT

My original post showed the results I was getting that had the duplicate websites and I asked if there was a way to get rid of them using the dedup command. The edited search returns no results but rolling back to your first search below returns results:

  | bucket _time span=5m
  | stats count values(website) AS website by _time, src
  | sort -count
  | stats dc(website) as distinct_website, list(website) as Website, list(count) as count, sum(count) as Total by src 
  | where distinct_website >= 3
  | sort -Total

Re: Can you help me using dedup and count?

janderson19 — Wed, 23 Jan 2019 22:38:39 GMT