topic Re: use of tstats instead of stats in Splunk Search

use of tstats instead of stats

vikashperiwal — Wed, 30 Sep 2020 01:49:40 GMT

I am trying to iterate through 16million data and trying to use tstats instead of stats... please help me out in converting the stats query to tstats.
query

index=xyz sourcetype=SUMMARY
| fields TERM_SUBGRPG_CD ORIG_POINT_CD TERM_GRPG_CD DPC_CARRIER_LONG_NM CALLED_PARTY_NOA_CD DEST_POINT_CD DPC_TOLL_IND GENERIC_PORTED_SUBGRPG_CD OPC_CARRIER_LONG_NM OPC_CLLI_CD DPC_CLLI_CD SUM_BILL_TM_CNT SUM_NTWK_DURTN_CNT TOTAL_CALL_CNT DMS_FILE - _raw
| fillnull value=0
| stats sum(SUM_BILL_TM_CNT) as SUM_BILL_TM_CNT,sum(SUM_NTWK_DURTN_CNT) as SUM_NTWK_DURTN_CNT, sum(TOTAL_CALL_CNT) as TOTAL_CALL_CNT by DPC_CARRIER_LONG_NM,CALLED_PARTY_NOA_CD DEST_POINT_CD, DPC_CLLI_CD, OPC_CLLI_CD,TERM_SUBGRPG_CD
| table OPC_CLLI_CD DPC_CARRIER_LONG_NM CALLED_PARTY_NOA_CD DEST_POINT_CD DPC_CLLI_CD TERM_SUBGRPG_CD SUM_BILL_TM_CNT SUM_NTWK_DURTN_CNT TOTAL_CALL_CNT
| sort 0 - SUM_BILL_TM_CNT

Re: use of tstats instead of stats

richgalloway — Mon, 19 Aug 2019 13:58:28 GMT

Here's how it might look:

| tstats sum(SUM_BILL_TM_CNT) as SUM_BILL_TM_CNT,sum(SUM_NTWK_DURTN_CNT) as SUM_NTWK_DURTN_CNT, sum(TOTAL_CALL_CNT) as TOTAL_CALL_CNT by DPC_CARRIER_LONG_NM,CALLED_PARTY_NOA_CD DEST_POINT_CD, DPC_CLLI_CD, OPC_CLLI_CD,TERM_SUBGRPG_CD where index=xyz sourcetype=SUMMARY
| table OPC_CLLI_CD DPC_CARRIER_LONG_NM CALLED_PARTY_NOA_CD DEST_POINT_CD DPC_CLLI_CD TERM_SUBGRPG_CD SUM_BILL_TM_CNT SUM_NTWK_DURTN_CNT TOTAL_CALL_CNT 
| sort 0 - SUM_BILL_TM_CNT

There are some caveats:
1. tstats is a generating command so it must be first in the query.
2. All fields referenced by tstats must be indexed. There is no search-time extraction of fields.
3. fillnull cannot be used since it can't precede tstats.

Re: use of tstats instead of stats

vikashperiwal — Wed, 30 Sep 2020 01:49:45 GMT

Thanks for quick response.

I tried the above approach but no luck, let me know if i am missing anything. I broke query into simple form.

| tstats values(SUM_BILL_TM_CNT) where index=ndspr | stats sum(SUM_BILL_TM_CNT) as SUM_BILL_TM_CNT by DPC_CARRIER_LONG_NM,CALLED_PARTY_NOA_CD DEST_POINT_CD, DPC_CLLI_CD, OPC_CLLI_CD,TERM_SUBGRPG_CD
| table SUM_BILL_TM_CNT

tstats--Here number of events --25,702,086 and output no results

index=ndspr | stats sum(SUM_BILL_TM_CNT) as SUM_BILL_TM_CNT by DPC_CARRIER_LONG_NM,CALLED_PARTY_NOA_CD DEST_POINT_CD, DPC_CLLI_CD, OPC_CLLI_CD,TERM_SUBGRPG_CD

| table SUM_BILL_TM_CNT--- same number of events and i am getting output.

Re: use of tstats instead of stats

solarboyz1 — Wed, 30 Sep 2020 01:47:55 GMT

As written the tstats command should return one statistic, the list of values(SUM_BILL_TM_CNT) as "values(SUM_BILL_TM_CNT)"

Therefore the stats command is not getting any of the fields referenced.

If the recommended tstats search doesnt return results:

| tstats sum(SUM_BILL_TM_CNT) as SUM_BILL_TM_CNT,sum(SUM_NTWK_DURTN_CNT) as SUM_NTWK_DURTN_CNT, sum(TOTAL_CALL_CNT) as TOTAL_CALL_CNT by DPC_CARRIER_LONG_NM,CALLED_PARTY_NOA_CD DEST_POINT_CD, DPC_CLLI_CD, OPC_CLLI_CD,TERM_SUBGRPG_CD where index=xyz sourcetype=SUMMARY

I would verify that all of the fields specified are INDEXED fields.

Re: use of tstats instead of stats

richgalloway — Mon, 19 Aug 2019 19:27:44 GMT

You broke the query, but not into simple form. Queries should be broken at | characters only. Inserting extra commands doesn't help.
If the query in my answer doesn't work then it's probably because the fields used in the tstats command were not extracted at index time. tstats won't work otherwise.

Re: use of tstats instead of stats

vikashperiwal — Wed, 30 Sep 2020 01:50:07 GMT

i understand your point and it seems fields are not indexed , in this case what should be my approach?

Because when i run the query with normal way i get "search auto finilized after disk usage limit reached to 1000MB" as when i check the sourcetype count its too huge -- 1566206536

index=xyz sourcetype=abc
| fields TERM_SUBGRPG_CD ORIG_POINT_CD TERM_GRPG_CD DPC_CARRIER_LONG_NM CALLED_PARTY_NOA_CD DEST_POINT_CD DPC_TOLL_IND GENERIC_PORTED_SUBGRPG_CD OPC_CARRIER_LONG_NM OPC_CLLI_CD DPC_CLLI_CD SUM_BILL_TM_CNT SUM_NTWK_DURTN_CNT TOTAL_CALL_CNT DMS_FILE - _raw
| fillnull value=0
| stats sum(SUM_BILL_TM_CNT) as SUM_BILL_TM_CNT,sum(SUM_NTWK_DURTN_CNT) as SUM_NTWK_DURTN_CNT, sum(TOTAL_CALL_CNT) as TOTAL_CALL_CNT by DPC_CARRIER_LONG_NM,CALLED_PARTY_NOA_CD DEST_POINT_CD, DPC_CLLI_CD, OPC_CLLI_CD,TERM_SUBGRPG_CD
| table OPC_CLLI_CD DPC_CARRIER_LONG_NM CALLED_PARTY_NOA_CD DEST_POINT_CD DPC_CLLI_CD TERM_SUBGRPG_CD SUM_BILL_TM_CNT SUM_NTWK_DURTN_CNT TOTAL_CALL_CNT
| sort 0 - SUM_BILL_TM_CNT

Re: use of tstats instead of stats

solarboyz1 — Tue, 20 Aug 2019 14:00:42 GMT

If you want to use tstats, I recommend creating a data model:
https://docs.splunk.com/Documentation/Splunk/7.3.1/Knowledge/Aboutdatamodels

That includes at minimum, the fields specified in your search.