topic Re: Make a stats by value a separate field in Splunk Search

Make a stats by value a separate field

ebs — Thu, 27 May 2021 01:59:00 GMT

I've performed a stats by command I was wondering if there was a way to store all these as fields and then for the by field which has returned 0 make it null.

For context I performed an eval field to create a new field on via case then performed a stats by command.

stats command: stats avg(response_time) by category

Re: Make a stats by value a separate field

gcusello — Thu, 27 May 2021 05:47:12 GMT

Hi @ebs,

I'm not sure to understood your questions:

you want to know how to pass all fields after a stats command and how to manage values where the BY fields are null, is it correct?

If this is your need:

it's better to have in the stats command only the fields you need so you use less memory, but if you want to have all the fields, you could use values(*) AS * , but I don't like.

Abouth the null BY fields, you can use the fillnull command (adding e.g. "-") before the stats command, in this way you're sure that all the events have a value for the BY fields.

Ciao.

Giuseppe

Re: Make a stats by value a separate field

ebs — Mon, 31 May 2021 01:43:30 GMT

Hi @gcusello,

To explain further when I do my stats avg by command I have one column of field values and one column of correlating stats values. What I want to do is take the stats value and then assign it a field with the name of the corresponding field value e.g. if the avg of a = 3 and the avg of b = 5 I then want a field created called a, where the field value is 3 and another field called b where the value is 5. If there is then a value of c where there are no results, I want that created into a field and then do a fillnull command.

Also I specifically wanted the fillnull value for the created stats field

Thanks!

Re: Make a stats by value a separate field

gcusello — Mon, 31 May 2021 06:19:55 GMT

Hi @ebs,

in the stats command you can assign the field name as you want, e.g. something like this:

| stats avg(a) AS a avg(b) AS b BY c

and anyway, you have the rename command to change a field name.

about the null values, you have to try to use the fillnull command.

Ciao.

Giuseppe

Re: Make a stats by value a separate field

ITWhisperer — Mon, 31 May 2021 07:37:11 GMT

Have you considered transpose?

| transpose 0 header_field=value

Re: Make a stats by value a separate field

ebs — Mon, 31 May 2021 23:21:33 GMT

Thanks so much! I didn't even think of this command