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

[rana_nour]

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.

[sjaworski · Accepted solution]

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]

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 🙂

[sjaworski]

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