How can I filter different values from the same field ?

[pgadhari]

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

[woodcock]

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]

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!

[pgadhari]

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.

[pgadhari]

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

[whrg · Accepted solution]

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]

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.

[whrg]

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]

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

[pgadhari]

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.

[whrg]

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]

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

[pgadhari]

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.

[whrg]

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]

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

[pgadhari]

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.