Need help with count of count using Timechart

[Prathyusha891]

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

[gcusello]

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

or

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

Ciao.

Giuseppe

[Prathyusha891]

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

[gcusello]

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

[Prathyusha891]

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

[gcusello]

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

[Prathyusha891]

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

[ITWhisperer · Accepted solution]

index= store

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

|stats count by _time, sku

| stats sum(count) as count by _time

[Prathyusha891]

Thanks @ITWhisperer , @gcusello 

[gcusello]

Hi @Prathyusha891,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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