stats command not working as expected

[sarit_s]

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

[somesoni2]

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

[sarit_s]

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

[adonio]

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

read more here:
https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Streamstats

hope it helps

[sarit_s]

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

[VatsalJagani]

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.

[sarit_s]

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

[VatsalJagani]

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.

[sarit_s]

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