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