topic Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest) in Splunk Search

Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 13:00:09 GMT

Hi,

I've read a while ago how easier Splunk is vs SQL, but I do not agree within the context of my issue:(

I want to get the latest values based on a field, and then use a different field, but according to SPL, this is not so common, and I need to use hours to hit my head in table

So, basically, here's what I want:

|stats latest(_time) by A

-> I want now to see/use values for field C, but I cannot as after stats, the only fields that are left are the ones mentioned in stats.

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

kamlesh_vaghela — Mon, 21 Jan 2019 13:10:01 GMT

@net1993

Answer Updated from comments:

Please try this one.

YOUR_SEARCH | eventstats latest(C) as C1 by A | stats values(C1) as C1 latest(_time) as T by A | eval _time=T | stats sum(C1) as C

You just remove one by one syntax, you will get the flow of result.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Eventstats

My Sample Search:

| makeresults 
| eval _raw="A=text1,C=23,Time=20180101", _time=(_time-(86000*4))
| append 
    [| makeresults 
    | eval _raw="A=text2,C=33,Time=20180102", _time=(_time-(86000*3)) ] 
| append 
    [| makeresults 
    | eval _raw="A=text1,C=24,Time=20180103", _time=(_time-(86000*2)) ] 
| append 
    [| makeresults 
    | eval _raw="A=text2,C=54,Time=20180104", _time=(_time-(86000*1)) ] 
| kv 
| eventstats latest(C) as C1 by A | stats values(C1) as C1 latest(_time) as T by A | eval _time=T | stats sum(C1) as C

Happy Splunking

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

dkeck — Mon, 21 Jan 2019 13:10:20 GMT

Hi,

so your problem is that stats switch you over to statistic tab?, just change your search mode to verbose and you can switch back to events as well.

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 13:14:20 GMT

hmm, no this is not doing what I need. I tried but instead of doing grouping , it does something else.

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 13:20:08 GMT

no. this is not the problem. I want to have stat table but what I want is after using the stats function to keep all of the fields which are before using stats. Currently, after stats is used, the only available fields after that are the one mentioned in stats function. The rest are unavailable.

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 13:28:20 GMT

I will try to illustrate the required outcome:

Before using stats function I have the following fields:

A, B, C, _time

then I do this:
|stats latest(_time) by B * I want to see the values for field C for every value of field B
then If I want to do that :
|stats sum(C) * I get error as field C doesnt exist anymore as its not mentioned in stats command.

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

vnravikumar — Mon, 21 Jan 2019 14:00:36 GMT

Hi @net1993

Try like

index="_internal" 
| stats list(date_hour) as date_hour count by host 
| mvexpand date_hour 
| stats sum(date_hour)

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

kamlesh_vaghela — Mon, 21 Jan 2019 14:27:00 GMT

Can you please share some sample events and your expected output?? like
1) Table of your event with your expected fields.
2) Your final expected table

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 14:31:19 GMT

thank you but this still is not right solution but I think you got the point. The problems with mvexpand:
- work with only up to 100 values.
- it is sorting the values as they re strings where isntead I need to get only these values which care corresponding on the rows for result from latest function.
- as far as I see from your command, the final sum will do sum on all values from list command fx 100x values but this ebcomes incorect as I want sum only on the values resulting from latest functions row

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

kamlesh_vaghela — Mon, 21 Jan 2019 14:32:40 GMT

@net1993

Can you please try these?

|stats latest(_time) as Time by B,C |stats sum(C)

|stats latest(_time) as Time , latest(C) as C by B |stats sum(C)

|stats latest(_time) as Time , values(C) as C by B |stats sum(C)

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 14:33:24 GMT

Yes I hope this is enought.?

Before using stats function I have the following fields:

A, B, C, _time

then I do this:
|stats latest(_time) by B I want to see the resulting values for field C for every value of field B
then If I want to do that :
|stats sum(C) I get error as field C doesnt exist anymore as its not mentioned in stats command.
let me know if not clear

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

kamlesh_vaghela — Mon, 21 Jan 2019 14:36:38 GMT

@net1993

Can you please share sample output of A, B, C, _time ?

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 14:52:15 GMT

A|C|_time

text1|23|20180101\n
text2|33|20180102\n
text1|24|20180103\n
text2|54|20180104\n

|stats latest(_time) by A

A|C|_time

text1|20180103\n
text2|20180104\n

A|C|_time

text1|24|20180103\n
text2|54|20180104\n

then I want to do sum on C

and get:
78

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 14:57:13 GMT

I marked \n for new line as the reply web form is not recognizing new lines

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

kamlesh_vaghela — Mon, 21 Jan 2019 14:57:36 GMT

Does _time contain 20180101?? Don't you think it should be epoch??

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 14:58:48 GMT

its epoch but I write it like that for simplicity..

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

kamlesh_vaghela — Mon, 21 Jan 2019 15:06:28 GMT

@net1993

Can you please try this?

YOUR_SEARCH 
| dedup A 
| stats sum(C) as C

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 15:08:32 GMT

dedup will remove data which I need , this is incorect.

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

kamlesh_vaghela — Mon, 21 Jan 2019 15:21:55 GMT

@net1993

Yes.

Please try this one.

YOUR_SEARCH | eventstats latest(C) as C1 by A | stats values(C1) as C1 latest(_time) as T by A | eval _time=T | stats sum(C1) as C

You just remove one by one syntax, you will get the flow of result.

My Sample Search:

| makeresults 
| eval _raw="A=text1,C=23,Time=20180101", _time=(_time-(86000*4))
| append 
    [| makeresults 
    | eval _raw="A=text2,C=33,Time=20180102", _time=(_time-(86000*3)) ] 
| append 
    [| makeresults 
    | eval _raw="A=text1,C=24,Time=20180103", _time=(_time-(86000*2)) ] 
| append 
    [| makeresults 
    | eval _raw="A=text2,C=54,Time=20180104", _time=(_time-(86000*1)) ] 
| kv 
| eventstats latest(C) as C1 by A | stats values(C1) as C1 latest(_time) as T by A | eval _time=T | stats sum(C1) as C

Re: Why does the stats function remove my fields and what Splunk solutions can I use for the following order: 1st do lastest(_time) -> then do sum(on the result of latest)

net1993 — Mon, 21 Jan 2019 23:01:00 GMT

Hi
Thanks a lot for the fast responses and help:)
I needed 1,5 hour to check this but seems to work quite ok.
Still it is not the perfect solution as this retain the value of only 1 field. lWhat do we do if we need whole row. Write 50 lines more?
Anyway, this is working solution.