strptime works when using makeresults, but not with actual results

[raoul]

I have a dataset of user data including the user's LastLogin. The LastLogin field is slightly oddly formatted but very regular in it's pattern. I wish to calculate the number of days since LastLogin. This should be super simple.

What is bizarre is that in a contrived example using makeresults it works perfectly.

| makeresults
| eval LastLogin="Mar 20, 2024, 16:40"
| eval lastactive=strptime(LastLogin, "%b %d, %Y, %H:%M")
| eval dayslastactive=round((now() - lastactive) / 86000, 0)

This yields:

But with the actual results the same transformations do not work. 

| inputlookup MSOLUsers 
| where match(onPremisesDistinguishedName, "OU=Users") 
| where not isnull(LastLogin)
| eval LastActive=strptime(LastLogin, "%b %d, %Y, %H:%M")
| eval DaysLastActive=round((now() - LastActive) / 86000, 0)
| fields Company, Department, DisplayName, LastLogin, LastActive, DaysLastActive

This yields:

What am I missing? Cutting and pasting the strings into the makeresults form gives what I would expect. 

[bowesmana]

Minor point, but the number of seconds in a day is 86400, not 86000.

[scelikok · Accepted solution]

Hi @raoul,

Maybe spaces in your LastLogin field are unprintable characters. Can you try below query which cleans all whitespace?

| inputlookup MSOLUsers 
| where match(onPremisesDistinguishedName, "OU=Users") 
| where not isnull(LastLogin)
| eval LastLogin=replace(LastLogin,"[^A-Za-z0-9,:]+","")
| eval LastActive=strptime(LastLogin, "%b%d,%Y,%H:%M")
| eval DaysLastActive=round((now() - LastActive) / 86000, 0)
| fields Company, Department, DisplayName, LastLogin, LastActive, DaysLastActive