Splunk Search

How to use tstats and get raw last event?

Builder

Hello,

I would like to get raw last event for each source listed by tstats, how to do? I've tried tstats ... | join but no result (see attached)

Thanks.

alt text

0 Karma

Builder

Solved with stats

0 Karma

Super Champion

remove |table _time, _raw as here you are considering only two fields in results and trying to join with host, source and index
or you can replace that with |table _time, _raw, host, source, index
Let me know if it gives output...

0 Karma

Builder

no result 😞

0 Karma

Builder

I tried reverse way and it said tstats must be the first command.

table time,host,source,index,raw | head 1

| join host,source,index [search | tstats latest(time) as latest,earliest(time) as earliest WHERE (index=* by host source index | eval lastevent=strftime(latest, "%Y-%m-%d %H:%M") | eval firstevent=strftime(earliest, "%Y-%m-%d %H:%M")
| eval stimeyesterday="%".strftime(timeYesterday, "%Y-%m-%d")."%" | eval timeRelative=round(relative_time(now(), "@d")) | where latest < timeRelative | eval datacenter=if(match(host,"s303|s403|s503|s603|s703"),"N","S") | eval resultat=if(latest < timeRelative,"KO","OK") | eval stimerel=strftime(timeRelative, "%Y-%m-%d %H:%M") | sort datacenter,index,host | where NOT like (source,stimeyesterday) | fields - latest earliest timeRelative timeYesterday stimeyesterday stimerel]

0 Karma

Super Champion

yes tstats should be first command .
when you search your query starting with |tstats and ends with
|where NOT like(source,stimeyesterday)
are you getting output?

0 Karma

Builder

Thanks, this works. My question was about _raw data I want to show (of last event)

0 Karma

Super Champion

so finally you are getting _raw data as expected...:)

0 Karma

Builder

No unfortunately 😞

0 Karma

Super Champion

so can you share one sample output after running:

 | tstats latest(_time) as latest,earliest(_time) as earliest WHERE (index=* by host source index | eval lastevent=strftime(latest, "%Y-%m-%d %H:%M") | eval firstevent=strftime(earliest, "%Y-%m-%d %H:%M")  | eval stimeyesterday="%".strftime(timeYesterday, "%Y-%m-%d")."%" | eval timeRelative=round(relative_time(now(), "@d")) | where latest < timeRelative | eval datacenter=if(match(host,"s303|s403|s503|s603|s703"),"N","S") | eval resultat=if(latest < timeRelative,"KO","OK") | eval stimerel=strftime(timeRelative, "%Y-%m-%d %H:%M") | sort datacenter,index,host | where NOT like (source,stimeyesterday)
0 Karma