Splunk Search

use of tstats instead of stats

vikashperiwal
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 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

0 Karma

richgalloway
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, Karma would be appreciated.

vikashperiwal
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(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.

0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma

vikashperiwal
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 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

0 Karma

solarboyz1
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

solarboyz1
Builder

| 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

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.

Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...