Splunk Search

Can we generate a report per index that shows the time back stats of the queries?

danielbb
Motivator

We need to decide soon how much storage to allocate to the hot/warm volume versus the cold one. Therefore, I would like to generate a report which would show for each index the time back we search against.

Is it possible?

Tags (1)
0 Karma

adonio
Ultra Champion

its possible but not so straight forward
look at all your saved searches and alerts via | rest like this:

|rest/servicesNS/-/-/saved/searches
| table search dispatch.earliest_time dispatch.latest_time

now you have the time window for your searches and the search syntax.
as often happen, maybe not all searches have index=<something> maybe some has index IN(a b c) or maybe index=a OR index=b or maybe they donrt specify an index at all and you have to go to the roles to figure out what are the deault indexes for this role.
in any case, not always easy to rex it out
now, after you did that , you can search the _audit and _internal indexes to look at ad-hoc searches
you also probably want to check your dashboards and how their panels and base searches are defined as well.
i will recommend to focus on what you need, and consider the following: do you have fast disk for hot / warm?
how much data do you bring daily?
according to surveys, most users use 95% of their searches on data consumed in the last 24 hours.
follow this guideline, and i am pretty sure you will be safe

adonio
Ultra Champion

@danielbb I see you upvoted, and that's great. But could you also click the "accept" button?

0 Karma

jacobpevans
Motivator

Sure you can! I built this as a fun exercise so no promises that it perfectly meets your needs (which is why I'm not posting as an answer), but check this out. My goal was to show the count of the earliest days by index name. Hopefully it helps getting you started. You'll definitely want to set the Y axis to a "Log" scale.

index=_audit action=search info=granted search=* NOT "search_id='scheduler" NOT "search='|history" NOT "user=splunk-system-user" NOT "search='typeahead" NOT "search='| metadata type=* | search totalCount>0"
| rex max_match=0 "index\s*=\s*(?<Searched_Indexes>\w+)"
| mvexpand Searched_Indexes
| table _time Searched_Indexes apiStartTime apiEndTime
| where isnotnull(Searched_Indexes)
| eval query_start = strptime(apiStartTime, "'%c'")
| eval _time = query_start
| timechart useother=false limit=0 count by Searched_Indexes

Cheers,
Jacob

Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.

danielbb
Motivator

Great. I see the following -

alt text

How can I restrict the X axis to cover the past month, for example?

0 Karma

jacobpevans
Motivator

You don't want to do that. It's showing you the earliest time in the time range picker - not the time the search was executed. I'd go with the other answer over mine.

Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.
0 Karma

danielbb
Motivator

That's all good but if you look at the graph, all the real data is compressed on the right side.

0 Karma

jacobpevans
Motivator

The vast majority (99% on average) of searches are placed within the past week or so.

If you really want to do it, throw on this at the end:

| where _time > now() - 86400*30

For the past 30 days.

Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...