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.
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
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
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:?
Just add another lookup command: | lookup lookupname.csv username AS manager OUTPUT email
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
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.
Thanks. I know what I want just find it hard to get there sometimes!
You're welcome 🙂 if it works, please mark the answer as accepted, so others can also easily find the right solution for this question 🙂
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?
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 🙂
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.
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"