topic Re: Help Fix My Search in Splunk Search

Help Fix My Search

GattyBiggz — Tue, 30 Sep 2025 22:04:53 GMT

index="*azure*" UserId="*#EXT#*" earliest=-300d@d latest=now
| eval activity_time = coalesce(strptime(CreationTime, "%Y-%m-%dT%H:%M:%S"), _time)
| stats latest(activity_time) AS last_seen BY UserId
| eval days_since_last_activity = round((now() - last_seen) / 86400, 0)
| where days_since_last_activity > 180
| eval last_seen_readable = strftime(last_seen, "%Y-%m-%d %H:%M:%S")
| table UserId last_seen_readable days_since_last_activity
| sort -days_since_last_activity

My search is showing users as last active in March however when I dive into the logs for a specific user I can see that same user had activity in May, how can I fix this?

Re: Help Fix My Search

ITWhisperer — Tue, 30 Sep 2025 22:33:49 GMT

If your user's latest activity had a CreationTime of sometime in March, then that's what your search reports, not the time of the latest event.

Re: Help Fix My Search

GattyBiggz — Tue, 30 Sep 2025 22:44:51 GMT

The logs in May also have a CreationTime of May. But my search seems to be showing the oldest log it can find not the latest.

Re: Help Fix My Search

ITWhisperer — Tue, 30 Sep 2025 22:51:43 GMT

Try this

| stats max(activity_time) AS last_seen BY UserId

Re: Help Fix My Search

GattyBiggz — Tue, 30 Sep 2025 23:23:00 GMT

This is the log that is displaying in my search, but I need it to display the most recent, not the oldest.

Thank you

Re: Help Fix My Search

GattyBiggz — Tue, 30 Sep 2025 23:21:26 GMT

So this did not work, it still giving me the log from March. Here is the creation time of the latest log compared to what its giving me.

Re: Help Fix My Search

ITWhisperer — Wed, 01 Oct 2025 00:59:12 GMT

This looks like it might be part of a JSON format event. Please share the full event in raw format in a code block using the </> format button. Please obfuscate any sensitive data as appropriate.

Re: Help Fix My Search

GattyBiggz — Wed, 01 Oct 2025 20:51:31 GMT

{ "AppAccessContext": { "AADSessionId": "*", "ClientAppName": "*", "CorrelationId": "*", "UniqueTokenId": "*" }, "CreationTime": "2025-05-30T21:00:55", "Id": "*", "Operation": "FileAccessed", "OrganizationId": "*", "RecordType": 6, "UserKey": "*", "UserType": 0, "Version": 1, "Workload": "SharePoint", "ClientIP": "*", "UserId": "*", "AuthenticationType": "OAuth", "BrowserName": "", "BrowserVersion": "", "CorrelationId": "*", "EventSource": "SharePoint", "GeoLocation": "*", "IsManagedDevice": false, "ItemType": "File", "ListId": "*", "ListItemUniqueId": "*", "Platform": "*", "Site": "*", "UserAgent": "*", "WebId": "*", "DeviceDisplayName": "*", "HighPriorityMediaProcessing": false, "ListBaseType": 1, "ListServerTemplate": 101, "SiteUrl": "*", "SourceRelativeUrl": "*", "SourceFileName": "*", "SourceFileExtension": "docx", "ApplicationDisplayName": "WebWord", "ObjectId": "*" }

Re: Help Fix My Search

ITWhisperer — Wed, 01 Oct 2025 01:48:31 GMT

It is hard to tell, but it looks like you only have one field which might be used as a timestamp. Are all you events like this? For the events which aren't being picked up as being the latest, how different are they to this format? If they are the same format, what value for _time and CreationTime are they getting?

Re: Help Fix My Search

yuanliu — Wed, 01 Oct 2025 06:32:28 GMT

There is a much simpler explanation - with a slight uncertainty: Your search selects only those users who last appeared more than 180 days ago. 2025-05-30 is 123 days ago as of today 2025-09-30. So, if there is a user who was last seen on 2025-03-02 and a user who was last seen on 2025-05-30, only the former will be shown.

The uncertainty comes from the groupby clause BY UserId. Can you confirm that the user displayed in your actual result (last seen on 2025-03-02) is different from the one last seen 2025-05-30? If they are the same user, there must be a different glitch, especially if you use max. (Maybe the format of CreationTime is not precisely "%Y-%m-%dT%H:%M:%S"?) None the less, that search will never catch a user that appeared on 2025-05-30 till 2025-11-26 21:00:55.

Re: Help Fix My Search

livehybrid — Wed, 01 Oct 2025 20:56:02 GMT

Hi @GattyBiggz

You might need to extract the CreationTime field before the stats:

| eval eventTime=strptime(CreationTime, "%Y-%m-%dT%H:%M:%S") | stats latest(eventTime) as latestTime by UserId

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

Re: Help Fix My Search

yuanliu — Thu, 02 Oct 2025 04:52:42 GMT

This should not be necessary as the OP indicates and illustrates events as JSON objects. Splunk automatically extracts from JSON..

Re: Help Fix My Search

PickleRick — Thu, 02 Oct 2025 06:58:10 GMT

The general idea behind your search is relatively sound but there are some possible issues with it. Most of those doubts come from the fact that we don't know your data and how it was ingested

1. Don't search for "*#EXT#*" if you can avoid it. It will kill performance of your search.

2. The logic behind the coalesce() is not clear. It seems you have another field which is supposedly more important that the event's _time itself. But only in certain cases. That's strange.

3. Are you sure you wanted to use latest() with the stats command? With time-related fields you can choose from first/last, earliest/latest and min/max. There are subtle differences between them and chosing one over another can yield different, possibly wrong, results.

4. I'm not a big fan of rendering timestamps using eval. I'm more of a fieldformat guy.

5. You usually don't want the table command anywhere before the last command. (in this case it doesn't change much but it's worth remembering)