I get the impression that your requirement can be reinterpreted as listing the last two installed version and their installed times. Is this accurate? As @bowesmana suggested, this problem would be...
See more...
I get the impression that your requirement can be reinterpreted as listing the last two installed version and their installed times. Is this accurate? As @bowesmana suggested, this problem would be best solved by maintaining a lookup table, then work from there. Any search that does not use a static dataset like lookup is bound to be inefficient because your lookback period cannot be predetermined. As a proof of concept, here is a literal implementation of my interpretation of your requirement. The premise is that you make a search with sufficient coverage for last two versions of packages of interest. Assume that the search returns something like the following _time host package version 2024-01-21 host1 somesoft1 1.2.1 2024-01-21 host2 somesoft2 2.2.3 2024-03-02 host1 somesoft1 1.2.5 2024-03-03 host2 somesoft2 2.3.0 2024-04-10 host1 somesoft1 1.2.10 You then apply the following: <some search with sufficient history>
| stats max(_time) as _time by package version
| eval version = json_object("version", version, "install_time", _time)
| stats list(version) as version_installed by package
| eval version = json_extract(mvindex(version_installed, -1), "version"), "installed date" = json_extract(mvindex(version_installed, -1), "install_time")
| eval last_version = json_extract(mvindex(version_installed, -2), "version"), "last installed date" = json_extract(mvindex(version_installed, -2), "install_time")
| fieldformat "installed date" = strftime('installed date', "%F")
| fieldformat "last installed date" = strftime('last installed date', "%F")
| fields - version_installed This should give a table like this package installed_date last installed date last_version version somesoft1 2024-03-02 2024-04-10 1.2.10 1.2.5 somesoft2 2024-03-03 2024-01-21 2.2.3 2.3.0 What the code really illustrates is the general approach of a semantic "join" without using join command. stats is a lot more efficient in SPL. lookup, using binary search, is another very efficient method. Here is an emulation that produces the mock search output above. Play with it and compare with real data. | makeresults format=csv data="_time,host,package,version
2024-01-21,host1,somesoft1,1.2.1
2024-01-21,host2,somesoft2,2.2.3
2024-03-02,host1,somesoft1,1.2.5
2024-03-03,host2,somesoft2,2.3.0
2024-04-10,host1,somesoft1,1.2.10"
| eval _time = strptime(_time, "%F")
``` data emulation above ```