Splunk Search

How do you separate fields into different variables based on the beginning letter of field?

Explorer

So I'm sure I'm missing something obvious, but I cannot for the life of me find something similar to what I'm looking for.

I'm trying to take a search that returns all Accounts that have changed their password, and separate them into the 3 different types of accounts I have with a count.

The different accounts start with different letters, which can be searched with Account_Name=R* to get the R account type.

Index=foo EventCode=bar | act1=count(if Account_Name=R*), act2=count(if Account_Name=D*)...etc |table act1 act2 act3

I tried my best to find this, but I simply cannot figure out where to start with that conditional.

Thank you in advance

0 Karma
1 Solution

Motivator

Try this:

index=foo EventCode=bar | eval Account_Type=case(Account_Name=="R*","AccountType1",Account_Name=="D*","AccountType2",Account_Name=="X*","AccountType3",1==1,"unknown" | stats count by Account_Type

View solution in original post

Explorer

noted in a comment below
index=foo EventCode=bar | eval Account_Type=case(like(Account_Name,"R%"),"AccountType1",like(Account_Name,"D%"),"AccountType2",like(Account_Name,"X%"),"AccountType3",1==1,"unknown") | stats count by Account_Type

SplunkTrust
SplunkTrust

You would need to use string match functions of eval e.g. like or match for it.

 index=foo EventCode=bar | eval act1=count(like(Account_Name,"R%"),1,0) , act2=count(like(Account_Name,"D%"),1,0) ,acct3=count(like(Account_Name,"R%"),1,0)  | stats sum(act*) as act*
0 Karma

Explorer

I get the below error with this version of the search

Error in 'eval' command: The 'count' function is unsupported or undefined

0 Karma

Motivator

Try this:

index=foo EventCode=bar | eval Account_Type=case(Account_Name=="R*","AccountType1",Account_Name=="D*","AccountType2",Account_Name=="X*","AccountType3",1==1,"unknown" | stats count by Account_Type

View solution in original post

Explorer

When I do this it labels them all as "unknown" but does contain the correct count next to it.

0 Karma

Motivator

Okay, then modify the query like this:

index=foo EventCode=bar | eval Account_Type=case(like(Account_Name,"R%"),"AccountType1",like(Account_Name,"D%"),"AccountType2",like(Account_Name,"X%"),"AccountType3",1==1,"unknown") | stats count by Account_Type

Explorer

This did require a "," after the first accounttype1 and a parenthesis at the end (for any future users) but this works perfectly thank you! Its been driving me nuts

Motivator

Sorry, edited the typos for convenience. Glad it helped.

If you could mark the answer as accepted it would help future users 😉

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!