Zulu Time and Convert

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Zulu Time and Convert

L1 Bithead

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

L5 Sessionator

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

Ashutosh Patil

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

L5 Sessionator

Hello @K.Richards ,

 

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

 

Ashutosh Patil

L1 Bithead

This is primarily for PIM role checkout logs from Azure

L5 Sessionator

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

 

 

Ashutosh Patil

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

L4 Transporter

 

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

  • 467 Views
  • 7 replies
  • 0 Likes
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!