How to create a table of all users matching one lookup table from two sources, each with non-matching user ID field names?

[reswob4]

I've asked a couple of questions about lookups before and have received great answers. While I think I can use my previous questions to help finalize this answer, there is one little part I can't quite figure out.

Basically, I want to create a table of all users matching a lookup table from two different sources of events.

So I have a lookup table with two columns: user, fullname. The problem is, one source has the user's id in a field called user and the other source has the user's id in a field called username.

Doing a search against the first source is easy:

"SourceName=Microsoft-Windows-Security-Auditing" user!=NULL Logon_Type=3 [ inputlookup Service_Accounts | fields user]  | dedup user,ComputerName | table user,ComputerName 

But how do I combine that search with a second search against the second source when the field doesn't match the lookup table column name?

Searching against the second source is the problem as neither of the two following searches work:

"SourceName=blahblah"  | rename UserName AS user [ inputlookup Service_Accounts | fields user ] | dedup user,ComputerName | table user,ComputerName

or

"SourceName=blahblah"  | rename UserName AS user | inputlookup Service_Accounts | fields user | dedup user,ComputerName | table user,ComputerName

The problem seems to be that the column name 'user' in the lookup table doesn't match the field name 'username' in the second source.

I could recreate the lookup table and duplicate the 'user' column into a 'username' column so my lookup table would then have three columns: user, username, fullname where the first two columns have the exact same data.

But I figured I'd first ask if there were any suggestions on how to create a single search against the currently configured lookup table?

Thanks

[woodcock]

You have a simple typo/mismatch; try this:

"SourceName=blahblah" | rename UserName AS username | inputlookup Service_Accounts | fields username | dedup user,ComputerName | table username,ComputerName

[reswob4]

With this I get an error:

Error in 'inputlookup' command: This command must be the first command of a search. 

I tried putting square brackets"

"SourceName=blahblah" | rename UserName AS username [ inputlookup Service_Accounts | fields username ] | dedup user,ComputerName | table username,ComputerName

but I got this error:

Error in 'rename' command: Usage: rename [old_name AS/TO/-> new_name]+ 

So then I looked at your answer and one of the answers from my previous question and came up with this:

"SourceName=blahblah" | rename UserName AS user | lookup Service_Accounts user output fullname | dedup fullname,host | lookup Service_Accounts fullname output user | rename host AS ComputerName | table user, ComputerName

So I used your rename suggestion, passed it to a straight lookup, got back the fullname, dedupped the results, then passed those results BACK to the lookup, but this time getting the results back in a field named user (which is what I wanted) and now I can merge these results with the results of the first search because they all have the same column names.

Thanks.

[woodcock]

Please click "Accept" for the answer.