topic How do I create a table from 3 different lookup table and get the data to line up? in Splunk Dev

How do I create a table from 3 different lookup table and get the data to line up?

lowden — Tue, 21 Feb 2017 00:12:07 GMT

I have 3 lookups. 1 is primary users and a count of total users, 2 is primary users and a task, 3 is primary users and not doing task.

I tried to combine their data using stats count to get the data and appendcols to format into a table. I get the data in a table, but the data from lookups 2 and 3 do not line up with the correct primary user. If I have 15 primary users and 10 with a count, then the 10 counts just start at the top and go down 10. I need the 10 counts to line up with the 10 primary users they are associated with.

Re: How do I create a table from 3 different lookup table and get the data to line up?

niketn — Tue, 29 Sep 2020 12:59:53 GMT

You would need to provide more details on the fields and if possible sample data... For example if the two fields in first lookup are user and count, second lookup is user and task_performed and third one is user and task_not_performed

<Your Base Search>
| stats values(count) as Count values(task_performed) values(task_not_performed) by user

Re: How do I create a table from 3 different lookup table and get the data to line up?

lowden — Tue, 21 Feb 2017 22:27:16 GMT

I am not allowed to provide anymore detail. However, I appreciate your answer and will give it a try. Thanks for you help.

Re: How do I create a table from 3 different lookup table and get the data to line up?

martin_mueller — Tue, 21 Feb 2017 23:17:25 GMT

You don't need to provide the actual data, it'd be enough to post a small dummy data set for each table.

Re: How do I create a table from 3 different lookup table and get the data to line up?

gvmorley — Wed, 22 Feb 2017 04:37:25 GMT

Hi,

As both @martin_mueller and @niketnilay mentioned, it would be great if you could post your searches and a small set of dummy data.

But without that, here are a couple of ways which may work...

I've just put some dummy data into 3 csv files to simulate your 3 lookups / searches. These are:

users-count.csv
user,total_count
bob,3
fred,3
dave,3

users-task.csv
user,task
bob,swimming
fred,running
dave,kayaking

users-not-task.csv
user,not_task
bob,reading
fred,writing
dave,splunking

These are in my $SPLUNK_HOME/etc/system/lookups folder, on a test Splunk instance on my laptop (this is for testing; not production). I can get this info into Splunk using the search:

| from inputlookup:"users-count.csv"
| append [| from inputlookup:"users-task.csv"]
| append [| from inputlookup:"users-not-task.csv"]
| table user total_count task not_task

Which looks like this:

Which sounds a bit like the situation you described where fields aren't lining up.

So a couple of options. This should work:

| from inputlookup:"users-count.csv"
| appendcols [| from inputlookup:"users-task.csv"]
| appendcols [| from inputlookup:"users-not-task.csv"]
| table user total_count task not_task

Or you could use a join and do:

| from inputlookup:"users-count.csv"
| join user [| from inputlookup:"users-task.csv"]
| join user [| from inputlookup:"users-not-task.csv"]
| table user total_count task not_task

The results from both of these searches output the same in this test:

This is just an example of what 'might' work for you. All a bit theoretical without a bit more detail around exactly what you're trying to achieve.

But maybe it will spark an idea or give you something else to try.

Re: How do I create a table from 3 different lookup table and get the data to line up?

lowden — Wed, 22 Feb 2017 23:22:03 GMT

2 of my lookups are not working. I have asked the Admin person to fix them (Admin created them). I will try your suggestions as soon as I can. As a side note, I am very new to Splunk. Thanks for your help.

Re: How do I create a table from 3 different lookup table and get the data to line up?

lowden — Mon, 13 Mar 2017 23:57:47 GMT

I want to update on this post. I got it to work by using appendcols. My main problem was each look up did not have the exact same user names. Once I deleted the user names that were not in all three lookups the numbers lined up. I then used table to arrange the 3 columns in a way to get a good bubble chart. Thanks for the help.

Re: How do I create a table from 3 different lookup table and get the data to line up?

gvmorley — Tue, 14 Mar 2017 04:03:52 GMT

Great to hear that you got it working!

If you're happy that the question is resolved, just accept this as the answer, then it gets marked a 'done' when others are looking.

Happy Splunking!