Getting Data In

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

aseid
New Member

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

0 Karma
1 Solution

alacercogitatus
SplunkTrust
SplunkTrust

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

View solution in original post

alacercogitatus
SplunkTrust
SplunkTrust

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
New Member

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

0 Karma

aseid
New Member

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.

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

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.

0 Karma

aseid
New Member

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.

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

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

somesoni2
Revered Legend

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

0 Karma

aseid
New Member

Both fields are correctly populated; no miss in there.

0 Karma

somesoni2
Revered Legend

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...