All Apps and Add-ons

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

AntoineDRN
Path Finder

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! 

Labels (2)
0 Karma
1 Solution

AntoineDRN
Path Finder

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 !

View solution in original post

0 Karma

AntoineDRN
Path Finder

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 !

0 Karma

nembela
Path Finder

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.

0 Karma

PaulPanther
Builder

Have you already checked the sourcetype with btool?

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

 

0 Karma

AntoineDRN
Path Finder

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

0 Karma

PaulPanther
Builder

Could you provide me a sample event?

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

0 Karma

AntoineDRN
Path Finder

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

Here is a sample event with the wrong time parsing :

AntoineDRN_0-1673530856543.png

 

0 Karma

PaulPanther
Builder

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.

0 Karma

AntoineDRN
Path Finder

Hello Paul,

 

Yes, I already tried this stanza. 

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

Thanks again for your time!

0 Karma

AntoineDRN
Path Finder

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!

Tags (1)
0 Karma

PaulPanther
Builder

@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

 

0 Karma

AntoineDRN
Path Finder

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

0 Karma

davidoff96
Path Finder

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.

0 Karma

AntoineDRN
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...