count number of serialnumber with dc takes lots of time

[sarit_s]

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

[somesoni2 · Accepted solution]

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

[sarit_s]

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

[MuS]

Please accept this answer if it solved your problem.

cheers, MuS

[Vijeta]

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

[sarit_s]

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

how can i use summary ?

[Vijeta]

@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.

[sarit_s]

thanks !

[somesoni2]

What do those two macros do??

[sarit_s]

extract SerialNumber and Region from source

[Vijeta]

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

[sarit_s]

its still very slow..

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