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
Path Finder

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.

Tags (2)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

View solution in original post

0 Karma

gouravdashtcs
Loves-to-Learn

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

0 Karma

vnravikumar
Champion

Hi @net1993

Try like

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

net1993
Path Finder

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

0 Karma

net1993
Path Finder

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.

0 Karma

dkeck
Influencer

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.

0 Karma

net1993
Path Finder

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

0 Karma

net1993
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

net1993
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@net1993

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

0 Karma

net1993
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

net1993
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@net1993

Can you please try this?

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

net1993
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
0 Karma

net1993
Path Finder

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.

0 Karma

net1993
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...