Splunk Search

How to write a search to display sites with the top hits monthly?

rana_nour
Explorer

I am new to splunk 🙂

I need your help to get the top sites with highest hits monthly. In other words, I need to have the dates as the column and under each date I need to have the sites with top hits. Please support me with the query as I always fail to get the desired output and I am not sure if that's Possible.

Tags (3)
1 Solution

sjaworski
Communicator

The timechart, untable, stats and sort command should help.

Your search | timechart useother=false limit=20 span=1mon count(Your_Site_Field) as cysf by Your_Site_Field | untable _time Your_Site_Field cysf | sort - cysf | where cysf> 0 | stats list(Your_Site_Field) as Sites list(cysf) as Hits by _time | sort - _time

Breakdown of the search
|timechart useother=false limit=20 span=1mon count(Your_Site_Field) as cysf by Your_Site_Field
This will give you the option to group your data by day or month. Adjust the span option to fit you needs. Grouped by month is span=1mon while grouped by day is span=1d.

Adjust the limit option accordingly. This search will limit the results to the 20 top results for each group.
Check out the timechart command in Splunk docs for more detail on limit, span, and useother.

| untable _time Your_Site_Field cysf
This converts the tabular timechart results into a table you can run stats on.

| sort - cysf
Descending sort of the sites

| where cysf> 0
Because the timechart lists every Your_Site_Field there will be values of 0 for days the site was not accessed. This will remove results of 0 for those days.

| stats list(Your_Site_Field) as Sites list(cysf) as Hits by _time
This will group the results with count by the time and rename the fields to something legible.

| sort - _time
Sort the results one more time by descending.

View solution in original post

sjaworski
Communicator

The timechart, untable, stats and sort command should help.

Your search | timechart useother=false limit=20 span=1mon count(Your_Site_Field) as cysf by Your_Site_Field | untable _time Your_Site_Field cysf | sort - cysf | where cysf> 0 | stats list(Your_Site_Field) as Sites list(cysf) as Hits by _time | sort - _time

Breakdown of the search
|timechart useother=false limit=20 span=1mon count(Your_Site_Field) as cysf by Your_Site_Field
This will give you the option to group your data by day or month. Adjust the span option to fit you needs. Grouped by month is span=1mon while grouped by day is span=1d.

Adjust the limit option accordingly. This search will limit the results to the 20 top results for each group.
Check out the timechart command in Splunk docs for more detail on limit, span, and useother.

| untable _time Your_Site_Field cysf
This converts the tabular timechart results into a table you can run stats on.

| sort - cysf
Descending sort of the sites

| where cysf> 0
Because the timechart lists every Your_Site_Field there will be values of 0 for days the site was not accessed. This will remove results of 0 for those days.

| stats list(Your_Site_Field) as Sites list(cysf) as Hits by _time
This will group the results with count by the time and rename the fields to something legible.

| sort - _time
Sort the results one more time by descending.

rana_nour
Explorer

This is brillaint 🙂 that is exactly what I need 🙂 Just one more question.. how would I get the hits on all of the sites... I mean not only the top 20

Thanks a million 🙂

0 Karma

sjaworski
Communicator

Just change limit=20 to limit=0. Depending on how many results you may have. Most likely your search will result with the warning 'stats' command: limit for values of field X. You may have to modify your limits.conf file. Check out this Splunk answer for more information.

Modify Limits.conf

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...