Checking when a field value has changed

[rleyba828]

Hi team, I have a highly simplified set of log entries similar to the sample data below:

|makeresults |eval dummy="Dec 09 19:43:45 system1 User_name: User1 Client_version: 1.1"
|append [| makeresults |eval dummy= "Dec 11 19:13:42 system1 User_name: User2 Client_version: 1.1"]
|append [| makeresults |eval dummy= "Dec 11 19:26:07 system1 User_name: User3 Client_version: 1.1"]
|append [| makeresults |eval dummy= "Dec 11 19:33:25 system1 User_name: User4 Client_version: 1.1"]
|append [| makeresults |eval dummy= "Dec 12 05:06:14 system1 User_name: User5 Client_version: 1.1"]
|append [| makeresults |eval dummy= "Dec 12 05:07:53 system1 User_name: User1 Client_version: 1.2"]
|append [| makeresults |eval dummy= "Dec 12 08:41:48 system1 User_name: User1 Client_version: 1.2"]
|append [| makeresults |eval dummy= "Dec 13 08:42:48 system1 User_name: User1 Client_version: 1.2"]
|append [| makeresults |eval dummy= "Dec 14 08:43:48 system1 User_name: User2 Client_version: 1.2"]
|append [| makeresults |eval dummy= "Dec 15 08:44:48 system1 User_name: User3 Client_version: 1.2"]
|append [| makeresults |eval dummy= "Dec 16 18:45:48 system1 User_name: User4 Client_version: 1.2"]
|append [| makeresults |eval dummy= "Dec 17 18:46:48 system1 User_name: User1 Client_version: 1.2"]
|append [| makeresults |eval dummy= "Dec 18 18:46:48 system1 User_name: User5 Client_version: 1.1"]

Could someone point me to the SPL query that could show me which user(s) have upgraded their "Client_version" and when? I basically need to track when a field value for a particular user has changed.
In the example set above, I want an output (table or graphs) that shows User1:Dec 12 05:07:53 , User2:Dec 14 08:43:48, User3:Dec 15 08:44:48 and User4:Dec 16 18:45:48

User5 won't show up as his "Client_version" field has not updated.

And in the case of User1, he has logged in multiple times, but I need to see only the timestamp when his "Client_version" field has changed.

Thanks very much.

[to4kawa · Accepted solution]

| makeresults
| eval dummy="Dec 09 19:43:45 system1 User_name: User1 Client_version: 1.1
Dec 11 19:13:42 system1 User_name: User2 Client_version: 1.1
Dec 11 19:26:07 system1 User_name: User3 Client_version: 1.1
Dec 11 19:33:25 system1 User_name: User4 Client_version: 1.1
Dec 12 05:06:14 system1 User_name: User5 Client_version: 1.1
Dec 12 05:07:53 system1 User_name: User1 Client_version: 1.2
Dec 12 08:41:48 system1 User_name: User1 Client_version: 1.2
Dec 13 08:42:48 system1 User_name: User1 Client_version: 1.2
Dec 14 08:43:48 system1 User_name: User2 Client_version: 1.2
Dec 15 08:44:48 system1 User_name: User3 Client_version: 1.2
Dec 16 18:45:48 system1 User_name: User4 Client_version: 1.2
Dec 17 18:46:48 system1 User_name: User1 Client_version: 1.2
Dec 18 18:46:48 system1 User_name: User5 Client_version: 1.1"
| makemv delim="
" dummy
| mvexpand dummy
| rename COMMENT as "this is sample you provide"
| rename COMMENT as "From here, the logic"
| rex field=dummy "(?<time>^.+) (?<system>system\d) User_name: (?<user_name>.+?) Client_version: (?<client_version>.+)"
| eval _time=strptime(time,"%B %d %T")
| table _time system user_name client_version
| streamstats dc(client_version) as session by user_name
| stats earliest(_time) as _time by session user_name
| where session > 1

Hi, @rleyba828
How about this?
and try makemv and mvexpand

[rleyba828]

Excellent! I tried this on my live data, and the logic worked. Thanks very much.

[to4kawa]

you are welcome

[qman]

Hi!

I have a similar issue. I want to check if a url classification of the proxy has changed.

Could anybody explain exactly what was done in that search? I don't get it.

In detail my issue is like:

I have a url that is classified as malicious and all traffic to there is blocked. But maybe 2 or 3 days before the url wasn't classified as malicious and so users were able to get to the site. So I need a search which checks if the classification changed within the last few days.

Any ideas?