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

[aseid]

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

[alacercogitatus · Accepted solution]

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")

[aseid]

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

[aseid]

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.

[alacercogitatus]

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.

[aseid]

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.

[alacercogitatus]

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")

[somesoni2]

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???

[aseid]

Both fields are correctly populated; no miss in there.

[somesoni2]

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