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.
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.
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.
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.
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.
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!
You don't need to provide the actual data, it'd be enough to post a small dummy data set for each table.
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
I am not allowed to provide anymore detail. However, I appreciate your answer and will give it a try. Thanks for you help.