Getting Data In

What is the Splunk SPL for matching same values and output to an additional column with a newly defined value?

mbasharat
Contributor

Hi,

I have a field named OS

This field is populating multiple values such as below after running the following SPL:

| inputlookup Host.csv
| stats dc(host) as Count by OS
| fields - Count

Result:

WINDOWS NT
WINDOWS SERVER 2003
WINDOWS SERVER 2008
WINDOWS SERVER 2012
LINUX
LINUX 6.7
LINUX 7.0
SOLARIS 9
SOLARIS 10

I want an additional column in results that if:

All the Windows above should display Windows
All the Linux above, should display Linux
and so on in an additional column like below:

alt text

How? I tried to use eval and case but seems like not getting it or having a long day.

Thanks in-advance

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

Based on OS values you posted in question, something like this would work (assuming first letter in the OS value is OS Group)

| inputlookup Host.csv
| stats dc(host) as Count by OS
| fields - Count
| eval OS_Group=mvindex(split(OS," "),0)

If you know that available OS values are Static, you can do this eval-case implementation

| inputlookup Host.csv
| stats dc(host) as Count by OS
| fields - Count
| eval OS_Group=case(match(OS,"LINUX"),"Linux", match(OS,"WINDOWS"), "Windows", match(OS,"SOLARIS"), "Solaris",...other matches will come here..., true(),"Default Group Value Here")

View solution in original post

0 Karma

vnravikumar
Champion

Hi @mbasharat

You can try this also

| inputlookup Host.csv 
| stats dc(host) as Count by OS 
| fields - Count 
| rex field=OS "(?P<os>^([\w\-]+))" 
| eval os = upper(substr(os,1,1)).lower(substr(os,2))
0 Karma

mbasharat
Contributor

Hi @ vnravikumar

This one worked too with slight adjustment with my need. I am going to up-vote this as well. Thanks.

0 Karma

vnravikumar
Champion

Thanks. Please up vote.

somesoni2
Revered Legend

Based on OS values you posted in question, something like this would work (assuming first letter in the OS value is OS Group)

| inputlookup Host.csv
| stats dc(host) as Count by OS
| fields - Count
| eval OS_Group=mvindex(split(OS," "),0)

If you know that available OS values are Static, you can do this eval-case implementation

| inputlookup Host.csv
| stats dc(host) as Count by OS
| fields - Count
| eval OS_Group=case(match(OS,"LINUX"),"Linux", match(OS,"WINDOWS"), "Windows", match(OS,"SOLARIS"), "Solaris",...other matches will come here..., true(),"Default Group Value Here")
0 Karma

mbasharat
Contributor

@ somesoni2

Is it possible to do something like this for matching before and after?

| inputlookup Host.csv
| stats dc(host) as Count by OS
| fields - Count
| eval OS_Group = case(match(OS,"%LINUX%"),"Linux", match(OS,"%WIN%"), "Windows", match(OS,"%SOL%"), "Solaris", true(),"OS")

OR

  • | eval OS_Group = case(match(OS,"asterikLINUXasterik"),"Linux", match(OS,"asterikWINasterik"), "Windows", match(OS,"asterikSOLasterik"), "Solaris", true(),"OS")

I have a long list of different versions of several OSs and I want an additional column so I can categorize them in an OS_Group and then use these for drop down filter menus.

Also, What is this true() doing here?

Thx

0 Karma

somesoni2
Revered Legend

If you're using match function like I mentioned in my answer, you don't have to use a wildcard (e.g. % OR *). The match(OS,"LINUX") will check if value of field OS contains word LINUX (not doing exact match). You'd see the results when you actually run the search. It is same as like(OS,"%LINUS%%") and match(OS,".*LINUX.*").

The function true() (which is always true) is the condition I added to allow default value (means if value of field OS doesn't match any of your match, this value will be used).

0 Karma

mbasharat
Contributor

Sounds good. I used second option and accomodated in my need. Worked great. Thanks @somesoni2

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...