fuzzy keyword replacement

[codyfawcett]

I have a table of data that has the OS listed, but they are long winded and include useless data.  I would like to clean the field.

So I did this by manually creating a key:value table and using "find replace tool" and that works, but I would like for it to be a fuzzy version so that I would only need to maintain the list of short names and fuzzy logic would replace the long names.

I would like to replace these entries with short names, similar to:

windows 10
windows 7
windows embeded
windows 2008 r2
windows 2012 r2
windows 2012
windows 2016
windows 2008
redhat unknown
oracle unknown
oracle 6.10
oracle 7.4
oracle 6.5
oracle 7.6
oracle vm server release 3.2.9
redhat 6.10
redhat 6.4
redhat 7.1
redhat 7.2
redhat 7.3
redhat 7.4
redhat 7.5
redhat 7.6
suse 11.4
esxi 6.0.0
esxi 6.5.0
centos 7.6

here is the dirty list:

microsoft windows 10 pro version 10.0.17134 build 17134
microsoft windows 7 enterprise version 6.1.7601 build 7601
microsoft windows embedded standard version 6.1.7601 build 7601
microsoft windows server 2008 r2 enterprise version 6.1.7601 build 7601
microsoft windows server 2012 r2 standard version 6.3.9600 build 9600
microsoft windows server 2012 standard version 6.2.9200 build 9200
microsoft windows server 2016 datacenter version 10.0.14393 build 14393
microsoft? windows server? 2008 enterprise version 6.0.6002 build 6002
name="red hat enterprise linux server"
oracle name="oracle linux server"
oracle oracle linux server release 6.10
oracle oracle linux server release 7.4
oracle red hat enterprise linux server release 6.5 (santiago)
oracle red hat enterprise linux server release 7.6 (maipo)
oracle vm server release 3.2.9
red hat enterprise linux server release 6.10 (santiago)
red hat enterprise linux server release 6.4 (santiago)
red hat enterprise linux server release 7.1 (maipo)
red hat enterprise linux server release 7.2 (maipo)
red hat enterprise linux server release 7.3 (maipo)
red hat enterprise linux server release 7.4 (maipo)
red hat enterprise linux server release 7.5 (maipo)
red hat enterprise linux server release 7.6 (maipo)
suse linux enterprise server 11 (x86_64) version = 11 patchlevel = 4
vmware esxi vmnix-x86 6.0.0
vmware esxi vmnix-x86 6.5.0
centos 7.6.1810

CentOS Linux 7.6.1810
Cisco Device
Cisco NX-OS
F5 Networks Big-IP
FreeBSD
HP BladeSystem
Integrated Dell Remote Access Controller / Linux 2.4-2.6 / Embedded Device / F5 Networks Big-IP
Linux 2.4-2.6 / Embedded Device / F5 Networks Big-IP
Linux 2.4-2.6 / Embedded Device / F5 Networks Big-IP / Integrated Dell Remote Access Controller / Linux 2.4-2.6 / Embedded De...
Microsoft Windows Server 2008 R2 Enterprise 6.1.7601 64-bit Service Pack 1 Build 7601
Microsoft Windows Server 2012 R2 Standard 6.3.9600 64-bit N/A Build 9600
NetScaler
Oracle Enterprise Linux 7.4
Oracle Enterprise Linux 7.6
Red Hat Enterprise Linux Server 6.10
Red Hat Enterprise Linux Server 7.2
Red Hat Enterprise Linux Server 7.4
Red Hat Enterprise Linux Server 7.5
Red Hat Enterprise Linux Server 7.6
UNIX
Ubuntu
Ubuntu / Fedora / Tiny Core Linux / Linux 3.x
Ubuntu / Tiny Core Linux / Linux 2.6.x
Ubuntu / Tiny Core Linux / Linux 2.6.x / Integrated Dell Remote Access Controller / Linux 2.4-2.6 / Embedded Device / F5 Netw...
VMWare ESXi
VMware ESXi 5.5.0 build 6480324
VMware ESXi 6.0.0 build 3073146
VMware ESXi 6.5.0 build 10175896
VMware ESXi 6.5.0 build 10719125
VMware ESXi 6.5.0 build 7388607
VMware ESXi 6.5.0 build 8294253
VMware ESXi 6.5.0 build 8434149
VMware vCenter Server Appliance 6.5.0 build 7119157
VMware vCenter Server Appliance 6.5.0 build 7515524
VMware vCenter Server Appliance 6.5.0 build 9451637
Windows 2000 / Windows XP / Windows 2003 / Windows 2000 Service Pack 3-4 / Windows 2003 / Windows XP
Windows 2008 Enterprise Server Service Pack 2
Windows 2008 R2 Enterprise Service Pack 1
Windows 2012 R2 Standard
Windows 2012 R2/8.1
Windows 2012 Standard
Windows 2012 behind F5 Networks Big-IP
Windows 2016
Windows Server 2008 R2 Enterprise 64 bit Edition AD Service Pack 1
Windows Server 2008 R2 Enterprise 64 bit Edition Service Pack 1
Windows Server 2012 Datacenter 64 bit Edition
Windows Server 2012 R2 Datacenter 64 bit Edition
Windows Server 2012 R2 Standard 64 bit Edition
Windows Server 2012 R2 Standard 64 bit Edition AD
Windows Server 2012 Standard 64 bit Edition
Windows Server 2016 Datacenter 64 bit Edition AD Version 1607
Windows Server 2016 Datacenter 64 bit Edition Version 1607
Windows Vista / Windows 2008 / Windows 7 / Windows 2012 / Windows 8 / Windows 10
Windows Vista / Windows 2008 behind F5 Networks Big-IP

[Thableaus · Accepted solution]

Hi @codyfawcett 

Fuzzy Match is an art and is not so easy to get a fine result.

What I did matched a lot of names, but I think some are missing. Most of them due to no similiarity at all.

See workflow attached on version 2018.4

Cheers,

[codyfawcett]

Thanks for the reply, I'm looking through the code and I think I finally understand the fuzzy tool a bit more with a practical application.  thanks.

for now I think I'll maintain a the manual key list, and have filter in place to call out the ones that miss the match.

[codyfawcett]

Thanks for the reply @thableaus, I'm looking through the code and I think I finally understand the fuzzy tool a bit more with a practical application.  thanks.

for now I think I'll maintain a the manual key list, and have filter in place to call out the ones that miss the match.