Splunk Search

In a tstats query, how to discover the username's line manager which is listed in the lookup?

griggsy
New Member

Hello,

I have a tstats query running which returns the data. I then want to look up the username returned as part of that search in a lookup to discover the username's line manager which is listed in the lookup. I just simply can't get it working.

Can anyone suggest anything?

Ta.

0 Karma
1 Solution

FrankVl
Ultra Champion

Sounds like a job for the lookup command (assuming the lookup contains fields username and manager, adjust according to your situation):

| tstats summariesonly=t count FROM Datamodel=x WHER earliest=@d latest=now x.EventName="Login" BY X.EventName, X.Username
| lookup lookupname.csv username AS X.Username OUTPUT manager

View solution in original post

0 Karma

FrankVl
Ultra Champion

Sounds like a job for the lookup command (assuming the lookup contains fields username and manager, adjust according to your situation):

| tstats summariesonly=t count FROM Datamodel=x WHER earliest=@d latest=now x.EventName="Login" BY X.EventName, X.Username
| lookup lookupname.csv username AS X.Username OUTPUT manager
0 Karma

griggsy
New Member

Thanks Frank.

Question once I have returned the Line Manager it returns a userID I then need to look them up against the same lookup to there email. Would I then subsearch that:?

0 Karma

FrankVl
Ultra Champion

Just add another lookup command: | lookup lookupname.csv username AS manager OUTPUT email

0 Karma

griggsy
New Member

So I can run a lookup and then use the results of that lookup in another lookup so for example:

| tstats summariesonly=t count FROM Datamodel=x WHER earliest=@d latest=now x.EventName="Login" BY X.EventName, X.Username
| lookup lookupname.csv username AS X.Username OUTPUT managerID | lookup lookupname EmpID as ManagerID OUTPUT email

0 Karma

FrankVl
Ultra Champion

Exactly 🙂

The tstats command just returns a table of data. The first lookup command adds the managerID column to that table based on matching usernames. Then you again have a table of data, so you just apply another lookup command to fetch the manager's email address based on matching emplID.

0 Karma

griggsy
New Member

Thanks. I know what I want just find it hard to get there sometimes!

0 Karma

FrankVl
Ultra Champion

You're welcome 🙂 if it works, please mark the answer as accepted, so others can also easily find the right solution for this question 🙂

0 Karma

griggsy
New Member

Hello,

Thanks for all your help. The user ID in the lookup contains the username as email address with different suffixes. I have added a * to the end of the possible username in the initial query eg. joebloggs* however the lookup isn't returning anything.

if I manually search | from inputlookup: lookupname.csv | search username="joebloggs*" it returns records. Am I missing something?

0 Karma

FrankVl
Ultra Champion

Think you need to modify your lookup table content to add a username column without the @... part from the email address then? If your event data contained the email address and the lookup contained the short username without suffix, you could make the lookup do wildcard matching, but the other way around doesn't work as far as I know.

If you need further help with that, you might want to post a new question on that, not to go too much off-topic here 🙂

0 Karma

FrankVl
Ultra Champion

Can you share the query you have so far (that isn't working) and explain what exactly isn't working? Perhaps some info on the data you're querying might help as well.

0 Karma

griggsy
New Member

Hi Frank,

So with redacted information:

| tstats summariesonly=t count FROM Datamodel=x WHER earliest=@d latest=now x.EventName="Login" BY X.EventName, X.Username

I have shortened the above there is more fields however I would like to pass the Username in to a lookup to find a result in a lookup. so for example | from inputlookup:"LookupName.csv" | search EmailID = $x.username"

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