I have two data sources - 1. Discovered data. Can be either a lookup file or a db table. Let's assume db table. I'm pulling data from table using splunk app for DB connect. 2. A lookup file.
Scenario: I will have two columns OS_name and OS_Version coming from the discovery_data db table. I will also have a os_latest_release.csv lookup file which will have three columns - OperatingSystem, Existing_OSType, Latest_Available_Version.
| OperatingSystem |
ExistingOSType |
Latest_Available_Version |
| AIX |
any |
7.3 |
| HP-UX |
any |
11i v3 |
| Linux |
any |
RHEL 9.1 |
| Linux |
SuSE Enterprise Server 11 (3.0.101-108.2 |
SUSE Linux Enterprise Server 15 |
| Linux |
ubuntu_12_04 |
Ubuntu 22.10 |
| Linux |
Oracle Linux Server 7.9 |
Oracle Linux 9.1 |
| Linux Debian |
any |
Debian version 11 |
| Linux Lincase |
any |
LINUX Lincase SL 7.9 |
| Other |
other |
TBC |
| Solaris |
any |
Solaris 11.4 |
| SunOS/Solaris |
any |
Solaris 11.4 |
| VMware |
any |
vSphere ESXi 8.0 |
| Windows |
Windows 10 |
Windows 11 version 22H2 |
| Win2012R2 6.3.9600 |
any |
Windows Server 2022 |
| Windows |
any |
Windows Server 2022 |
For every OS_name and OS_version there will be a matching combination/entry in the lookup file.
The ExistingOSType column in lookup file will either have the version details similar (not necessarily same, strings may not match as-is) to discovered data or will have 'any' as field value (i.e., no matter which discovered version or os type be, the latest version will be same for the given OS_name).
Now I have to come up with latest Latest_Available_Version column for each row of data coming from db table. Conditions to be considered - 1. if db.OS_name=L.OperatingSystem and db.OS_version=L.Existing_OSType then Latest_Available_Version . if there's no match, then 2. set db.Latest_Available_Version =l.Latest_Available_Version where db.OS_name=L.OperatingSystem and L.Existing_OSType=any
Any help, please?
