Zulu Time and Convert

[K.Richards]

so my goals is to convert a jsonextract of a few time stamps:
I want to combine them and make a total hours. so start time and expiration time = how many hours total. I cant seem to get the time formatting from a string or I am doing something wrong

  {

    "key": "StartTime",

    "value": "2025-02-14T22:43:21.3182255Z"

  },

  {

    "key": "ExpirationTime",

    "value": "2025-02-15T00:43:21.3182255Z"

[aspatil]

Hello @K.Richards ,

Thanks for reaching out on LiveCommunity!

Could you please confirm what are you doing and what is your end goal?

Regards,

Ashutosh

[K.Richards]

Hello! My end goal is to make a widget showing total hours and minutes a user has requested for a particular role. Those times are the start and expiration of the "checkout"

For example, the dashboard would display.

User / Role / Total time of the checkout request

John Doe / Global / 8 hours

[aspatil]

Hello @K.Richards ,

Can you confirm which logs are you referring to, is it Azure AD event logs or anything else?

[K.Richards]

This is primarily for PIM role checkout logs from Azure

[aspatil]

Do you know in which dataset these logs are getting stored in XDR? IF you are aware about it, then you can alter a new column and use extract_time of start time and expiration time. Then using timestamp_diff()  you can calculate the total hours.

https://docs-cortex.paloaltonetworks.com/r/Cortex-XDR/Cortex-XDR-Documentation/timestamp_diff

[K.Richards]

Thank you for the help but the query keeps failing. "Query not valid" when trying to extract the time.

To get a better picture below is my query/widget so far and its working, but for some reason it will the see the time as a string:

dataset = msft_azure_ad_audit_raw
|alter User = trim(json_extract(initiatedBy, "$.user.userPrincipalName"),"\"")
|alter DisplayName = trim(json_extract(initiatedBy, "$.user.displayName"),"\"")
|alter Requested_Role = trim(json_extract(targetResources, "$[0].displayName"),"\"")
|filter activityDisplayName contains "Add member to role requested"
|alter StarttimeZandDate = split(json_extract(additionalDetails, "$[3].value"),"T")
|alter Starttime = trim(arrayindex(StarttimeZandDate,1),"\"")
|alter EndtimeZandDate = split(json_extract(additionalDetails, "$[4].value"),"T")
|alter ExpirationTime = trim(arrayindex(EndtimeZandDate,1),"\"")
|fields User, DisplayName, Requested_Role , resultreason, Starttime , ExpirationTime
|sort desc _time

and heres everything from where I extracted the data:

[

  {

    "key": "RoleDefinitionOriginId",

    "value": "194ae4cb-b126-40b2-bd5b-6091b380977d"

  },

  {

    "key": "RoleDefinitionOriginType",

    "value": "BuiltInRole"

  },

  {

    "key": "TemplateId",

    "value": "194ae4cb-b126-40b2-bd5b-6091b380977d"

  },

  {

    "key": "StartTime",

    "value": "2025-02-19T16:14:35.9186014Z"

  },

  {

    "key": "ExpirationTime",

    "value": "2025-02-19T17:44:35.9186014Z"

  },

  {

    "key": "Justification",

    "value": "Reviewing Defender alerts: 7432, 7431 and instances of files being quarantined."

  },

  {

    "key": "IsActivationRequireApproval",

    "value": "false"

  },

  {

    "key": "oid",

    "value": "b8e5329d-7133-4fb2-858b-c975cfdfa575"

  },

  {

    "key": "tid",

    "value": "6c3ea66e-576d-4c38-9245-625329590cd2"

  },

  {

    "key": "wids",

    "value": "b79fbf4d-3ef9-4689-8143-76b194e85509"

  },

  {

    "key": "ipaddr",

    "value": "158.120.74.60"

  },

  {

    "key": "RequestId",

    "value": "c47fbed4-692f-439b-8e39-c91d8b64b51c"

  }

[MBeauchamp2]

Would be something like this once you have the start and end dates:

// replace with the timestamp from the logs

| alter start = "2025-02-20T19:00:49.9924647Z" 
| alter end = "2025-02-21T17:00:49.9924647Z"
| alter start_date = parse_timestamp("%Y-%m-%dT%H:%M:%S", arrayindex(regextract(start, "(.*)\."),0))
| alter end_date = parse_timestamp("%Y-%m-%dT%H:%M:%S", arrayindex(regextract(end, "(.*)\."),0))
| alter diff = timestamp_diff(end_date, start_date, "HOUR")
| fields diff, start_date, end_date