topic Re: count number of serialnumber with dc takes lots of time in Splunk Search

count number of serialnumber with dc takes lots of time

sarit_s — Mon, 06 May 2019 14:03:53 GMT

hello
i have this query :
index = amer_pj
| SerialNumber
| Region
| stats dc(SerialNumber) as SerialNumber by Region
| table SerialNumber

which supposed to count the number of uniqe SerialNumbers
for last 30 days it take more than an hour to complete the query
what am i doing wrong ? is there a better way to do it ?
is there a way to save the result of the last run ?
(it is a dashboard, not a report)

thanks

Re: count number of serialnumber with dc takes lots of time

Vijeta — Mon, 06 May 2019 14:15:09 GMT

How many events you have in 30 day time frame.You can summarize your data in a summary index over last 30 days and use the summary index in your report.

Also try this query

index=amer_pj| fields SerialNumber , Region | stats dc(SerialNumber) as SerialNumber by Region

Re: count number of serialnumber with dc takes lots of time

somesoni2 — Mon, 06 May 2019 14:52:52 GMT

What do those two macros do??

Re: count number of serialnumber with dc takes lots of time

sarit_s — Mon, 06 May 2019 16:33:42 GMT

extract SerialNumber and Region from source

Re: count number of serialnumber with dc takes lots of time

sarit_s — Mon, 06 May 2019 16:35:21 GMT

its still very slow..

i have millions of events in 30 days
how can i use summary ?

Re: count number of serialnumber with dc takes lots of time

Vijeta — Mon, 06 May 2019 16:37:48 GMT

May be doing extraction at index time will help.
Also scheduled summary indexing is a good option for your case.

Re: count number of serialnumber with dc takes lots of time

sarit_s — Mon, 06 May 2019 16:39:20 GMT

the rex is not the problem since im using it in lots of other queries

how can i use summary ?

Re: count number of serialnumber with dc takes lots of time

somesoni2 — Mon, 06 May 2019 17:55:04 GMT

When you say source, you means "source" (generally file name) metadata field? If yes, then try this version: Should be way faster

| tstats count WHERE index = amer_pj  by source
| `SerialNumber`
| `Region`
| stats dc(SerialNumber) as SerialNumber by Region
| table SerialNumber

Re: count number of serialnumber with dc takes lots of time

sarit_s — Mon, 06 May 2019 18:15:09 GMT

perfect !!!
please edit your answer so i will be able to accept it

Re: count number of serialnumber with dc takes lots of time

Vijeta — Wed, 30 Sep 2020 00:26:20 GMT

@sarit_s Write your search and save it as a report .At the end of your search collect the output data in a summary index (say test_summary) using collect command and schedule it to run as per your need may be once a day based on last 30 days date time range. The _time in summary index can be changed as per your need else it will take time at which scheduled search ran.
| collect index=test_summary

Once data is collected in test_summary , you can simply query on index=test_summary in your dashboard or report.

Re: count number of serialnumber with dc takes lots of time

sarit_s — Tue, 07 May 2019 05:47:39 GMT

thanks !

Re: count number of serialnumber with dc takes lots of time

MuS — Tue, 07 May 2019 06:53:50 GMT

Please accept this answer if it solved your problem.

cheers, MuS