Splunk Dev

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

lowden
New Member

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.

Tags (1)
0 Karma
1 Solution

gvmorley
Contributor

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:

alt text

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:

alt text

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.

View solution in original post

gvmorley
Contributor

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:

alt text

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:

alt text

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.

lowden
New Member

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.

0 Karma

lowden
New Member

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.

0 Karma

gvmorley
Contributor

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!

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

niketn
Legend

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

lowden
New Member

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...