fieldsummary command examples

The following are examples for using the SPL2 fieldsummary command. To learn more about the fieldsummary command, see How the SPL2 fieldsummary command works.

For a description of the summary information returned by the fieldsummary command, see fieldsummary command usage.

1. Return summaries for all fields

Consider the following data from a set of events in the orders dataset:

_time	clientip	action	pid	quantity	price
20 Jan 2022 12:00	192.0.2.0	purchase	DC-SG-G02	1	39.99
20 Jan 2022 11:58		addtochart	MB-AG-G07	3	27.99
20 Jan 2022 11:58	203.0.113.0	purchase	WC-SH-A01	1
20 Jan 2022 11:56	198.51.100.255	changequantity	PZ-SG-G05	2	4.99
20 Jan 2022 11:51	192.0.2.0	purchase	SF-BVS-01	1	49.99
20 Jan 2022 11:47	198.51.100.0	purchase	SF-BVS-G01	1	26.99
20 Jan 2022 11:42	192.0.2.0	purchase	WC-SH-T02	2	19.99
20 Jan 2022 11:39	198.51.100.0	purchase	PZ-SG-G05	1	4.99

This search returns summaries for all fields in the orders dataset:

| FROM orders | fieldsummary

The following results are returned. The values for the mean and stdev fields are truncated, but look similar to this:

field	count	distinct_ count	is_ exact	max	mean	min	numeric_ count	stdev	values
action	8	3	1				0		[{"value":"purchase","count":6}, {"value":"addtochart","count":1}, {"value":"changequantity","count":1}]
clientip	7	4	1				0		[{"value":"192.0.2.0","count":3}, {"value":"198.51.100.0","count":2}, {"value":"198.51.100.255","count":1}, {"value":"203.0.113.0","count":1}]
pid	8	7	1				0		[{"value":"PZ-SG-G05","count":2}, {"value":"DC-SG-G02","count":1}, {"value":"MB-AG-G07","count":1}, {"value":"SF-BVS-01","count":1}, {"value":"SF-BVS-G01","count":1}, {"value":"WC-SH-A01","count":1}, {"value":"WC-SH-T02","count":1}]
price	7	6	1	49.99	24.99	4.99	7	16.7	[{"value":"4.99","count":2}, {"value":"19.99","count":1}, {"value":"26.99","count":1}, {"value":"27.99","count":1}, {"value":"39.99","count":1}, {"value":"49.99","count":1}]
quantity	8	3	1	3	1.5	1	8	0.7	[{"value":"1","count":5}, {"value":"2","count":2}, {"value":"3","count":1}]

For a description of the summary information returned by the fieldsummary command, see fieldsummary command usage.

2. Return summaries for specific fields

Consider the following data from a set of events in the orders dataset:

_time	clientip	action	pid	quantity	price
20 Jan 2022 12:00	192.0.2.0	purchase	DC-SG-G02	1	39.99
20 Jan 2022 11:58		addtochart	MB-AG-G07	3	27.99
20 Jan 2022 11:58	203.0.113.0	purchase	WC-SH-A01	1
20 Jan 2022 11:56	198.51.100.255	changequantity	PZ-SG-G05	2	4.99
20 Jan 2022 11:51	192.0.2.0	purchase	SF-BVS-01	1	49.99
20 Jan 2022 11:47	198.51.100.0	purchase	SF-BVS-G01	1	26.99
20 Jan 2022 11:42	192.0.2.0	purchase	WC-SH-T02	2	19.99
20 Jan 2022 11:39	198.51.100.0	purchase	PZ-SG-G05	1	4.99

You can return summaries for all of the fields by including the fieldsummary command in your search without any other arguments. By default, a maximum of 10 values are returned for each field.

However, most of the time you want to specify which fields you want the summaries for. This search returns summaries for the specified fields in the orders dataset.

| FROM orders | fieldsummary fields=[action, pid, quantity]

The following results are returned. The values for the mean and stdev fields are truncated, but look similar to this:

field	count	distinct_ count	is_ exact	max	mean	min	numeric_ count	stdev	values
action	8	3	1				0		[{"value":"purchase","count":6}, {"value":"addtochart","count":1}, {"value":"changequantity","count":1}]
pid	8	7	1				0		[{"value":"PZ-SG-G05","count":2}, {"value":"DC-SG-G02","count":1}, {"value":"MB-AG-G07","count":1}, {"value":"SF-BVS-01","count":1}, {"value":"SF-BVS-G01","count":1}, {"value":"WC-SH-A01","count":1}, {"value":"WC-SH-T02","count":1}]
quantity	8	3	1	3	1.5	1	8	0.7	[{"value":"1","count":5}, {"value":"2","count":2}, {"value":"3","count":1}]

Notice that the results are sorted by the field column in ascending order. In the values column, the values are sorted first by highest count and then by distinct value, in ascending order.

3. Using wildcards when specifying field names

When you use wildcards to search for field names, you must enclose the field name in single quotation marks. For more information, see Quotation marks in the SPL2 Search Manual.

Consider the following data from a set of events in the hosts dataset:

_time	host	average_kbps	instanenous_kbps	kbps
14 Feb 2022 12:00	danube.sample.com	2.643	1.865	3.420
14 Feb 2022 11:53	mekong.buttercupgames.com	0.710	0.164	1.256
14 Feb 2022 11:47	danube.sample.com	1.325	0.419	2.230
14 Feb 2022 11:42	yangtze.buttercupgames.com	2.249	0.000	2.249
14 Feb 2022 11:39	nile.example.net	2.874	3.841	1.906
14 Feb 2022 11:33	nile.example.net	2.023	0.915	3.130

This search returns summaries for fields in the hosts dataset with names that contain "kbps":

FROM hosts| fieldsummary fields=[host, '*kbps*']

The results look similar to this:

field	count	distinct_ count	is_ exact	max	mean	min	numeric_ count	stdev	values
average_ kbps	6	6	1	2.874	1.9	0.71	6	0.8	[{"value":"0.71","count":1},{"value":"1.325","count":1},{"value":"2.023","count":1},{"value":"2.249","count":1},{"value":"2.643","count":1},{"value":"2.874","count":1}]
host	6	4	1				0		[{"value":"danube.sample.com","count":2},{"value":"nile.example.net","count":2},{"value":"mekong.buttercupgames.com","count":1},{"value":"yangtze.buttercupgames.com","count":1}]
instanenous_kbps	6	6	1	3.841	1.2	0.0	6	1.4	[{"value":"0.0","count":1},{"value":"0.164","count":1},{"value":"0.419","count":1},{"value":"0.915","count":1},{"value":"1.865","count":1},{"value":"3.841","count":1}]
kbps	6	6	1	3.42	2.3	1.256	6	0.7	[{"value":"1.256","count":1},{"value":"1.906","count":1},{"value":"2.23","count":1},{"value":"2.249","count":1},{"value":"3.13","count":1},{"value":"3.42","count":1}]

4. Specifying the number of values to return

By default, the fieldsummary command returns a maximum of 10 values. Use the maxvals argument to specify the number of values you want returned.

Consider the following data from a set of events in the hosts dataset:

_time	host	average_kbps	instanenous_kbps	kbps
14 Feb 2022 12:00	danube.sample.com	2.643	1.865	3.420
14 Feb 2022 11:53	mekong.buttercupgames.com	0.710	0.164	1.256
14 Feb 2022 11:47	danube.sample.com	1.325	0.419	2.230
14 Feb 2022 11:42	yangtze.buttercupgames.com	2.249	0.000	2.249
14 Feb 2022 11:39	nile.example.net	2.874	3.841	1.906
14 Feb 2022 11:33	nile.example.net	2.023	0.915	3.130

This search returns summaries for fields in the hosts dataset with names that contain "kbps" and returns a maximum of 2 values:

FROM hosts| fieldsummary fields=[host, 'kbps*'] maxvals=2

The results look similar to this:

field	count	distinct_ count	is_ exact	max	mean	min	numeric_ count	stdev	values
average_ kbps	6	6	1	2.874	1.9	0.71	6	0.8	[{"value":"0.71","count":1},{"value":"1.325","count":1}]
host	6	4	1				0		[{"value":"danube.sample.com","count":2},{"value":"nile.example.net","count":2}]
instanenous_kbps	6	6	1	3.841	1.2	0.0	6	1.4	[{"value":"0.0","count":1},{"value":"0.164","count":1}]
kbps	6	6	1	3.42	2.3	1.256	6	0.7	[{"value":"1.256","count":1},{"value":"1.906","count":1}]

The values are sorted first by highest count and then by distinct value, in ascending order. Compare this with the results returned in the example Using wildcards when specifying field names.

5. Returning summaries for object members in a field

Consider the following data in the supply_orders dataset. The address field contains an object with key-value members:

supplier_ID	supplier_name	address	orders
1009	Mile High Games	{"city":"Denver", "state/province":"Colorado", "country":"United States"}	45
1238	Area 51 Games	{"city":"Roswell", "state/province":"New Mexico", "country":"United States"}	21
4111	Isthmus Pastimes	{"city":"Panama City", "state/province":"Panama","country":"Panama"}	7
5007	EuroToys	{"city":"Prague", "state/province":"Central Bohemia", "country":"Czech Republic"}	15
1238	Area 51 Games	{"city":"Roswell", "state/province":"New Mexico", "country":"United States"}	6
5020	Blarney Games	{"city":"Dublin", "state/province":"Dublin", "country":"Ireland"}	23
5007	EuroToys	{"city":"Prague", "state/province":"Central Bohemia", "country":"Czech Republic"}	39
5007	EuroToys	{"city":"Prague", "state/province":"Central Bohemia", "country":"Czech Republic"}	18

Use the following search to return the supplier ID and the city names from the object in the address field:

| FROM supply_orders | fieldsummary fields=[supplier_ID, address.city]

The following results are returned. The values for the mean and stdev fields are truncated, but look similar to this:

field	count	distinct_ count	is_ exact	max	mean	min	numeric_ count	stdev	values
city	8	5	1				0		[{"value":"Prague","count":3},{"value":"Roswell","count":2},{"value":"Denver","count":1},{"value":"Dublin","count":1},{"value":"Panama City","count":1}]
supplier_ID	8	5	1	5020	3454.6	1009	8	1924.2	[{"value":"5007","count":3},{"value":"1238","count":2},{"value":"1009","count":1},{"value":"4111","count":1},{"value":"5020","count":1}]

See also

fieldsummary command

fieldsummary command overview

fieldsummary command syntax details

fieldsummary command usage