How to compare fields between db and lookup file and to pick a column value from lookup as resultant field?

sh254087 — Tue, 11 Apr 2023 16:30:30 GMT

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?

topic How to compare fields between db and lookup file and to pick a column value from lookup as resultant field? in Splunk Search

How to compare fields between db and lookup file and to pick a column value from lookup as resultant field?