topic Re: How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process? in Getting Data In

How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process?

aseid — Mon, 21 Sep 2015 13:14:18 GMT

I designed a scheduled search that populates "identities.csv" by querying Active Directory using 'ldapsearch'. Everything looks fine except that when 'Enterprise Security/Incident Review' reports a big number of "Expired account activity" although the endDate for most of the accounts is "(never)". When I checked the date format for srartDate and endDate, it is of type 20140602145733.0Z I suspect that Splunk don't probably interpret these dates properly thus reporting a large number of "false positive" of expired account activities.

Any pointer on the issue is welcome.

regards

Re: How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process?

somesoni2 — Mon, 21 Sep 2015 16:40:48 GMT

What is your current search that populates identities.csv? I guess issue here is that startDate and endDate fields in this csv is not recognized correctly, hence causing issues???

Re: How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process?

aseid — Mon, 21 Sep 2015 18:05:12 GMT

Both fields are correctly populated; no miss in there.

Re: How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process?

somesoni2 — Mon, 21 Sep 2015 18:13:56 GMT

Ok.. The 'Enterprise Security/Incident Review' reports is a custom dashboard OR is part of app for splunk-base?

Re: How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process?

alacercogitatus — Mon, 21 Sep 2015 18:23:54 GMT

You can recalculate the date string. Here is a sample of how to convert it.

index=_internal | head 1 | eval ADDATE = "20140602145733.0Z" | eval epoch = strptime(ADDATE,"%Y%m%d%H%M%S.%1NZ") | table epoch ADDATE

You can add this into a props configuration to do it automatically.

props.conf

[your_sourcetype]
EVAL-endDate = strptime(endDate,"%Y%m%d%H%M%S.%1NZ")

Re: How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process?

aseid — Tue, 22 Sep 2015 14:13:04 GMT

The above works for 'startDate' (=AD 'whenCreated') but not for 'endDate' (=AD 'accountExpires'). The format of the later is different from the former. An example of endDate is: 2016-03-01T05:00:00Z.

Re: How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process?

alacercogitatus — Tue, 22 Sep 2015 14:18:30 GMT

Then you can add a second one.

[your_sourcetype]
EVAL-startDate = strptime(startDate,"%Y%m%d%H%M%S.%1NZ")
EVAL-endDate = strptime(endDate,"%Y-%m-%dT%H%M%S.%2NZ")

The important part here is the strptime function. That's what does your conversion.

Re: How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process?

aseid — Tue, 22 Sep 2015 15:24:38 GMT

I still have issues with 'endDate'. One thing I noticed is that the endDate the 'H', 'M' and 'S' fields are separated by a ':' and there is not '.' as you may see from the sample data: 2015-05-09T04:00:00Z. But of course, I tried it by modifying the format within strptime function to reflect these points but in vain.

Re: How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process?

alacercogitatus — Tue, 22 Sep 2015 15:38:05 GMT

Sorry typo. Try this set:

 [your_sourcetype]
 EVAL-startDate = strptime(startDate,"%Y%m%d%H%M%S.%1NZ")
 EVAL-endDate = strptime(endDate,"%Y-%m-%dT%H:%M:%SZ")

Re: How to covert AD date format (eg. 20140602145733.0Z) into a format that Splunk Enterprise Security can process?

aseid — Wed, 23 Sep 2015 13:07:52 GMT

Yeah, I made the following correction and it works: strptime(endDate,"%Y-%m-%dT%H:%M:%SZ"). Thanks for your will and time.