topic Zulu Time and Convert in Cortex XDR Discussions

Zulu Time and Convert

K.Richards — Fri, 14 Feb 2025 23:53:13 GMT

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"
 

Re: Zulu Time and Convert

aspatil — Mon, 17 Feb 2025 12:04:09 GMT

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

Re: Zulu Time and Convert

K.Richards — Mon, 17 Feb 2025 17:36:26 GMT

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

Re: Zulu Time and Convert

aspatil — Tue, 18 Feb 2025 06:44:49 GMT

Hello @K.Richards ,

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

Re: Zulu Time and Convert

K.Richards — Tue, 18 Feb 2025 21:37:20 GMT

This is primarily for PIM role checkout logs from Azure

Re: Zulu Time and Convert

aspatil — Wed, 19 Feb 2025 15:37:35 GMT

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

Re: Zulu Time and Convert

K.Richards — Wed, 19 Feb 2025 17:40:53 GMT

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

Re: Zulu Time and Convert

MBeauchamp2 — Thu, 20 Feb 2025 22:43:45 GMT

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