Format custom date property to sort like "Last Report Time"

(imported topic written by Ivan.FPL)

I’m trying to create a custom property (specifically, the “Enrollment Time” property from the “Device Enrollment Status” analysis) to appear and sort similar to the “Last Report Time” property. Although I was able to format the output string with the relevance below, I noticed it wasn’t sorting nicely like “Last Report Time” does. Then after looking at the “Last Report Time” relevance, I observed that the console must be doing something special with that property because the output should be of the form “Thu, 30 May 2013 15:49:02 +0000” but is instead being displayed as “5/30/2013 11:49:02 AM” within the columns. Besides the relevance below, is there something I can do to make this property appear and behave exactly like “Last Report Time”?

The below relevance converts from “Thu, 30 May 2013 15:49:02 +0000” to “05/30/2013 11:49:02 AM” (month and day are two digits)

((month of it as two digits & “/” & day_of_month of it as two digits & “/” & year of it as string) of (date (local time zone) of (enrollment time of mdm server))) & " " & (((if (hour_of_day of it > 12) then (hour_of_day of it - 12) else (if (hour_of_day of it = 0) then (12) else (hour_of_day of it))) as string & “:” & (two digit minute of it) as string & “:” & (two digit second of it) as string & " " & (if (hour_of_day of it > 11) then (“PM”) else (“AM”))) of (time (local time zone) of (enrollment time of mdm server)))

(imported comment written by Ivan.FPL)

No ideas? Has anyone attempted something similar?

I would also like to know this

The ‘Last Report Time’ value in the Console is in fact treated specially, and follows the date/time formatting configuration of the OS where the Console is running.

As there is not currently a means to assign specific data types to properties, all custom properties are essentially treated like text/strings, and so follow string sorting logic.

(anticipating next question)
A time object can be formatted as a sortable string via

q: ((year of it as string & "_" & first 2 of ("0" & (month of it as integer as string)) & "_" &  first 2 of ("0" & day_of_month of it as integer as string)) of (date (local time zone) of it) & " " &  (two digit hour of it & ":" & two digit minute of it & ":" & two digit second of it ) of time (local time zone) of it) of now
A: 2017_07_02 19:37:02
T: 0.150 ms
I: singular string
1 Like

Jason, hugely appreciate this snippet that got me on the right track for this, however, what you’re doing with first 2 of ("0" & ... doesn’t work for any month or day >=10 as it will always take the first 2 of e.g. “015” and result in a “01”.

Here’s a better version, for anyone else who makes it here in the future -

((year of it as string & "_" & (if (it < 10) then "0" & (it as string) else (it as string)) of (month of it as integer) & "_" & (if (it < 10) then "0" & (it as string) else (it as string)) of (day_of_month of it as integer)))
date (local time zone) of it

This is for year, month, day only as nothing needs to be changed for time.

You’re absolutely right…I meant to use

last 2 of (“0” & (month of it as integer as string))

Same with ‘day of month’. The idea being that if it is less than two digits, the result is padded with a 0; and if it’ already two digits, the 0 is truncated off the front and we end up with the last two digits.

Once the concept is understood, I find it much cleaner logic to pad in this way, especially if you have to pad for larger numbers (suppose, for a four-digit field).
The way I presented would end up with

last 4 of (“0000” & (whatever integer as string))

Where an if/then would expand quickly to something like

(if it > 999 then it else if it > 99 then (“0” & it as string) as integer else if it > 9 then (“00” & it as string) as integer else (“000” & it as string) as integer) of (some number)

Yep, my brain didn’t work well enough to figure out it just needs to be last 2 instead of first 2. Thanks for clearing that up, that makes perfect sense.

My excuse is that it’s monday here…