topic Re: What search can I use to identify logs with future timestamps? in Getting Data In

What search can I use to identify logs with future timestamps?

Hemnaath — Fri, 11 Aug 2017 13:39:29 GMT

Hi All, Can any one guide me on how to check whether any log sources that are logging with future time stamps. I am not sure how to identify those log source which are having a future time stamps. Kindly guide me on this.

thanks in advance

Re: What search can I use to identify logs with future timestamps?

richgalloway — Fri, 11 Aug 2017 13:47:04 GMT

Try this query.

| metadata type=sources | where recentTime>now() | fieldformat recentTime=strftime(recentTime,"%Y-%m-%d %H:%M")

Re: What search can I use to identify logs with future timestamps?

Hemnaath — Tue, 29 Sep 2020 15:20:26 GMT

hey richgalloway, i tried the above query but it did not fetch any result, got no result found. I had executed a query by keeping the time frame for last 24 hours.

when same query was executed with time frame for last 7 days, i could see this message in job

Metadata results may be incomplete: 100000 entries have been received from all peers (see parameter maxcount under the [metadata] stanza in limits.conf), and this search will not return metadata information for any more entries. (sid=md_1502460848.256250_8xAD37x6-D2F6-4C43-A22C-66B26D1236F6)

Re: What search can I use to identify logs with future timestamps?

JDukeSplunk — Fri, 11 Aug 2017 14:24:45 GMT

When I've run into an issue with a log file posting future timestamps, (Wrong timezone, wrong timestamp parsing, etc) an "All Time" search will usually show them. Obviously, make the search as specific as you can to limit the results.

I've not tried it, but the _indextime command might bear some fruit.

Re: What search can I use to identify logs with future timestamps?

richgalloway — Fri, 11 Aug 2017 14:28:40 GMT

To find future events you must search All Time or use something like latest=+2d.

Re: What search can I use to identify logs with future timestamps?

Hemnaath — Tue, 29 Sep 2020 15:20:43 GMT

hey i tried to execute the above query by keeping the time frame as "ALL time" but it did not fetch any results and got no result found.

0 results (before 8/11/17 11:11:48.000 AM)

Also getting this popup message in Job.
Metadata results may be incomplete: 100000 entries have been received from all peers (see parameter maxcount under the [metadata] stanza in limits.conf), and this search will not return metadata information for any more entries. (sid=md_1502460848.227250_8xAD37x6-D2F6-4C43-A22C-66B26D1236F6)

Re: What search can I use to identify logs with future timestamps?

prakash007 — Fri, 11 Aug 2017 15:25:20 GMT

An easy way should be searching the index and source with a future date-time range from your time range picker.

Re: What search can I use to identify logs with future timestamps?

richgalloway — Fri, 11 Aug 2017 16:35:55 GMT

If you have no events in the future then this query will not return any results. Try omitting the where clause to see what is returned.

Re: What search can I use to identify logs with future timestamps?

Hemnaath — Mon, 14 Aug 2017 12:34:02 GMT

Hi Richgalloway, When I had tried to execute the SPL query by omitting the where clause with the time range set as ALL Time, I could see some data being populated in statistics tab but unable to filter out the which source or index is having an issue.

I had tried in another way to find out whether future logs are generated for any index or not by keeping the time frame for future date "16-Aug-2017" and simple search like this

index = windows source=*

Can I check like this also ? is this a right way to find out whether any log sources that are logging with future time stamps.

Kindly guide me please.
thanks in advances.

Re: What search can I use to identify logs with future timestamps?

richgalloway — Mon, 14 Aug 2017 12:38:52 GMT

When I run the query, the results are sorted by recentTime. If they're not sorted when you run the query, click on the column heading to sort by that column. Then you'll be able to see if any sources are in the future. Since you get no results with the where clause, I'm guessing you have no future events.

Re: What search can I use to identify logs with future timestamps?

DalJeanis — Mon, 14 Aug 2017 13:46:58 GMT

@richgalloway @JDukeSplunk - you want lasttime rather than recenttime.

recenttime would only show the problem if the buggy event had been the last event processed on its index.

Re: What search can I use to identify logs with future timestamps?

Hemnaath — Tue, 29 Sep 2020 15:21:20 GMT

Hi richgalloway, thanks for your inputs on this, when executed the above query with out the where clause and time frame set as ALL Time and I got some data in to statistics, when sorted out I could see two events with future time stamp

| metadata type=sources | fieldformat recentTime=strftime(recentTime,"%Y-%m-%d %H:%M")

/opt/splunk/var/spool/splunk/3e5c33405c9c491d_events.stash_new sources 1497539222 1497539519 2017-06-15 11:42 5409
XXX-SEC-SUM-MAIL-SYMANTEC-MAIL sources 1497539401 1497555595 2017-06-15 16:10 18708

now, Not sure how to find from which index/host is this source is mapped to in splunk instances.

Re: What search can I use to identify logs with future timestamps?

gjanders — Mon, 14 Aug 2017 23:19:01 GMT

EDIT: I later packed these alerts and others into an app called Alerts for Splunk Admins on SplunkBase and also on github if you wanted just the searches...

I use (to detect future based data in any index):

index=* earliest=+5m latest=+20y 
| eval ahead=abs(now() - _time)
| eval indextime=_indextime
| bin span=1d indextime 
| stats avg(ahead) as averageahead, max(_time) AS maxTime, min(_time) as minTime, count by host, sourcetype, index, indextime
| where indextime>(now()-604800) AND averageahead > 1000
| eval averageahead =tostring(averageahead, "duration")
| eval indextime=strftime(indextime, "%+"), maxTime = strftime(maxTime, "%+"), minTime = strftime(minTime, "%+")

Re: What search can I use to identify logs with future timestamps?

Hemnaath — Tue, 29 Sep 2020 15:21:37 GMT

Hi Garethatiag, Thanks a lot for providing the query to fetch the future log source. When we had executed the above query by setting a time frame as All Time, we got some 550 node servers results.

host sourcetype index indextime averageahead maxTime minTime count
test1 Script:ListeningPorts win_svrs 00:00.0 16:41:30 49:00.0 49:00.0 49
test2 Script:ListeningPorts win_svrs 00:00.0 11:28:30 36:00.0 36:00.0 45
test3 Script:ListeningPorts win_svrs 00:00.0 4:29:30 37:00.0 37:00.0 45
test4 Script:ListeningPorts win_svrs 00:00.0 4:02:30 10:00.0 10:00.0 41
test5 Script:ListeningPorts win_svrs 00:00.0 0:28:30 36:00.0 36:00.0 68
test6 Script:ListeningPorts win_svrs 00:00.0 5:28:30 36:00.0 36:00.0 73

I am not that good in SPL, so could not understand what the output is trying to convey. Could please guide on this.

thanks in advance.

Re: What search can I use to identify logs with future timestamps?

gjanders — Tue, 15 Aug 2017 21:59:06 GMT

I've updated my post to use eval/strftime instead to see if that makes it more clear.

The query attempts to look for data that has been parsed between 5 minutes and 20 years into the future, and indexed during the last 7 days

The average ahead should be >1000 in my query and I can see 0 in your results...

If you want to see any future based data irrelevant of when it was indexed (I look over 7 days as a weekly alert FYI) then drop this line:

 | where indextime>(now()-604800) AND averageahead > 1000

And replace it with:

 | where averageahead > 1000

Re: What search can I use to identify logs with future timestamps?

Hemnaath — Tue, 29 Sep 2020 15:26:34 GMT

Hi Garethatiag, thanks for your effort, I have executed the update query from your post for a time frame of last 7 days. And I had got the below output .

host sourcetype index indextime averageahead maxTime minTime count
test1 Script:ListeningPorts win_svrs Wed Aug 16 00:00:00 EDT 2017 15:56:22 Wed Aug 16 22:49:00 EDT 2017 Wed Aug 16 22:49:00 EDT 2017 49
test2 Script:ListeningPorts win_svrs Wed Aug 16 00:00:00 EDT 2017 10:42:22 Wed Aug 16 17:35:00 EDT 2017 Wed Aug 16 17:35:00 EDT 2017 45
test3 Script:ListeningPorts win_svrs Wed Aug 16 00:00:00 EDT 2017 3:44:22 Wed Aug 16 10:37:00 EDT 2017 Wed Aug 16 10:37:00 EDT 2017 45
test4 Script:ListeningPorts win_svrs Wed Aug 16 00:00:00 EDT 2017 3:17:22 Wed Aug 16 10:10:00 EDT 2017 Wed Aug 16 10:10:00 EDT 2017 41
test5 Script:ListeningPorts win_svrs Wed Aug 16 00:00:00 EDT 2017 4:43:22 Wed Aug 16 11:36:00 EDT 2017 Wed Aug 16 11:36:00 EDT 2017 73
test6 Script:ListeningPorts win_svrs Wed Aug 16 00:00:00 EDT 2017 16:30:22 Wed Aug 16 23:23:00 EDT 2017 Wed Aug 16 23:23:00 EDT 2017 40

And also after changing the query by replacing it with | where averageahead > 1000 got the below output.

host sourcetype index indextime averageahead maxTime minTime count
test1 Script:ListeningPorts win_svrs Wed Aug 16 00:00:00 EDT 2017 15:52:28 Wed Aug 16 22:49:00 EDT 2017 Wed Aug 16 22:49:00 EDT 2017 49
test2 Script:ListeningPorts win_svrs Wed Aug 16 00:00:00 EDT 2017 10:38:28 Wed Aug 16 17:35:00 EDT 2017 Wed Aug 16 17:35:00 EDT 2017 45
test3 Script:ListeningPorts win_svrs Wed Aug 16 00:00:00 EDT 2017 3:40:28 Wed Aug 16 10:37:00 EDT 2017 Wed Aug 16 10:37:00 EDT 2017 45
test4 Script:ListeningPorts win_svrs Wed Aug 16 00:00:00 EDT 2017 3:13:28 Wed Aug 16 10:10:00 EDT 2017 Wed Aug 16 10:10:00 EDT 2017 41

Could you please guide us, whether we have any future log data source in our environment based on the above output result. As we have got some 550 nodes out of 2500 nodes in our environment, after executing the query.

thanks in advance.

Re: What search can I use to identify logs with future timestamps?

richgalloway — Wed, 16 Aug 2017 13:20:48 GMT

Make your query a subsearch and add SPL to get the fields you want.

[| metadata type=sources | fieldformat recentTime=strftime(recentTime,"%Y-%m-%d %H:%M")] | dedup source index host | table source index host

Re: What search can I use to identify logs with future timestamps?

gjanders — Tue, 29 Sep 2020 15:26:41 GMT

So the above is advising that hosts test1/2/3/4 with sourcetype Script:ListeningPorts in index win_svrs are definitely logging data with timestamps in the future!

The average is 15 hours ahead in the first line....

Perhaps you can try:
index=win_svrs host=test1 sourcetype=Script:ListeningPorts earliest=+5m latest=+20y
| where _indextime < _time
| eval indextime=strftime(_indextime, "%+")

I have not tested the where clause, but I suspect even without it you should see some data with index time stamps much older than the time stamps received...

Re: What search can I use to identify logs with future timestamps?

Hemnaath — Tue, 29 Sep 2020 15:23:04 GMT

Hi Garethatiag, again thanks for your effort, I had executed the query for few of the servers with time frame set as last 24 hours and found below outputs.

index=win_svrs host=test1 sourcetype=Script:ListeningPorts earliest=+5m latest=+20y
| where _indextime < _time
| eval indextime=strftime(_indextime, "%+")

8/17/17
10:49:00.000 PM
08/17/2017 0:22:49 transport=TCP dest_ip=[::] dest_port=63351 pid=6112
host = test1 source = C:\Program Files\SplunkUniversalForwarder\etc\apps\test-app-win_svrs\bin\win_listening_ports.bat sourcetype = Script:ListeningPorts
8/17/17
10:49:00.000 PM
08/17/2017 0:22:49 transport=TCP dest_ip=[::] dest_port=63335 pid=588
host = test1 source = C:\Program Files\SplunkUniversalForwarder\etc\apps\test-app-win_svrs\bin\win_listening_ports.bat sourcetype = Script:ListeningPorts
8/17/17

and there are nearly 375 nodes having the future log time problem from the same sourcetype = Script:ListeningPorts. I have checked the remote server node test1 time, its showing the correct time EDT time. Kindly let me know how to fix this issue.

Re: What search can I use to identify logs with future timestamps?

gjanders — Tue, 29 Sep 2020 15:23:33 GMT

Time parsing is done per sourcetype so assuming all logs entries look like this:
08/17/2017 0:22:49 transport=...

You could update props.conf with:
TIME_PREFIX=^
TIME_FORMAT = %m/%d/%Y %k:%M:%S

If your forwarders run in a different time zone or similar you can also use TZ property in the props.conf but the above might fix your issue...