Help Fix My Search

[GattyBiggz]

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? 

[CreolaHegmann]

I learned a lot from this thread.

[PickleRick]

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)

[yuanliu]

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.

[ITWhisperer]

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.

[GattyBiggz]

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. 

[ITWhisperer]

Try this

| stats max(activity_time) AS last_seen BY UserId

[GattyBiggz]

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. 

[ITWhisperer]

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.

[GattyBiggz]

{
    "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": "*"
}

[ITWhisperer]

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?

[GattyBiggz]

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

Thank you 

[livehybrid]

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

[yuanliu]

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