I have a question regarding how to properly extract the time ranges between the Events to use as a field value for a Date-Range column. Im setting up the Chargeback app, and im making a specific report. Currently, Im tracking the total ingestion by the Biz_Unit. The main splunk query does fine, but there's a lot of time manipulation within the search, and im not sure how to properly set the date I need. here is an example of some of the output.
This is the query, i know its a large query, but this outputs all of the fields used in chargeback.
`chargeback_summary_index` source=chargeback_internal_ingestion_tracker idx IN (*) st IN (*) idx="*" earliest=-7d@d latest=now
| fields _time idx st ingestion_gb indexer_count License
| rename idx As index_name
| `chargeback_normalize_storage_info`
| bin _time span=1h
| stats Latest(ingestion_gb) As ingestion_gb_idx_st Latest(License) As License By _time, index_name, st
| bin _time span=1d
| stats Sum(ingestion_gb_idx_st) As ingestion_idx_st_GB Latest(License) As License By _time, index_name, st
`chargeback_comment(" | `chargeback_data_2_bunit(index,index_name,index_name)` ")`
| `chargeback_index_enrichment_priority_order`
| `chargeback_get_entitlement(ingest)`
| fillnull value=100 perc_ownership
| eval shared_idx = if(perc_ownership="100", "No", "Yes")
| eval ingestion_idx_st_GB = ingestion_idx_st_GB * perc_ownership / 100 , ingest_unit_cost = ingest_yearly_cost / ingest_entitlement / 365
| fillnull value="Undefined" biz_unit, biz_division, biz_dep, biz_desc, biz_owner, biz_email
| fillnull value=0 ingest_unit_cost, ingest_yearly_cost, ingest_entitlement
| stats Latest(License) As License Latest(ingest_unit_cost) As ingest_unit_cost Latest(ingest_yearly_cost) As ingest_yearly_cost Latest(ingest_entitlement) As ingest_entitlement_GB Latest(shared_idx) As shared_idx Latest(ingestion_idx_st_GB) As ingestion_idx_st_GB Latest(perc_ownership) As perc_ownership Latest(biz_desc) As biz_desc Latest(biz_owner) As biz_owner Latest(biz_email) As biz_email Values(biz_division) As biz_division by _time, biz_unit, biz_dep, index_name, st
| eventstats Sum(ingestion_idx_st_GB) As ingestion_idx_GB by _time, index_name
| eventstats Sum(ingestion_idx_st_GB) As ingestion_bunit_dep_GB by _time, biz_unit, biz_dep, index_name
| eventstats Sum(ingestion_idx_st_GB) As ingestion_bunit_GB by _time, biz_unit, index_name
| eval ingestion_idx_st_TB = ingestion_idx_st_GB / 1024 , ingestion_idx_st_PB = ingestion_idx_st_TB / 1024 ,ingestion_idx_TB = ingestion_idx_GB / 1024 , ingestion_idx_PB = ingestion_idx_TB / 1024 , ingestion_bunit_dep_TB = ingestion_bunit_dep_GB / 1024 , ingestion_bunit_dep_PB = ingestion_bunit_dep_TB / 1024, ingestion_bunit_TB = ingestion_idx_GB / 1024 , ingestion_bunit_PB = ingestion_bunit_TB / 1024
| eval ingestion_bunit_dep_cost = ingestion_bunit_dep_GB * ingest_unit_cost, ingestion_bunit_cost = ingestion_bunit_GB * ingest_unit_cost, ingestion_idx_st_cost = ingestion_idx_st_GB * ingest_unit_cost
| eval ingest_entitlement_TB = ingest_entitlement_GB / 1024, ingest_entitlement_PB = ingest_entitlement_TB / 1024
| eval Time_Period = strftime(_time, "%a %b %d %Y")
| search biz_unit IN ("*") biz_dep IN ("*") shared_idx=* _time IN (*) biz_owner IN ("*") biz_desc IN ("*") biz_unit IN ("*") | table biz_unit biz_dep Time_Period index_name st perc_ownership ingestion_idx_GB ingestion_idx_st_GB ingestion_bunit_dep_GB ingestion_bunit_GB ingestion_bunit_dep_cost ingestion_bunit_cost biz_desc biz_owner biz_email
| sort 0 - ingestion_idx_GB
| rename st As Sourcetype ingestion_bunit_dep_cost as "Cost B-Unit/Dep", ingestion_bunit_cost As "Cost B-Unit", biz_unit As B-Unit, biz_dep As Department, index_name As Index, perc_ownership As "% Ownership", ingestion_idx_st_GB AS "Ingestion Sourcetype GB", ingestion_idx_GB As "Ingestion Index GB", ingestion_bunit_dep_GB As "Ingestion B-Unit/Dep GB",ingestion_bunit_GB As "Ingestion B-Unit GB", biz_desc As "Business Description", biz_owner As "Business Owner", biz_email As "Business Email"
| fieldformat Cost B-Unit/Dep = printf("%'.2f USD",'Cost B-Unit/Dep')
| fieldformat Cost B-Unit = printf("%'.2f USD",'Cost B-Unit')
| search Index = testing
| dedup Time_Period
| table B-Unit Time_Period "Ingestion B-Unit GB"
The above image shows what im trying to extract. The query has binned _time twice:
| fields _time idx st ingestion_gb indexer_count License
| rename idx As index_name
| `chargeback_normalize_storage_info`
| bin _time span=1h
| stats Latest(ingestion_gb) As ingestion_gb_idx_st Latest(License) As License By _time, index_name, st
| bin _time span=1d
| stats Sum(ingestion_gb_idx_st) As ingestion_idx_st_GB Latest(License) As License By _time, index_name, st
Ive asked our GPT equivalent bot how to properly do it, and it mentioned that when im sorting the stats by _time and index, it was overwriting the time variable. it also kept recommending me change and eval time down near the bottom of the query, something like:
| stats sum(Ingestion_Index_GB) as Ingestion_Index_GB sum("Ingestion B-Unit GB") as "Ingestion B-Unit GB" sum("Cost B-Unit") as "Cost B-Unit" earliest(_time) as early_time latest(_time) as late_time by B-Unit
| eval Date_Range = strftime(early_time, "%Y-%m-%d %H:%M:%S") . " - " . strftime(late_time, "%Y-%m-%d %H:%M:%S")
| table Date_Range B-Unit Ingestion_Index_GB "Ingestion B-Unit GB" "Cost B-Unit"
Other instances it said that it wasnt in string format, so i couldnt use the strftime.
overall, im now confused as to what is happening to the _time value. All i want is to get the earliest and latest value by index and set that as Date_Range. Can someone help me with this and possibly explain what is happening to the _time variable as it keeps getting manipulated and sorted by.
This is the search query found in the chargeback app under the storage tab. Its the "Daily Ingestion By Index, B-Unit & Department" search query.
if anyone has any ideas, any help would be much appreciated.
