Splunk Search
Highlighted

use of tstats instead of stats

Path Finder

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 TERMSUBGRPGCD ORIGPOINTCD TERMGRPGCD DPCCARRIERLONGNM CALLEDPARTYNOACD DESTPOINTCD DPCTOLLIND GENERICPORTEDSUBGRPGCD OPCCARRIERLONGNM OPCCLLICD DPCCLLICD SUMBILLTMCNT SUMNTWKDURTNCNT TOTALCALLCNT DMSFILE - _raw
| fillnull value=0
| stats sum(SUM
BILLTMCNT) as SUMBILLTMCNT,sum(SUMNTWKDURTNCNT) as SUMNTWKDURTNCNT, sum(TOTALCALLCNT) as TOTALCALLCNT by DPCCARRIERLONGNM,CALLEDPARTYNOACD DESTPOINTCD, DPCCLLICD, OPCCLLICD,TERMSUBGRPGCD
| table OPC
CLLICD DPCCARRIERLONGNM CALLEDPARTYNOACD DESTPOINTCD DPCCLLICD TERMSUBGRPGCD SUMBILLTMCNT SUMNTWKDURTNCNT TOTALCALLCNT
| sort 0 - SUM
BILLTMCNT

0 Karma
Highlighted

Re: use of tstats instead of stats

SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
Highlighted

Re: use of tstats instead of stats

Path Finder

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(SUMBILLTMCNT) where index=ndspr | stats sum(SUMBILLTMCNT) as SUMBILLTMCNT by DPCCARRIERLONGNM,CALLEDPARTYNOACD DESTPOINTCD, DPCCLLICD, OPCCLLICD,TERMSUBGRPGCD
| table SUM
BILLTMCNT

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

index=ndspr | stats sum(SUMBILLTMCNT) as SUMBILLTMCNT by DPCCARRIERLONGNM,CALLEDPARTYNOACD DESTPOINTCD, DPCCLLICD, OPCCLLICD,TERMSUBGRPGCD

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

0 Karma
Highlighted

Re: use of tstats instead of stats

Builder

| tstats values(SUMBILLTMCNT) where index=ndspr | stats sum(SUMBILLTMCNT) as SUMBILLTMCNT by DPCCARRIERLONGNM,CALLEDPARTYNOACD DESTPOINTCD, DPCCLLICD, OPCCLLICD,TERMSUBGRPGCD
| table SUM
BILLTMCNT

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

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

If the recommended tstats search doesnt return results:

| tstats sum(SUMBILLTMCNT) as SUMBILLTMCNT,sum(SUMNTWKDURTNCNT) as SUMNTWKDURTNCNT, sum(TOTALCALLCNT) as TOTALCALLCNT by DPCCARRIERLONGNM,CALLEDPARTYNOACD DESTPOINTCD, DPCCLLICD, OPCCLLICD,TERMSUBGRPGCD where index=xyz sourcetype=SUMMARY

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

Highlighted

Re: use of tstats instead of stats

SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: use of tstats instead of stats

Path Finder

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 TERMSUBGRPGCD ORIGPOINTCD TERMGRPGCD DPCCARRIERLONGNM CALLEDPARTYNOACD DESTPOINTCD DPCTOLLIND GENERICPORTEDSUBGRPGCD OPCCARRIERLONGNM OPCCLLICD DPCCLLICD SUMBILLTMCNT SUMNTWKDURTNCNT TOTALCALLCNT DMSFILE - _raw
| fillnull value=0
| stats sum(SUM
BILLTMCNT) as SUMBILLTMCNT,sum(SUMNTWKDURTNCNT) as SUMNTWKDURTNCNT, sum(TOTALCALLCNT) as TOTALCALLCNT by DPCCARRIERLONGNM,CALLEDPARTYNOACD DESTPOINTCD, DPCCLLICD, OPCCLLICD,TERMSUBGRPGCD
| table OPC
CLLICD DPCCARRIERLONGNM CALLEDPARTYNOACD DESTPOINTCD DPCCLLICD TERMSUBGRPGCD SUMBILLTMCNT SUMNTWKDURTNCNT TOTALCALLCNT
| sort 0 - SUM
BILLTMCNT

0 Karma
Highlighted

Re: use of tstats instead of stats

Builder

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.

0 Karma