topic Re: How do you separate fields into different variables based on the beginning letter of field? in Splunk Search

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

ibdubs — Thu, 28 Mar 2019 15:03:24 GMT

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

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

DMohn — Thu, 28 Mar 2019 15:17:57 GMT

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

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

somesoni2 — Thu, 28 Mar 2019 15:18:20 GMT

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*

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

ibdubs — Thu, 28 Mar 2019 15:29:44 GMT

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

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

ibdubs — Thu, 28 Mar 2019 15:30:40 GMT

I get the below error with this version of the search

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

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

DMohn — Thu, 28 Mar 2019 15:32:34 GMT

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

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

ibdubs — Thu, 28 Mar 2019 15:46:23 GMT

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

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

ibdubs — Thu, 28 Mar 2019 15:49:55 GMT

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

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

DMohn — Thu, 28 Mar 2019 15:51:49 GMT

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

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