topic Re: Need help with count of count using Timechart in Reporting

Need help with count of count using Timechart

Prathyusha891 — Fri, 20 Jan 2023 07:22:40 GMT

Event 1:
Product=shirt1 sku=123 sku=234

Event 2:
Product=shirt2 sku=987 sku=789

index= store

| rex field=_raw max_match=0 "sku\W(?P<sku>.*?)\,"

|stats count by _time, sku

o/p:

_time	sku	count
01-04-23	123	1
01-04-23	234	1
01-04-23	987	1
01-04-23	789	1

Output I’m looking for

_time	count
01-04-23	4

Re: Need help with count of count using Timechart

ITWhisperer — Fri, 20 Jan 2023 07:32:23 GMT

index= store | rex field=_raw max_match=0 "sku\W(?P<sku>.*?)\," |stats count by _time, sku | stats sum(count) as count by _time

Re: Need help with count of count using Timechart

gcusello — Fri, 20 Jan 2023 07:35:25 GMT

Hi @Prathyusha891,

please try this:

index= store | rex field=_raw max_match=0 "sku\W(?P<sku>.*?)\," | stats dc(sku) AS sku_count BY _time

index= store | rex field=_raw max_match=0 "sku\W(?P<sku>.*?)\," | timechart dc(sku)

Ciao.

Giuseppe

Re: Need help with count of count using Timechart

Prathyusha891 — Fri, 20 Jan 2023 08:46:06 GMT

Its giving us the distinct count of sku but not the total count

Re: Need help with count of count using Timechart

gcusello — Fri, 20 Jan 2023 09:08:01 GMT

HI @Prathyusha891,

I understood that you wanted to have the distinct count, no problem, you can use sum instead dc:

index= store | rex field=_raw max_match=0 "sku\W(?P<sku>.*?)\," | stats dc(sku) AS sku_count count BY _time

Ciao.

Giuseppe

Re: Need help with count of count using Timechart

Prathyusha891 — Fri, 20 Jan 2023 09:21:32 GMT

Sorry. Let me rephrase it for better understanding

_time	sku	count
01-04-23	123	1
01-04-23	234	1
01-04-23	987	1
01-04-23	789	3
01-04-24	678	1

O/P I'm looking for -

_time	count
01-04-23	6
01-04-24	1

Re: Need help with count of count using Timechart

gcusello — Fri, 20 Jan 2023 09:24:41 GMT

Hi @Prathyusha891,

in this case you can use the solution from @ITWhisperer or:

index= store | rex field=_raw max_match=0 "sku\W(?P<sku>.*?)\," | stats count by _time sku | timechart sum(count) AS count BY _time

Ciao.

Giuseppe

Re: Need help with count of count using Timechart

Prathyusha891 — Fri, 20 Jan 2023 09:35:27 GMT

Sure Thanks. But I don't think stats and Timechart work together.

Re: Need help with count of count using Timechart

Prathyusha891 — Fri, 20 Jan 2023 09:38:00 GMT

Thanks @ITWhisperer , @gcusello

Re: Need help with count of count using Timechart

gcusello — Fri, 20 Jan 2023 10:08:55 GMT

Hi @Prathyusha891,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉