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.
@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
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
Hi @net1993
Try like
index="_internal"
| stats list(date_hour) as date_hour count by host
| mvexpand date_hour
| stats sum(date_hour)
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
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.
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.
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.
@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
hmm, no this is not doing what I need. I tried but instead of doing grouping , it does something else.
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
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
@net1993
Can you please share sample output of A, B, C, _time
?
text1|23|20180101\n
text2|33|20180102\n
text1|24|20180103\n
text2|54|20180104\n
|stats latest(_time) by A
text1|20180103\n
text2|20180104\n
text1|24|20180103\n
text2|54|20180104\n
then I want to do sum on C
and get:
78
Does _time
contain 20180101
?? Don't you think it should be epoch??
its epoch but I write it like that for simplicity..
@net1993
Can you please try this?
YOUR_SEARCH
| dedup A
| stats sum(C) as C
dedup will remove data which I need , this is incorect.
@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
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.
I marked \n for new line as the reply web form is not recognizing new lines
@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)