Splunk Search
Highlighted

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)

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
Highlighted

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)

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
Highlighted

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)

Path Finder

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

0 Karma
Highlighted

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)

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
Highlighted

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)

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
Highlighted

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)

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
Highlighted

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)

SplunkTrust
SplunkTrust

@net1993

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

0 Karma
Highlighted

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)

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
Highlighted

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)

Path Finder

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

0 Karma
Highlighted

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)

SplunkTrust
SplunkTrust

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

0 Karma