topic Re: Search string to filter filed not updated in last 24 hours in Splunk Search

Search string to filter filed not updated in last 24 hours

Gowtham0809 — Tue, 29 Sep 2020 16:53:02 GMT

Hi,

I use the below search to filer the source which were not updated on current day(Today)

index=index sourcetype="sourcetype" source="source*.csv" | table source, _time | dedup source | where _time < relative_time(now(),"-1d@d).

This string was providing the results and not suddenly stops working. No changes were made what so ever.

can some one help me with this

Thanks

Re: Search string to filter filed not updated in last 24 hours

MousumiChowdhur — Fri, 24 Nov 2017 04:22:18 GMT

Hi Gowtham, what's the error do you get when you run the query?

Re: Search string to filter filed not updated in last 24 hours

niketn — Fri, 24 Nov 2017 08:07:23 GMT

@Gowtham0809, from the query seems like you want to check whether there is any data source which has not data today but was sending data earlier. Instead of running base search on your index with all time search, you should leverage commands like metadata or tstats which are specifically meant for such use cases.

Option 1: Using Metadata command

| metadata type="sources" index="index"
| where lastTime<relative_time(now(),"-1d@d")
| fieldformat lastTime=strftime(lastTime,"%c")
| table source totalCount lastTime

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Metadata

Option 2: Using tstats command

| tstats count as Events earliest(_time) AS EarlistEventTime, latest(_time) AS LatestEventTime WHERE index="index" BY source
| where LatestEventTime<relative_time(now(),"-1d@d") 
| fieldformat LatestEventTime=strftime(LatestEventTime,"%c")
| table source Events LatestEventTime

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Tstats#Examples

PS: "-1d@d" implies one day before, for last 24 hours ago you should use "-24h@h".

Re: Search string to filter filed not updated in last 24 hours

woodcock — Mon, 27 Nov 2017 01:18:39 GMT

What @niketnilay said except that metadata does strange things in regards to the TimePicker (because it has to: it has to judge on a bucket-by-bucket basis and any bucket may have a considerable span of events) so definitely use tstats. Additionally, I would weight _indextime more than _time because what you really care about is that events are still being indexed, right? So this:

| tstats count max(_indextime) AS _time WHERE index="index" BY source
| eval secondsSinceLastIndexed = now() - _time
| sort 0 - secondsSinceLastIndexed
| lastIndexDistance=tostring(secondsSinceLastIndexed , "duration")