topic Re: strptime works when using makeresults, but not with actual results in Splunk Search

strptime works when using makeresults, but not with actual results

raoul — Tue, 02 Apr 2024 12:53:12 GMT

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.

Re: strptime works when using makeresults, but not with actual results

scelikok — Wed, 03 Apr 2024 05:19:27 GMT

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

Re: strptime works when using makeresults, but not with actual results

bowesmana — Wed, 03 Apr 2024 07:38:57 GMT

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