Splunk Search

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

umsundar2015
Path Finder

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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")

View solution in original post

woodcock
Esteemed Legend

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")

woodcock
Esteemed Legend

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

0 Karma

umsundar2015
Path Finder

Hi Woodcock,

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

niketn
Legend

@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"

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

umsundar2015
Path Finder

thank you ,

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

0 Karma

niketn
Legend

@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"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

javiergn
SplunkTrust
SplunkTrust

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

0 Karma

umsundar2015
Path Finder

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...