Calculate sizes of dynamic fields

This search determines which fields in your events consume the most disk space, without any prior knowledge of field names and number of events.

Scenario

index=_internal earliest=-15m latest=now
| fieldsummary 
| rex field=values max_match=0 "value\":\"(?<values>[^\"]*)\","
| mvexpand values 
| eval bytes=len(values)
| rex field=field "^(?!date|punct|host|hostip|index|linecount|source|sourcetype|timeendpos|timestartpos|splunk_server)(?<FieldName>.*)"
| stats count sum(bytes) as SumOfBytesInField values(values) as Values max(bytes) as MaxFieldLengthInBytes by FieldName
| rename count as NumOfValuesPerField
| eventstats sum(NumOfValuesPerField) as TotalEvents sum(SumOfBytesInField) as TotalBytes
| eval PercentOfTotalEvents=round(NumOfValuesPerField/TotalEvents*100,2)
| eval PercentOfTotalBytes=round(SumOfBytesInField/TotalBytes*100,2)
| eval ConsumedMB=SumOfBytesInField/1024/1024
| eval TotalMB=TotalBytes/1024/1024
| table FieldName NumOfValuesPerField SumOfBytesInField ConsumedMB PercentOfTotalBytes PercentOfTotalEvents
| addcoltotals labelfield=FieldName label=Totals
| sort - PercentOfTotalEvents

The results appear on the Statistics tab and look something like this:

FieldName NumValuesPerField SumOfBytesInField ConsumedMB PercentOfTotalBytes PercentOfTotalEvents
Totals 1802 45700 0.0436 99.87 100.29
cumulative_hits 100 587 0.0006 1.28 5.55
eps 100 1862 0.0018 4.07 5.55
kb 100 1159 0.0011 2.54 5.55
kbps 100 1881 0.0018 4.12 5.55
req_time 100 3000 0.0029 6.56 5.55
uri 100 10559 0.0101 23.11 5.55
uri_query 100 3532 0.0034 7.73 5.55
message 96 11633 0.0111 25.46 5.33
avg_age 76 280 0.0012 2.80 4.22
ev 62 140 0.0001 0.31 3.44
average_kbps 59 1071 0.0010 2.34 3.27
Note: The totals do not add up to exactly 100% because this search is adding up the values after the values have been rounded. To avoid this, you can move the rounding to the end of the search string.

Walkthrough

Let's walk through each part of the search.

  1. The example begins with a search to retrieve all events in index=_internal within the last 15 minutes. 
    index=_internal earliest=-15m latest=now

    Note: You can replace this with any search string and time range.

  2. Next, add the the fieldsummary command to create a summary of all the fields in the previously retrieved events. 
    | fieldsummary

    The results appear on the Statistics tab and look something like this:

    field count distinct _count is _exact max mean min numeric _count stdev values
    abandoned _channels 29 1 1 0.0 0.00 0.0 29 0.00 [{"value":"0","count":29}]
    active 29 1 1 0.0 0.00 0.0 29 0.00 [{"value":"0","count":29}]
    active _hist _searches 31 2 1 1.0 0.13 0.0 31 0.34 [{"value":"0","count":27},

    {"value":"1","count":4}]

    average _kbps 87 59 1 0.3 0.21 0.0 87 0.15 [{"value":"0","count":29},

    {"value":"0.31239045073685034","count":1}, {"value":"0.31240549380412547","count":1}, {"value":"0.3124557194522294","count":1}, {"value":"0.3124707607545469","count":1}]

  3. The values of each field are extracted with a regex into a multivalue field called values, and then expanded. The length of each value is calculated in bytes. 
    | rex field=values max_match=0 "value\":\"(?<values>[^\"]*)\","
| mvexpand values 
| eval bytes=len(values)
  4. The values of the field are extracted with another regex, with some exceptions. 
    | rex field=field "^(?!date|punct|host|hostip|index|linecount|source|sourcetype|timeendpos|timestartpos|splunk_server)(?<FieldName>.*)"
  5. The stats command is used to perform multiple calculations using stats functions, including the count and the sum of the bytes (SumOfBytesInField). The values function is used to returns the list of all distinct values of the values field as a multivalue entry (Values). The max function calculates the maximum field length in bytes (MaxFieldLengthInBytes). The results are organized by field name. 
    | stats count sum(bytes) as SumOfBytesInField values(values) as Values max(bytes) as MaxFieldLengthInBytes by FieldName
| rename count as NumOfValuesPerField
  6. The eventstats command is used to calculate several sums, the number of values in each field (TotalEvents) and the sum of the bytes in each field (Total Bytes). 
    | eventstats sum(NumOfValuesPerField) as TotalEvents sum(SumOfBytesInField) as TotalBytes
  7. Several eval commands are run to calculate the percentage of total events, the percentage of total bytes, the megabytes consumed, and the total megabytes. 
    | eval PercentOfTotalEvents=round(NumOfValuesPerField/TotalEvents*100,2)
| eval PercentOfTotalBytes=round(SumOfBytesInField/TotalBytes*100,2)
| eval ConsumedMB=SumOfBytesInField/1024/1024
| eval TotalMB=TotalBytes/1024/1024
  8. The table command is used to display on a specific set of fields. The addfoltotals command is used to calculate the total for each column. The sort command is used sort the list in descending order by the PercentageOfTotalEvents field. 
    | table FieldName NumberOfValuesPerField SumOfBytesInField ConsumedMB PercentageOfTotalBytes PercentageOfTotalEvents
| addcoltotals labelfield=FieldName label=Totals
| sort - PercentageOfTotalEvents

The results appear on the Statistics tab and look something like this:

FieldName NumValuesPerField SumOfBytesInField ConsumedMB PercentOfTotalBytes PercentOfTotalEvents
Totals 1802 45700 0.0436 99.87 100.29
cumulative_hits 100 587 0.0006 1.28 5.55
eps 100 1862 0.0018 4.07 5.55
kb 100 1159 0.0011 2.54 5.55
kbps 100 1881 0.0018 4.12 5.55
req_time 100 3000 0.0029 6.56 5.55
uri 100 10559 0.0101 23.11 5.55
uri_query 100 3532 0.0034 7.73 5.55
message 96 11633 0.0111 25.46 5.33
avg_age 76 280 0.0012 2.80 4.22
ev 62 140 0.0001 0.31 3.44
average_kbps 59 1071 0.0010 2.34 3.27