Splunk Search

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

ibdubs
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

DMohn
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

ibdubs
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

somesoni2
Revered Legend

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

ibdubs
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

DMohn
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

ibdubs
Explorer

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

0 Karma

DMohn
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

ibdubs
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

DMohn
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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.