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

[AntoineDRN]

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! 

[AntoineDRN · Accepted solution]

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 !

[nembela]

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.

[PaulPanther]

Have you already checked the sourcetype with btool?

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

[AntoineDRN]

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

[PaulPanther]

Could you provide me a sample event?

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

[AntoineDRN]

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

Here is a sample event with the wrong time parsing :

[PaulPanther]

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.

[AntoineDRN]

Hello Paul,

Yes, I already tried this stanza. 

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

Thanks again for your time!

[AntoineDRN]

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!

[PaulPanther]

@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

[AntoineDRN]

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

[davidoff96]

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.

[AntoineDRN]

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