topic How to do groupBy based on a field and select the ones when occurrence of a word inside the grouped By values is greater than 2 in Knowledge Management

How to do groupBy based on a field and select the ones when occurrence of a word inside the grouped By values is greater than 2

annibaby13 — Fri, 30 Dec 2016 20:17:14 GMT

Hi,
I have a requirement where I have to do a group by initially and from the groupBy values perform a search operation to filter the final results when the occurrence of a word (count of the word) is greater than 2

example below:

I have rows like 1 aaaaaaaaa aaaaa ggggggg aaaaa
1 ssssssssss sssssssss ssssssss
2 tttttttttttttttt yyyyyyy gggggg
1 eeeeeeeeeeee wwwww wwwww

from the above example i need to do groupby and from the result, need to do a search operation and pick the count of a word (for example wwwww when its occurrence is greater than 2 (which is true in the last statement) so the final result should be only 1 eeeeeeeeeeee wwwww wwwww

Re: How to do groupBy based on a field and select the ones when occurrence of a word inside the grouped By values is greater than 2

niketn — Sun, 01 Jan 2017 03:58:12 GMT

There are several splunk functions which will allow you to do "group by" of same field values like chart, rare, sort, stats, and timechart, eventstats, streamstats, sistats etc. Following is a comparison between SQL and SPL(Splunk Processing Language).

DB SQL Example

SELECT mycolumn, avg(mycolumn)
FROM mytable
WHERE mycolumn=value
GROUP BY mycolumn

SPL Example in Splunk

source=mytable mycolumn=value
| STATS avg(mycolumn) BY mycolumn
| FIELDS mycolumn, avg(mycolumn)

Refer to the Splunk documentation on transitioning from SQL to Splunk: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/SQLtoSplunk

You would need to provide more examples of your data so that we can identify data type(based on source of data), field names and suggest query as per your need).

Re: How to do groupBy based on a field and select the ones when occurrence of a word inside the grouped By values is greater than 2

maraman_splunk — Sun, 01 Jan 2017 14:40:29 GMT

Have you looked at this post : https://answers.splunk.com/answers/253863/how-do-i-search-the-count-of-how-many-times-a-keyw.html ?

BTW, If you want to keep the original events but run stats at the same time, then probably you should try the eventstats function (after using the post above) .