topic Re: How to create a search that can Event count by Vendor/Product by Day for past 30days? in Splunk Search

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

adalbor — Tue, 25 Jun 2019 14:57:01 GMT

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!

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

DavidHourani — Tue, 25 Jun 2019 15:17:10 GMT

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 😉

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

adalbor — Tue, 25 Jun 2019 15:20:16 GMT

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

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

DavidHourani — Tue, 25 Jun 2019 15:25:38 GMT

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 ?

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

adalbor — Tue, 25 Jun 2019 15:29:36 GMT

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.

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

DavidHourani — Tue, 25 Jun 2019 16:19:08 GMT

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

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

adalbor — Tue, 25 Jun 2019 17:19:50 GMT

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.

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

DavidHourani — Tue, 25 Jun 2019 17:35:05 GMT

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.

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

adalbor — Tue, 25 Jun 2019 17:38:28 GMT

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

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

DavidHourani — Tue, 25 Jun 2019 18:20:19 GMT

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_model
Step 2: Accelerate the data model. That can be found here :
https://docs.splunk.com/Documentation/Splunk/7.3.0/HadoopAnalytics/Configuredatamodelacceleration#Accelerate_the_data_model
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_summariesonly_argument

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

Cheers,
David

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

adalbor — Tue, 25 Jun 2019 18:25:39 GMT

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

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

sandeepmakkena — Tue, 25 Jun 2019 19:09:55 GMT

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

This should work.

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

adalbor — Tue, 25 Jun 2019 20:29:00 GMT

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

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

sandeepmakkena — Wed, 26 Jun 2019 02:07:06 GMT

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.

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

DavidHourani — Wed, 26 Jun 2019 09:42:46 GMT

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

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

adalbor — Wed, 26 Jun 2019 14:36:31 GMT

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!

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

sandeepmakkena — Wed, 26 Jun 2019 20:07:31 GMT

Did this work ?