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.
Hope this works...
index=<index> sourcetype=<sourcetype> | lookup userlist.csv username as workstation_name OUTPUT username, fullname | table username fullname workstation_name ipaddr
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.
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.