Find / Search Function

[jonnyp138]

Good evening all, I am hoping someone can help me out, I have a report to get out for Monday and I am struggling on one section, its a bit hard to explain but I will do my best:

I have a report of all error messages that come from a servers event log in a spreadsheet represented in a column called "Brief Description" and I have created a new column and called Issue and I have to write a function that will extract the issue from the event message, an example of some of the events and their respective issue are as follows:

Message Issue
Server101- The Virtual Servers Processor Performance is at 94.25% : WARNING Processor
Server143 - Total CPU Usage is at 54.24% CPU
Server152 - Networking Functions monitoring is CRITICAL Networking Functions
Active Directory FSMO Role LDAP connections allowed check for Domain Naming Master monitored from SERVER423 is in state CRITICAL - Alert generated if FSMO role not accepting LDAP connections | LDAP Connections

Server01 - Logical Disk I: has 11.03 Available Space (%) Disk
Hope that makes sense.
Thanks in advance

 

[jonnyp138]

Ok this may make this a little easier, in another table I have a list of the most common issues such as:

This is a new table
Operating System
NT Service
Windows Events |
SQL Server
Disk
CPU
Processor
Bandwidth Utilization
System Uptime
Windows Event Log Monitor
LDAP connections
Unable to Ping device
Connectivity has returned
Memory Performance
Windows Service
Windows monitoring
Parameter Connect Status
vserverTable Container
Networking Functions
patrol
Memory usage
VirtualCenter
Free Memory
Windows 2003 monitoring
Hardware
Port
The Server has been restarted
communication
Filesystem Capacity

So what I would like to do is search the message ^^Examples above and in the message the first "keyword" it comes across that is in this keyword table should appear in the issues column

 

[MarcelBeug]

My advice would be to create a table, let’s say table “tabIssues”, with 2 fields:
Field “String” with strings to look for in the error messages, and
Field “Issue” with categories.

Examples:
String “Networking” with Issue “Networking Functions”
String “CPU” with issue “CPU”.

If your error messages start in A2, then enter in B2 array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER) and copy down:

=INDEX(tabIssues[Issue],SMALL(IF(ISNUMBER(FIND(tabIssues[String],A2)),ROW(INDIRECT("1:"&COUNTA(tabIssues[Issue])))),1))