- Access exclusive content
- Connect with peers
- Share your expertise
- Find support resources
02-14-2025 03:53 PM
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
02-17-2025 04:04 AM
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
02-17-2025 09:36 AM
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
02-17-2025 10:44 PM
Hello @K.Richards ,
Can you confirm which logs are you referring to, is it Azure AD event logs or anything else?
02-18-2025 01:37 PM
This is primarily for PIM role checkout logs from Azure
02-19-2025 07:37 AM - edited 02-19-2025 07:37 AM
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
02-19-2025 09:40 AM
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:
02-20-2025 02:43 PM
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
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!