topic Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly in All Apps and Add-ons

Splunk TA for Microsoft Office 365 can't parse timestamp correctly?

AntoineDRN — Mon, 16 Jan 2023 19:44:02 GMT

Hello Splunkers,

I am currently facing a problem and can't find any documentation.

Let me explain, we are using the Splunk_TA_o365 mostly for sign-in logs. The issues is that any of this logs have the right timestamp.

For these sign in logs, the timestamp is stored in the "createdDateTime" field, and not in the "timestamp" field like other events. So I tried to "fix" it with the local/props.conf with the stanza :

[o365:graph:api] TIME_PREFIX = ("createdDateTime":\s*")|timestamp TIME_FORMAT = %Y-%m-%dT%H:%M:%S KV_MODE = json TZ = UTC

And it didn't work at all, but when I tried (and I know it is REALLY not recommended in the best practice) to write the same stanza in the default/props.conf, it surprizingly worked.

So I was wondering if it was a normal behavior (which I'd find strange), or if there is another solution that could be more sustainable than modifying the default folder.

Thanks in advance for your time,

Best regards and Happy splunking!

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

PaulPanther — Thu, 12 Jan 2023 13:16:07 GMT

Have you already checked the sourcetype with btool?

./splunk btool props list o365:graph:api --debug

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

AntoineDRN — Thu, 12 Jan 2023 13:23:26 GMT

Yes, I did. In the both case (local/default) I had the same result which is normal, but the timestamp field within Splunk Web is sadly not recognize when the stanza is in local/props.conf

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

PaulPanther — Thu, 12 Jan 2023 13:27:43 GMT

Could you provide me a sample event?

With local/default you mean system/local or app/local?

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

AntoineDRN — Thu, 12 Jan 2023 13:41:23 GMT

The changes have been made in the app/default or local/props.conf

Here is a sample event with the wrong time parsing :

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

PaulPanther — Thu, 12 Jan 2023 15:36:46 GMT

Have you already tried to configure the props.conf under local as

[o365:graph:api] TIMESTAMP_FIELDS = timestamp, createdDateTime KV_MODE = json TZ = UTC

If yes, please provide me the _raw event that I can copy it and use it in my test environment. Feel free to anonymize all confidential fields.

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

AntoineDRN — Mon, 16 Jan 2023 11:08:59 GMT

Hello Paul,

Yes, I already tried this stanza.

I sadly can't provide raw data due to internal policies.

Thanks again for your time!

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

AntoineDRN — Tue, 17 Jan 2023 11:20:11 GMT

Hello @PaulPanther,

I get two events fully anonymized that I can show.

{ "preview": false, "result": { "_raw": "{\"id\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"createdDateTime\": \"2023-01-17T10:25:15Z\", \"userDisplayName\": \"FirstName LASTNAME\", \"userPrincipalName\": \"xxxx@xxxxxxxxxxxxxxxx.xxxxxxx.xxx\", \"userId\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"appId\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"appDisplayName\": \"Windows Sign In\", \"ipAddress\": \"xxx.xxx.xxx.xxx\", \"clientAppUsed\": \"Mobile Apps and Desktop clients\", \"correlationId\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"conditionalAccessStatus\": \"notApplied\", \"isInteractive\": true, \"riskDetail\": \"none\", \"riskLevelAggregated\": \"none\", \"riskLevelDuringSignIn\": \"none\", \"riskState\": \"none\", \"riskEventTypes\": [], \"riskEventTypes_v2\": [], \"resourceDisplayName\": \"Windows Azure Active Directory\", \"resourceId\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"status\": {\"errorCode\": 0, \"failureReason\": \"Other.\", \"additionalDetails\": null}, \"deviceDetail\": {\"deviceId\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"displayName\": \"X-XXXXXX-xxxxxx\", \"operatingSystem\": \"Windows\", \"browser\": \"\", \"isCompliant\": true, \"isManaged\": true, \"trustType\": \"Azure AD joined\"}, \"location\": {\"city\": \"XXXXXXX\", \"state\": \"XXXXXXXX\", \"countryOrRegion\": \"XX\", \"geoCoordinates\": {\"altitude\": null, \"latitude\": XXXXX, \"longitude\": XXXXX}}, \"appliedConditionalAccessPolicies\": []}", "_time": "2023-01-17T11:35:09.000+0100", "action": "notApplied", "app": "Windows Sign In", "appDisplayName": "Windows Sign In", "appId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "clientAppUsed": "Mobile Apps and Desktop clients", "conditionalAccessStatus": "notApplied", "correlationId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "createdDateTime": "2023-01-17T10:25:15Z", "deviceDetail.browser": "", "deviceDetail.deviceId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "deviceDetail.displayName": "X-XXXXXX-xxxxxx", "deviceDetail.isCompliant": "true", "deviceDetail.isManaged": "true", "deviceDetail.operatingSystem": "Windows", "deviceDetail.trustType": "Azure AD joined", "yyyySite": "XXX", "yyyyZone": "XXX", "eventtype": [ "o365_graph_api", "o365_signins" ], "host": "xxxxxx", "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "index": "o365", "ipAddress": "xxx.xxx.xxx.xxx", "isInteractive": "true", "linecount": "1", "location.city": "XXXXXX", "location.countryOrRegion": "XX", "location.geoCoordinates.altitude": "null", "location.geoCoordinates.latitude": "XXXXXXX", "location.geoCoordinates.longitude": "XXXXXX", "location.state": "XXXXXXX", "punct": "{\"\":_\"----\",_\"\":_\"--::\",_\"\":_\"_\",_\"\":_\"@..\",_\"\":_\"", "reason": "Other.", "resourceDisplayName": "Windows Azure Active Directory", "resourceId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "riskDetail": "none", "riskLevelAggregated": "none", "riskLevelDuringSignIn": "none", "riskState": "none", "source": "AuditLogs.SignIns", "sourcetype": "o365:graph:api", "splunk_server": "xxxxxx", "src": "xxx.xxx.xxx.xxx", "src_ip": "xxx.xxx.xxx.xxx", "status": "0", "status.additionalDetails": "null", "status.errorCode": "0", "status.failureReason": "Other.", "tag": "authentication", "tag::eventtype": "authentication", "timestamp": "none", "user": "xxxx@xxxxxxxxxxxxxxxx.xxx", "userDisplayName": "FirstName LASTNAME", "userId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "userPrincipalName": "xxxx@xxxxxxxxxxxxxxxx.xxxxxxx.xxx" } } { "preview": false, "lastrow": true, "result": { "_raw": "{\"id\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"createdDateTime\": \"2023-01-17T09:53:26Z\", \"userDisplayName\": \"FirstName LASTNAME\", \"userPrincipalName\": \"xxxx@xxxxxxxxxxxxxxxx.xxxxxxx.xxx\", \"userId\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"appId\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"appDisplayName\": \"Windows Sign In\", \"ipAddress\": \"xxx.xxx.xxx.xxx\", \"clientAppUsed\": \"Mobile Apps and Desktop clients\", \"correlationId\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"conditionalAccessStatus\": \"notApplied\", \"isInteractive\": true, \"riskDetail\": \"none\", \"riskLevelAggregated\": \"none\", \"riskLevelDuringSignIn\": \"none\", \"riskState\": \"none\", \"riskEventTypes\": [], \"riskEventTypes_v2\": [], \"resourceDisplayName\": \"Windows Azure Active Directory\", \"resourceId\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"status\": {\"errorCode\": 50126, \"failureReason\": \"Error validating credentials due to invalid username or password.\", \"additionalDetails\": \"The user didn't enter the right credentials. \\xxxxxxxx's expected to see some number of these errors in your logs due to users making mistakes.\"}, \"deviceDetail\": {\"deviceId\": \"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\", \"displayName\": \"X-XXXXXX-xxxxxx\", \"operatingSystem\": \"Windows\", \"browser\": \"\", \"isCompliant\": false, \"isManaged\": true, \"trustType\": \"Azure AD joined\"}, \"location\": {\"city\": \"XXXXX\", \"state\": \"XXXXX\", \"countryOrRegion\": \"XX\", \"geoCoordinates\": {\"altitude\": null, \"latitude\": XXXXX, \"longitude\": XXXXXXX}}, \"appliedConditionalAccessPolicies\": []}", "_time": "2023-01-17T11:00:12.000+0100", "action": "notApplied", "app": "Windows Sign In", "appDisplayName": "Windows Sign In", "appId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "clientAppUsed": "Mobile Apps and Desktop clients", "conditionalAccessStatus": "notApplied", "correlationId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "createdDateTime": "2023-01-17T09:53:26Z", "deviceDetail.browser": "", "deviceDetail.deviceId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "deviceDetail.displayName": "X-XXXXXX-xxxxxx", "deviceDetail.isCompliant": "false", "deviceDetail.isManaged": "true", "deviceDetail.operatingSystem": "Windows", "deviceDetail.trustType": "Azure AD joined", "yyyySite": "XXX", "yyyyZone": "XXX", "eventtype": [ "err0r", "o365_graph_api", "o365_signins" ], "host": "xxxxxxxx", "id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "index": "o365", "ipAddress": "xxx.xxx.xxx.xxx", "isInteractive": "true", "linecount": "1", "location.city": "Xxxxxx", "location.countryOrRegion": "XX", "location.geoCoordinates.altitude": "null", "location.geoCoordinates.latitude": "XXXXXX", "location.geoCoordinates.longitude": "XXXXXX", "location.state": "XXXXXXX", "punct": "{\"\":_\"----\",_\"\":_\"--::\",_\"\":_\"_\",_\"\":_\"@..\",_\"\":_\"", "reason": "Error validating credentials due to invalid username or password.", "resourceDisplayName": "Windows Azure Active Directory", "resourceId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "riskDetail": "none", "riskLevelAggregated": "none", "riskLevelDuringSignIn": "none", "riskState": "none", "source": "AuditLogs.SignIns", "sourcetype": "o365:graph:api", "splunk_server": "xxxxxxx", "src": "xxx.xxx.xxx.xxx", "src_ip": "xxx.xxx.xxx.xxx", "status": "50126", "status.additionalDetails": "The user didn't enter the right credentials. It's expected to see some number of these errors in your logs due to users making mistakes.", "status.errorCode": "50126", "status.failureReason": "Error validating credentials due to invalid username or password.", "tag": [ "authentication", "error" ], "tag::eventtype": [ "authentication", "error" ], "timestamp": "none", "user": "xxxx@xxxxxxxxxxxxxxxx.xxxxxxx.xxx", "userDisplayName": "firstName LASTNAME", "userId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", "userPrincipalName": "xxxx@xxxxxxxxxxxxxxxx.xxxxxxx.xxx" } }

Here it is, hope you can find something.

Best Regards!

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

PaulPanther — Tue, 17 Jan 2023 14:27:47 GMT

@AntoineDRN Thank you for the events!

Could you please try below settings in your local props.conf

[o365:graph:api] CHARSET=AUTO KV_MODE=json SHOULD_LINEMERGE=true TZ=UTC disabled=false LINE_BREAKER=([\r\n]+) TIME_PREFIX="createdDateTime"\:\s" TIME_FORMAT=%Y-%m-%dT%H:%M:%S

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

AntoineDRN — Thu, 19 Jan 2023 16:06:49 GMT

Hello @PaulPanther ,

I will try it for sure, and let you know how it's going.

Hope it will works, thanks for your help!

Best Regards!

Antoine

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

davidoff96 — Thu, 19 Jan 2023 21:29:33 GMT

One other thing to note is you may need to change your "MAX_TIMESTAMP_LOOKAHEAD" in the props.conf since the default is 128. Wouldnt explain why it works in default vs local, but something to consider.

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly?

nembela — Mon, 23 Jan 2023 15:13:10 GMT

Hi,

I also have/had this problem and only modification of the default/props.conf solved the problem.

@davidoff96: Luckily the "MAX_TIMESTAMP_LOOKAHEAD" value is not a problem because createdDateTime is the second field in the raw json data.

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly

AntoineDRN — Tue, 07 Feb 2023 15:37:02 GMT

Hello @PaulPanther , @davidoff96 ,

Whatever I tried nothing worked sadly.

I also reach the support, who confirms that the only workaround is to modify the default/props.conf . Even if this work I found this is not a sustainable solution for platforms that are beginning to be wide.

I also found that modifying the datetime.xml may correct this issue without having to modify the default folder.

I can't try this for know but i'll try as soon as I have time.

Best regards

Re: Splunk TA for Microsoft Office 365 can't parse timestamp correctly?

AntoineDRN — Tue, 16 May 2023 08:36:28 GMT

After long discussion with the support, here is a workaround that work for us :

In the $SPLUNK_HOME/etc/apps/splunk_ta_o365/local/props.conf

[o365:graph:api]

TIME_PREFIX = ("createdDateTime":\s*")|timestamp

TIMESTAMP_FIELDS =

TIME_FORMAT = %Y-%m-%dT%H:%M:%S

KV_MODE = json

TZ = UTC

Hope it can help someone !