topic Re: stats command not working as expected in Splunk Search

stats command not working as expected

sarit_s — Wed, 30 Sep 2020 01:00:42 GMT

hello
i have this query that calculated gaps between events.
im trying to get the source file of the events that was captured but im getting the wrong results.
this is my query:

index=ssys_internal_pj AND sourcetype=*log*
| streamstats current=f last(_time) as last_time last(source) as last_source  by SerialNumber    
| eval gap = last_time - _time | where gap > 14400 | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(last_time) as last_time | table SerialNumber _time last_time  last_source source

the result im getting is the last source file of this SerialNumber not related at all to the _time and last_time that was captured

how can i get the right sources ?

also, is there a way to use tstats to make this query run faster? it is very slow

thanks

Re: stats command not working as expected

VatsalJagani — Wed, 19 Jun 2019 14:16:26 GMT

Hello @sarit_s - Could you please explain question more clearly or it would be greate if you can give sample results that you are getting and result you are expecting.

Re: stats command not working as expected

sarit_s — Wed, 19 Jun 2019 14:26:13 GMT

Hello
this is an example of the result im getting:

SerialNumber
8500154     
_time   
2019-06-17 12:15:32
last_time
2019-06-17 23:53:20 
    last_source
s3:///2019-06-17T13:07:47.939Z_1.91.0.192_1.92.0.0_31.31.58.12484/SANITIZED_17-06-19-11-32.log  
    source
    s3:///2019-06-17T13:07:47.939Z_1.91.0.192_1.92.0.0_31.31.58.12484/SANITIZED_17-06-19-11-32.log

as you can see the source is the same at both source and last_source
and neither of them is the right one 🙂

i want to get the source of the event that was captured at 2019-06-17 12:15:32
and the source of the event that was captured at 2019-06-17 23:53:20

Re: stats command not working as expected

VatsalJagani — Wed, 19 Jun 2019 14:34:28 GMT

I'm still not sure about your requirement, but I guess you want latest source in that case try removing current=f from your query.

Re: stats command not working as expected

sarit_s — Wed, 19 Jun 2019 14:44:12 GMT

this option returning no results at all

im not looking for latest source
i have _time and last _time
each one of them coming from event
i want to see the source of this event

Re: stats command not working as expected

adonio — Wed, 19 Jun 2019 17:32:44 GMT

wild guess here is that you want to add global=false to your streamstats as you are splitting by something

try this:

 index=ssys_internal_pj AND sourcetype=*log* 
    | streamstats current=f global=false last(_time) as last_time last(source) as last_source by SerialNumber 
    | eval gap = last_time - _time 
    | where gap > 14400 
    | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(last_time) as last_time 
    | table SerialNumber _time last_time last_source source

hope it helps

Re: stats command not working as expected

somesoni2 — Wed, 19 Jun 2019 19:08:40 GMT

Give this a try

index=ssys_internal_pj AND sourcetype=*log*
 | streamstats current=f window=1 values(_time) as last_time values(source) as last_source  by SerialNumber    
 | eval gap = last_time - _time | where gap > 14400 | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(last_time) as last_time | table SerialNumber _time last_time  last_source source

Re: stats command not working as expected

sarit_s — Thu, 20 Jun 2019 06:11:42 GMT

hi
thanks for your answer,
this option does not return the right results..

Re: stats command not working as expected

sarit_s — Thu, 20 Jun 2019 06:13:53 GMT

hi
thanks for your answer
this is the result im getting

  last_time 
        2019-06-17 15:22:03



last_source 
s3:///2019-06-02T23:53:43.027Z_1.91.0.192_1.88.0.0_31.30.75.12117/SANITIZED_02-06-19-12-22.log

as you can see the last time was 2019-06-17 and the last source was from 02-06-19 which is not correct