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]

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.

[gouravdashtcs]

Hello,

Kindly include all the field names which are required with the help of fields command. After that use the stats command.
Kindly note using stats command will mask the field which you are using for aggregation. If you want to retain your aggregated field, instead use eventstats command.

eg.
| fields x, y, z
| stats latest(_time) by A

[vnravikumar]

Hi @net1993

Try like

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

[net1993]

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

[net1993]

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.

[dkeck]

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.

[net1993]

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.

[kamlesh_vaghela · Accepted solution]

@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

[net1993]

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

[kamlesh_vaghela]

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

[net1993]

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

[kamlesh_vaghela]

@net1993

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

[net1993]

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

[kamlesh_vaghela]

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

[net1993]

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

[kamlesh_vaghela]

@net1993

Can you please try this?

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

[net1993]

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

[kamlesh_vaghela]

@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

[net1993]

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.

[net1993]

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

[kamlesh_vaghela]

@net1993

Can you please try these?

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

OR

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

OR

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