Splunk Search

How can I filter different values from the same field ?

pgadhari
Builder

Hi Experts,

I have a field called "Login" in my events, which has various types of values such as "1111@domain1.com", "temp-username@domain1.com, "security@domain1.com", "serviceaccounts@domain1.com".

Now, here I want to create 2 panels, wherein in one panel, it would show only the actual users count considering ( 1111, temp-*, & *security* ) — this would show only users, while the second panel would show only serviceaccounts, which are used by services. This way I can display actual users and service accounts in our application.

Please help me figure out how to achieve this.

Regards
PG

0 Karma
1 Solution

whrg
Motivator

Since you want 2 separate panels (perhaps single value panels), this could be your search for actual user accounts:

| your base search
| search Login="1111@*" OR Login="temp-username@*" OR Login="security@*"
| stats dc(Login) as User_Accounts

The search for service accounts is just negated:

| your base search
| search Login!="1111@*" Login!="temp-username@*" Login!="security@*"
| stats dc(Login) as Service_Accounts

View solution in original post

woodcock
Esteemed Legend

You need to create a users.csv or a serviceaccounts.csv file and create a lookup table with it. Then you can do a search like:

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo AND NOT (|inputlookup serviceaccounts.csv | table Login)

mstjohn_splunk
Splunk Employee
Splunk Employee

hi @pgadhari,

Did you get a chance to try @woodcock 's solution? If it worked, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help ya.

Thanks for posting!

0 Karma

pgadhari
Builder

Actually creating users.csv and serviceaccounts.csv will be tedious task for me, as these are employees and consultants which is a long list. Hence, this solution does not fit my requirements. But thank you @woodcock for your valuable inputs.

0 Karma

pgadhari
Builder

But still, I will upvote @woodcock solution, as for someone that will be definitely helpful. 🙂

0 Karma

whrg
Motivator

Since you want 2 separate panels (perhaps single value panels), this could be your search for actual user accounts:

| your base search
| search Login="1111@*" OR Login="temp-username@*" OR Login="security@*"
| stats dc(Login) as User_Accounts

The search for service accounts is just negated:

| your base search
| search Login!="1111@*" Login!="temp-username@*" Login!="security@*"
| stats dc(Login) as Service_Accounts

pgadhari
Builder

Actually, "1111" is just an example I have given. This value consists of all the empid of 3000 employees (which is between 1 to 4 digits), I want to filter all those values that start with dddd@domain1.com, rest "temp-*" and security value I can filter. The only challenge is I want to filter "1111", "2222", "22","333" and so on. There I am facing the challenge. Please help.

0 Karma

whrg
Motivator

How about this?

| your base search
| eval Is_User_Account=if(match(Login,"^\d{1,4}@") OR match(Login,"^temp-") OR match(Login,"^security"),"yes","no")
| search Is_User_Account="yes"
| stats dc(Login) as User_Accounts

search Is_User_Account="no" for service accounts.

pgadhari
Builder

Hi whrg.. can you please reply on my latest comment ? Actually I need to resolve this issue at the earliest please. Thanks.

0 Karma

pgadhari
Builder

Actually, this query will show the count, but I want to show the usernames also in the table, as to who are normal user accounts and who are service accounts. This I will be showing in a table with giving 2 radio button like - Normal Account Service Accounts:

So if the person, select Normal Account radio button, all those shud be listed and if the person selects "Generic Account" radio button, all generic accounts should be displayed. Please help in that regards ? Thanks.

0 Karma

whrg
Motivator

If you want a table of all accounts along with their respective status, then try this:

| your base search
| dedup Login
| eval Is_User_Account=if(match(Login,"^\d{1,4}@") OR match(Login,"^temp-") OR match(Login,"^security"),"yes","no")
| table Login,Is_User_Account

You can filter for the type of account via a token, which is set by a radio button, after the eval command.

pgadhari
Builder

One more thing in the search below, ^security is showing only small case, it is not showing - "Security" or "SECURITY" in the search, I mean it is case sensitive, how can I make it case insensitive, also can I use * sign instead of ^, so that I get all combinations of security keyword - "match(Login,"security"),"yes","no"). Please help ?

 your base search
 | dedup Login
 | eval Is_User_Account=if(match(Login,"^\d{1,4}@") OR match(Login,"^temp-") OR match(Login,"^security"),"yes","no")
 | table Login,Is_User_Account
0 Karma

pgadhari
Builder

Ya this is working great. But I just figured out that, this field "Login" which I am using is having only email ids (xxxx@domain1.com), it is not showing the Name of the person, instead it is showing empid, temp-*, & service accounts emails.

Actually, there is another field called displayname - which actually shows the first name and last name of that particular emails returning in above query - which is the actual field which I want to show in the dropdown - so that any person can look for the usernames instead of their email-ids. If I use this above query, it will show 1111@domain1.com, temp-personname@domain1.com and so on, but I want to show the Names of the employees in the dropdown. Please let me now how can i do that ? I mean how I can map the field with the field. Please help ? Thanks.

0 Karma

whrg
Motivator

Hello @pgadhari,
Check out this dashboard.
lower() converts a string to lower case.
match() is for matching a regex. The ^ refers to the start of the line. You could also use like() instead.

<form>
  <label>Users</label>
  <fieldset submitButton="false">
    <input type="dropdown" token="displayname">
      <label>displayname</label>
      <choice value="*">All</choice>
      <default>*</default>
      <prefix>displayname="</prefix>
      <suffix>"</suffix>
      <fieldForLabel>displayname</fieldForLabel>
      <fieldForValue>displayname</fieldForValue>
      <search>
        <query>index=... source=... | stats count by displayname</query>
        <earliest>$time.earliest$</earliest>
        <latest>$time.latest$</latest>
      </search>
    </input>
    <input type="time" token="time">
      <label></label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index=... source=... $displayname$
| eval Is_User_Account=if(match(Login,"^\d{1,4}@") OR match(lower(Login),"^temp-") OR match(lower(Login),"security"),"yes","no")
| table Login,displayname,Is_User_Account
| sort Login</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="count">10</option>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</form>

whrg
Motivator

How do you differentiate between user accounts and service accounts? Do all service accounts start with "serviceaccounts@"?

0 Karma

pgadhari
Builder

No, every service account will have the name as "oracleapp@domain1.com", "oracledb@domain1.com" etc.. based on specific applications for which the account is created. Actually the logic would be like - if I filter "1111", "temp-" & "security" from that Login field, I should be able to differentiate between those accounts, but I am finding little bit difficult to do that filtering on a single field. Appreciate if you can help in this ? Thanks.

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...