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 workstation_name (which is based on the username) and the ip_addr (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.
Hi whrg -
Ah that's very helpful! It's not fully working but it has gotten me much closer. Thanks for the pointers!
Glad to hear I could help!
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 workstation_name,ip_addr" you will get the most recent IP address for each workstation.
Maybe I didn't explain it well - in the lookup table I only have the username and fullname and do not have the workstation_name or ip_addr, 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 workstation_name, then from the workstation_name 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=wineventlog_security ** | top Source_Workstation limit=1
And then this to find the most recent IP for that workstation:
** | top Source_Network_Address 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.
Hope this works...
index=<index> sourcetype=<sourcetype>
| lookup userlist.csv username as workstation_name OUTPUT username, fullname
| table username fullname workstation_name ipaddr