Splunk Search

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

lohsed
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 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.

0 Karma

lohsed
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

whrg
Motivator

Glad to hear I could help!

0 Karma

whrg
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 workstation_name,ip_addr" you will get the most recent IP address for each workstation.

0 Karma

lohsed
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 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.

0 Karma

prakash007
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...