topic Re: Combining static single-word from lookup table with sentence in Splunk Search

Combining static single-word from lookup table with sentence

alfredoh14 — Thu, 09 May 2024 15:28:45 GMT

hello I need to determine the app name based on a lookup table for the SPL search below.
the SPL search results has a field, called SQL, which has the sql syntax which contains one of the keywords in a field of the lookup table.

I am not sure if join, union, inputlookup, lookup and/or combination of where command will solve this puzzle.
Any help is apreciated.

the lookup file name is:
lookup_weblogic_app.csv
the lookup file sample values are:
lk_wlc_app_short lk_wlc_app_name
ART Attendance Roster Tool
Building_Mailer Building Mailer
SCBT Service Center Billing Tool

SPL search results:
SQL
''' as "FIELD",''Missing Value'' AS "ERROR" from scbt_owner.SCBT_LOAD_CLOB_DATA_WORK
''' as "something ",''Missing Value'' AS "ERROR" from ART_owner.ART_LOAD_CLOB_DATA_WORK
from Building_Mailer_owner.Building_Mailer_

SPL final outcome desire:
lk_wlc_app_short SQL
scbt ''' as "FIELD",''Missing Value'' AS "ERROR" from scbt_owner.SCBT_LOAD_CLOB_DATA_WORK
ATR ''' as "something ",''Missing Value'' AS "ERROR" from ART_owner.ART_LOAD_CLOB_DATA_WORK
Building_Mailer from Building_Mailer_owner.Building_Mailer_

Re: Combining static single-word from lookup table with sentence

danspav — Thu, 09 May 2024 23:13:26 GMT

@alfredoh14,

Here's some SPL that gives you a table with the app name, short name, and SQL:

| makeresults count=3 | streamstats count as id | eval sql=case(id=1,"'' as \"FIELD\",''Missing Value'' AS \"ERROR\" from scbt_owner.SCBT_LOAD_CLOB_DATA_WORK", id=2,"'' as \"something \",''Missing Value'' AS \"ERROR\" from ART_owner.ART_LOAD_CLOB_DATA_WORK", id=3, "from Building_Mailer_owner.Building_Mailer_") | fields sql ``` The above was just to create the source data ``` | rex field="sql" "from\s+(?<lk_wlc_app_short>.+?)_owner" | lookup lookup_weblogic_app lk_wlc_app_short | table lk_wlc_app_short, lk_wlc_app_name, sql

The regular expression pulls out the table name in the SQL, eg "from XXXX_owner", and uses the short code to match the app name from the lookup.

To make the lookup work, you will need to ensure that the matches are NOT case sensitive, or make sure your lookup fields match what is in the SQL.

Re: Combining static single-word from lookup table with sentence

alfredoh14 — Thu, 16 May 2024 02:18:53 GMT

Thank you that is something that i will use if I cannot find anything that would actually do what i need.
the issue is that the lookup file column is one word while the sql field would be many characters.
the example I gave provided a structure wherein you could use rex however, in my real life data, there is no structure, for example, :

scbt_owner could be found as "scbt" or "scbt_owner" or " as scbt" or "where scbt"

if it were those 4 examples i gave, then yes i would be able to use rex but they might be different text in the SQL column.

basically, the issue is that i would like to use the lookup file lk_wlc_app_short , to do a "in" the sql field.
so i would use the lookup file as a base, and if any of the text in the lookup file match the sql field, i would flag them as a match and I would be able to get the final table output I want.

I am not sure if splunk can do this, I know it can do a match if both the sql field and the lk_wlc_app_short field are the same (as you gave me in your example), but can Splunk be able to determine which rows of the lookup file match the SQL field without having to parse with rex since i know the sql field text would be random?

Re: Combining static single-word from lookup table with sentence

yuanliu — Mon, 20 May 2024 20:49:34 GMT

You can make volunteers' life easier by listing sample lookup content in table format, and construct mock/sample SQL values according to illustrated lookup table or vice versa.

Anyway, there are often different ways to solve the same problem depending on actual data characteristics and nuances in requirements. If I understand you correctly, you want to catalogue events into some lk_wlc_app_name based on fragments of SQL that may match lk_wlc_app_short. You mentioned that SQL has no structure (regarding the key strings you are trying to match); your illustrated data suggest that your intended matches do not fall in "natural" word boundaries. This makes any strategy at risk of being too aggressive as to give false positives.

Because of the constraints, one very aggressive strategy is to use wildcard matches. You need to set "Match type" of lk_wlc_app_short to WILDCARD in "Advanced Options", and your table should contain wildcards before and after the short string, like

lk_wlc_app_short	lk_wlc_app_name
ART	Attendance Roster Tool
Building_Mailer	Building Mailer
SCBT	Service Center Billing Tool

Once this is set up, all you need is lookup, like

| lookup lookup_weblogic_app lk_wlc_app_short as SQL

Again, this is perhaps not an optimal solution because look-backward match is expensive.