Splunk Search
Highlighted

Can you help me build a table based on a lookup table?

New Member

I'm a fairly inexperienced Splunk user that could use some pointers on how to accomplish building a dashboard/table using a lookup table — any advice or guidance is appreciated.

I have a fairly simple lookup table (userlist.csv) of a list of users of this format:

username_1,fullname_1
username_2,fullname_2
username_3,fullname_3
etc.

Basically I want a table with this info:

username_1,fullname_1,workstation_name_1,ip_addr_1
username_2,fullname_2,workstation_name_2,ip_addr_2
username_3,fullname_3,workstation_name_3,ip_addr_3

I can generate a table of that format using a search like this:

| inputlookup userlist.csv | table username,fullname,workstation_name,ip_addr

But of course those last two fields are blank in the table. I have searches for the workstationname (which is based on the username) and the ipaddr (which is based on the workstation_name) but not sure how to tie these into the resulting table so that this info will populate the table.

0 Karma
Highlighted

Re: Can you help me build a table based on a lookup table?

Builder

Hope this works...

index=<index> sourcetype=<sourcetype> 
| lookup userlist.csv username as workstation_name OUTPUT username, fullname
| table username fullname workstation_name ipaddr
0 Karma
Highlighted

Re: Can you help me build a table based on a lookup table?

New Member

Maybe I didn't explain it well - in the lookup table I only have the username and fullname and do not have the workstationname or ipaddr, those both need to be calculated for each entry in the lookup table and then added to the output.

So basically I need to somehow iterate through each row of the input table, from the username do a search to figure out the workstationname, then from the workstationname do another search to find the ip_addr, and display everything in a table.

(note - for the two searches below I am mean ** and ** to be variables, not something in Splunk syntax)

For each username I would do a search similar to this to determine the user's most-used workstation:
eventtype=wineventlogsecurity ** | top SourceWorkstation limit=1

And then this to find the most recent IP for that workstation:
** | top SourceNetworkAddress limit=1

And then I need to add that data to the output table. Using a scripting language you would iterate through each row (username) and calculate/determine the desired values (workstation and ip) and then display it in a table format, but I'm not sure how to do that in Splunk.

0 Karma
Highlighted

Re: Can you help me build a table based on a lookup table?

Motivator

Hi! Try it like this using join commands:

| inputlookup userlist.csv | table username,fullname
| join username type=left [search index=... | stats count by username,workstation_name | sort -count | dedup username | table username,workstation_name]
| join workstation_name type=left [search index=... | dedup workstation_name,ip_addr | table workstation_name,ip_addr]
| table username,fullname,workstation_name,ip_addr

With "| stats count by username,workstation_name | sort -count | dedup username" you will get the user's most-used workstation.

With "| dedup workstationname,ipaddr" you will get the most recent IP address for each workstation.

0 Karma
Highlighted

Re: Can you help me build a table based on a lookup table?

New Member

Hi whrg -

Ah that's very helpful! It's not fully working but it has gotten me much closer. Thanks for the pointers!

0 Karma
Highlighted

Re: Can you help me build a table based on a lookup table?

Motivator

Glad to hear I could help!

0 Karma