How to create a search that can Event count by Vendor/Product by Day for past 30days?

[adalbor]

Hey All,

I am trying to create an efficient search that I can schedule and run once a month to create some metrics.

I would like to be able to get a number of events per vendor/product or sourcetype by day for the last 30 days. I was then hoping to use a sparkline to trend that data over those 30 days with each day being a point in that sparkline (or line chart).

I have been looking at tstats but wasn't quite sure how to accomplish this goal as a couple of test searches weren't returning quite what I needed.

Any help would be greatly appreciated!

[sandeepmakkena]

sourcetype=*
|bucket _time span=day
|stats count by _time

This should work.

[adalbor]

Does this include every index? The event counts looked pretty low when I ran that.

[sandeepmakkena]

It should include every index. We are only filtering by source type. Or if you think it’s not including you just say index=* and that query.

[sandeepmakkena]

Did this work ?

[DavidHourani · Accepted solution]

Hi @adalbor, if you want to use tstats for faster results make sure your data model includes the _time field and that its accelerated. Once that is done the rest should be easy, you can share your query with us and we can help you improve it 😉

[adalbor]

Hey @DavidHourani,
I dont have a data model setup for this. Is that something I should be doing?

[DavidHourani]

If you want to use tstats you will need to accelerate your data in a data model, yes. How were you using tstats without a DM to access non-indexed fields ?

[adalbor]

I am not specifically trying to use tstats, I am more just trying to find the most efficient way to do this.
I wasn't sure if tstats would do the job or not or a stats count.

[DavidHourani]

Well tstats runs on metadata directly so its the fastest, if you're looking for performance.

You can also go for a summary index containing the values you need for your timechart that could be fast as well.

In anycase you should avoid fetching all the data with

index=YourIndexName | stats yourStats

[adalbor]

Yeah thats why I was exploring other options and trying to figure out an advanced search like this as I am fairly new to Splunk world. Piping to stats was slow and inefficient.

[DavidHourani]

Yeah, you really want to avoid that. Accelerared data models and summary indexing is the way to to when there's a lot of data to search 🙂

Let me know if you need anything else ! And please accept the answer and upvote if it was helpful.

[adalbor]

Appreciate the help but I am still not sure where to start or how to accomplish.

[DavidHourani]

Ahh, ok well. That's the easy part, you got everything in Splunk docs.
Step 1: create you data model and assign the right data to it. That can be found here :
https://docs.splunk.com/Documentation/Splunk/7.3.0/Knowledge/Managedatamodels#Create_a_new_data_mode...
Step 2: Accelerate the data model. That can be found here :
https://docs.splunk.com/Documentation/Splunk/7.3.0/HadoopAnalytics/Configuredatamodelacceleration#Ac...
Step 3: query the accelerated data using tstats. You can find that here :
https://docs.splunk.com/Documentation/Splunk/7.3.0/Knowledge/Acceleratedatamodels#Using_the_summarie...

Let me know if you need more help with those steps.

Cheers,
David

[adalbor]

Once again...thanks for the assistance but docs aren't what I need. Guess Ill figure out on my own

[DavidHourani]

ummm, okay well judging from the answer below you're looking for something like this :

| metasearch index=*| timechart count by index

Or simply like this if it's a count of events over time :

| metasearch index=*| timechart count

[adalbor]

The first metasearch search worked pretty well. Took a little while to run against 7 days of data but I will schedule the search for a low impact time prob overnight.

Thanks for your help!