Using tokens with a lookup table

[matthew_foos]

Hey all,

Here is my search string I'm using to generate usernames for a drop-down on my dashboard:

index=blahblah source="blahblah"
| lookup cis_users.csv userid AS user
| dedup username
| table username
| sort username

user field looks like abc1234
username field is coming from the lookup First Last (userid)

Search string on the panel:

index=blah source="blah" NOT user="-" NOT user="USER*"
| lookup cis_users.csv userid AS user
| stats earliest(_time) as login latest(_time) as logout by username
| eval diff=logout-login
| eval diff=tostring(diff, "duration")
| convert timeformat="%B %d %Y %I:%M:%S %p" ctime(login)
| convert timeformat="%B %d %Y %I:%M:%S %p" ctime(logout)
| rename username as User, login as "Login Time", logout as "Logout Time", diff as "Time In Portal"

My goal is to have the Username drop down, generate by the lookup, allow me to use the token to retrieve specific information in each dashboard panel by user. I've tried quite a few different combinations for the token (user=$username$, userid=$username$, etc) with no success.

Any help would be mighty fine.

Thanks in advance.

[matthew_foos · Accepted solution]

So, figured this one out myself.

I had duplicate fields.

Added the token input_username with the where command and everything worked.

| where username="$input_username$"

[elliotproebstel]

Ok, let's take this one step at a time. I'll start with the code populating your first dropdown:

index=blahblah source="blahblah"
| lookup cis_users.csv userid AS user
| dedup username
| table username
| sort username

What this says to do:
1. Search index named "blahblah" and source called "blahblah" over some unspecified (to us, anyway - maybe specified in your dashboard code) time window and gather all the events.
2. For each event returned in step 1, use the lookup called "cis_users.csv" to key off the field user (aliased as userid in the lookup file) and return the field username.
3. Dedup all the events so that only one event remains for each username.
4. Make a table of the username values.
5. Sort the values.

If your goal is to limit the options in this dropdown to the usernames that map to users seen during a given time window, this isn't half bad, but it could be more efficient if you use a stats call to effectively dedup the user values before you look them up. That would look like this:

index=blahblah source="blahblah" 
| stats count by user
| lookup cis_users.csv userid AS user
| fields username
| sort 0 username

That should make that first dropdown load much more quickly. Assuming that works, you can use that as the query to populate the first dropdown, and the dropdown can be configured to set the token that will get passed into the second panel. Can you share the code of your existing XML dashboard? You can get there by clicking Edit on the dashboard, then selecting "Source". Please post it by using the 101010 code button, which will allow you to post XML and not have it get eaten/mangled.

[jrballesteros05]

Could you send your XML?

[matthew_foos]

OneCIS User Training

<input type="time" token="input_time" searchWhenChanged="true">
  <label>Time</label>
  <default>
    <earliest>@d</earliest>
    <latest>now</latest>
  </default>
</input>


<panel>
  <table>
    <title>Time In Portal Today</title>
    <search>
      <query>index=trn5 source="*trn5/logs/system/access.log" NOT user="-" NOT user="USER*"

| lookup cis_users.csv userid AS user
| stats earliest(_time) as login latest(_time) as logout by username
| eval diff=logout-login
| eval diff=tostring(diff, "duration")
| convert timeformat="%B %d %Y %I:%M:%S %p" ctime(login)
| convert timeformat="%B %d %Y %I:%M:%S %p" ctime(logout)
| rename username as User, login as "Login Time", logout as "Logout Time", diff as "Time In Portal"
@d
now
5m
delay

none
preview

<panel>
  <table>
    <title>Top 10 URL's</title>
    <search>
      <query>index=trn5 source=*trn5* NOT Category="Maintenance"

| top 15 url
| lookup cis20.csv url as url
| rename url as URL, count as Hits, percent as Percent, category as Category, task as Task
| search NOT Category="Maintenance"
$input_time.earliest$
$input_time.latest$
5m
delay

none
preview

<panel>
  <title>Training Activity</title>
  <chart>
    <title>Top 10 Active Users</title>
    <search>
      <query>index=trn5 source="*trn5/logs/system/access.log" NOT "USER03"

| lookup cis_users.csv userid as user
| top username
| rename username as User, count as Hits, percent as Percent
$input_time.earliest$
$input_time.latest$
5m
delay

pie
none
preview

[matthew_foos]

First column titled username: John Smith (jjs1234)
Second Column is titled userid: JJS1234