Getting Data In

How to extract the correct timestamp from a SAS log

las
Contributor

Hi.

I have an audit log from SAS SPDServer, where they use their internal time format, this looks like an epoch format, but its base is Jan. 1st 1960, not Jan. 1st 1970. Hence when I use %s I'm of by ten years.

_AUD_ 2 5 1667726473 BATSAS32 *LIBNAM* DATA _NONE_ _NONE_ ASSIGN ACCESS READ READ N RAMATPME 17 m:\ramspd\ramatp\ 6 _NONE_
_AUD_ 2 5 1667726473 BATSAS32 *LIBNAM* DATA _NONE_ _NONE_ ASSIGN ACCESS READ READ N RAMPDKME 17 m:\ramspd\rampdk\ 6 _NONE_
_AUD_ 2 5 1667726501 BATSAS32 *LIBNAM* DATA _NONE_ _NONE_ ASSIGN ACCESS READ READ N RAMAER 17 m:\ramspd\ramaer\ 6 _NONE_
_AUD_ 2 5 1667726843 BATSAS32 TWEKEWBEREGNPRAEM DATA BATSAS32 USERS OPEN INPUT READ READ N RAMAER 17 m:\ramspd\ramaer\ 6 _NONE_

The timestamp is the 4th blank delimited field.
I would expect the time to fall sometime at Nov. 4th 2012

Tags (3)
0 Karma
1 Solution

gkanapathy
Splunk Employee
Splunk Employee

I don't think there's currently any way to deal with this in Splunk. All I can suggest is to pre-process the files to a date format that Splunk can work with.

View solution in original post

las
Contributor

What we ended up doing is this:

1. monitor the files with a sourcetype of baddates to a special index with current time as the timestamp

2. Every 5 minutes we do a saved search, that fixes the dates and uses the collect statement to write a file.

3. Batch monitor the directory with the fixed data, and index them into the proper index.

This way we get the right timestamps, but these files count double against our license, but since they are very small, it has minimal impact.

At the same time I have raised an enchancement request with support.

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

I don't think there's currently any way to deal with this in Splunk. All I can suggest is to pre-process the files to a date format that Splunk can work with.

gkanapathy
Splunk Employee
Splunk Employee

it's not possible to do arithmetic at index time. it is an entirely reasonable enhancement request that could (and should) be made via Splunk Support. but even it were possible, time is handled a bit differently in Splunk, so it would need special treatment.

las
Contributor

Would it be possible to do the math at indextime.
so effectively it would end with being something like _Time = raw_time - (86400((36510)+3)), maybe in a custom datetime.xml?

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

If the date format really is just "calendar seconds since midnight Jan 1 1960" then i guess I don't see why the math would be any more complicated than subtraction of a fixed amount (86400*((365*10)+3)). Even if it includes leap seconds, for very recent dates it's off by no more than a couple of seconds. You can get around the quarantine bucket problems with indexes.conf and props.conf configurations (in fact, the props.conf ones would be required).

Search-time arithmetic is not a very good idea if you have any desire to correlate this data with more conventionally timestamped data.

0 Karma

sowings
Splunk Employee
Splunk Employee

You could do so at search time, yes. Because these events are so far "in the future", they'll likely end up in a quarantine bucket with their peers. It'll be tricky to search for them with the time range picker, but you can definitely do some second math at search time to get a more reasonable time field. What that second math looks like, however, is a good question; I don't think you can simply subtract "10 years in seconds" and get the answer you want.

0 Karma

las
Contributor

Too bad, is there a way I can get Splunk to call a script for each event, so I can use the monitor stanza, and still subtract the ten years difference?
Otherwise I have to keep control of where in the file we are located.

0 Karma

MHibbin
Influencer

I had something similar recently... I set up a scripted input that would effectively "tail" the file in python. It would then read each line and append my additional info (in your case this would be the Splunk friendly date), and write this to a new file (you could of course just print to STDOUT, but I prefer not to) and have Splunk monitor that new file (not needed if printing to STDOUT). ... Just some "food for thought"

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

Or see if there's any way to induce the time format to be printed in, well, most human-readable formats would work.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...