topic Re: how to separate the value into two different fields using like function in Splunk Search

how to separate the value into two different fields using like function

umsundar2015 — Thu, 06 Apr 2017 09:59:49 GMT

For example i have the field ,

description field like KM - PROD - MSSQL 2008 VA
DC - PROD - MSSQL 2012 VA
KM - WDC - MSSQL VA

Here i need to use like function in description

myquery|eval Description=case(LIKE(Description,"%MSSQL%"),"MySQL Database",like (Description,"% MSSQL 20%"),"Microsoft Sql server Database")).

But here iam getting only MySQL Database and the values with "DC - PROD - MSSQL 2012 VA" was not segregated like Microsoft Sql server Database.
I am also using this like function for filtering some more values like oracle etc in this.
Please suggest me a way to do this .

Thanks ,
ums

Re: how to separate the value into two different fields using like function

javiergn — Thu, 06 Apr 2017 10:34:42 GMT

If I understand correctly you just want to group your databases by type, in that case you can use the case statement with match like this:

| eval databaseType = case(
    match(Description, "MySQL"), "MySQL Database",
    match(Description, "MSSQL"), "Microsoft Sql server Database",
    match(Description, "Oracle"), "Oracle Database",
    1==1, "Unknown Database"
)

Is that what you are looking for or did I miss something?

Thanks,
J

Re: how to separate the value into two different fields using like function

niketn — Thu, 06 Apr 2017 10:37:56 GMT

@umsundar2015, your first condition is always true. You need to define case conditions in a way that your it matches only one of the conditions or else it will always pick the first one. Test out with the following run anywhere search (change DBType to test various values):

| makeresults
| eval DBType="KM - PROD - MSSQL 2008 VA"
| eval Description=case(like(DBType,"%MSSQL VA"),"MySQL Database",like(DBType,"%MSSQL 20%VA"),"Microsoft Sql server Database")
| table DBType Description

PS: define default condition in case block in case DB Pattern is not matched using true() or 1==1 condition and set DB to "Unknown DB"

Re: how to separate the value into two different fields using like function

gcusello — Thu, 06 Apr 2017 10:44:28 GMT

Hi umsundar2015,
why you don't extract fields from your events using something like this?

^(?<Field1>[^ ]*)\s-\s(?<Field2>[^ ]*)\s-\s(?<Field3>.*)

(you can test it at https://regex101.com/r/fjv7TF/1)
And after you can run searches on the single fields.
To correctly address your field, instead to use like function (that's slow!) you could create a lookup containing all the values of your DB and the description to show:
lookup

DB,Description
MSSQL 2008 VA,Microsoft Sql server Database
MSSQL 2012 VA,Microsoft Sql server Database
MSSQL VA,MySQL Database
........

so you can add lookup values to your searches obtaining something like this:

your_search
| lookup databases DB OUTPUT Description
| table _time description

Bye.
Giuseppe

Re: how to separate the value into two different fields using like function

umsundar2015 — Thu, 06 Apr 2017 12:29:22 GMT

thanks you ,

But if i write match function , i have to write many match functions .Because i have many values related to that and the above is just an example.

Pls suggest me different way

Re: how to separate the value into two different fields using like function

umsundar2015 — Thu, 06 Apr 2017 12:30:50 GMT

thank you ,

This is not working , i tried this but all the values are getting populated in MYSQL only

Re: how to separate the value into two different fields using like function

woodcock — Thu, 06 Apr 2017 14:49:46 GMT

The problem is that you have to go from most unique to less unique order in your case statement. The way that you have it now, the first match is so generic, it gobbles up the other more-specific cases on the first line and in a case statement, the first match wins. So we just re-order your case statement like this:

myquery
|eval Description=case(like(Description,"% MSSQL 20%"), "Microsoft Sql server Database",
                       like(Description,"%MSSQL%"    ), "MySQL Database",
                                                true(), "OTHER/UNKNOWN/ERROR")

Re: how to separate the value into two different fields using like function

javiergn — Thu, 06 Apr 2017 15:31:16 GMT

If your Description field has always the same format (VALUE1 - VALUE2 - DBTYPE) you could simply do:

| eval databaseType = mvindex(split(Description, "-"), 2)

Or use regex:

| rex field=Description "\-(?<databaseType>[^\-]+)$"

And then play around with the databaseType field to get rid of numeric values, etc.

Re: how to separate the value into two different fields using like function

woodcock — Thu, 06 Apr 2017 18:30:26 GMT

Perhaps also you meant MYSQL instead of MSSQL for the MySQL Database entry?

Re: how to separate the value into two different fields using like function

niketn — Fri, 07 Apr 2017 03:33:56 GMT

@umsundar2015.... You dont need first two lines, instead your base search will be present. I have used makeresults just as an example which you can use for testing... Keep replacing the second line above with various DB types and test. Once you find correct conditions, you can put the 3rd and 4th line back in your base search.

If following is your MySQL Server name(as per your example), you can replace the same and test out the above query.

 | eval DBType="KM - WDC - MSSQL VA"

Re: how to separate the value into two different fields using like function

umsundar2015 — Fri, 07 Apr 2017 06:53:57 GMT

Hi Woodcock,

Thank you so much .
This works the way which i expected .