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

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

griggsy — Mon, 18 Jun 2018 11:09:23 GMT

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.

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

FrankVl — Mon, 18 Jun 2018 11:27:59 GMT

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.

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

griggsy — Mon, 18 Jun 2018 11:42:34 GMT

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"

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

FrankVl — Mon, 18 Jun 2018 11:50:46 GMT

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

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

griggsy — Mon, 18 Jun 2018 11:55:48 GMT

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:?

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

FrankVl — Mon, 18 Jun 2018 11:58:02 GMT

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

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

griggsy — Mon, 18 Jun 2018 12:26:53 GMT

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

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

FrankVl — Mon, 18 Jun 2018 12:31:34 GMT

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.

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

griggsy — Mon, 18 Jun 2018 12:45:16 GMT

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

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

FrankVl — Mon, 18 Jun 2018 12:51:06 GMT

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

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

griggsy — Tue, 19 Jun 2018 11:33:24 GMT

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?

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

FrankVl — Tue, 19 Jun 2018 12:33:12 GMT

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 🙂