Inputlookup not functioning as expecting

[mraymer1]

I have a query for detecting logins to "sensitive" accounts from outside of certain countries. Rather than listing every single account, I want to use a lookup listing the UserIds of sensitive accounts.

Currently my query looks like this and functions fine:

 sourcetype=office365 ResultStatus=Succeeded Operation=UserLoggedIn (UserId="john.doe@whateverdotcom" OR 
 UserId="jane.doe@whateverdotcom" OR UserId="man.face@ whateverdotcom" OR UserId="onemore.example@ whateverdotcom")
    | iplocation ClientIP
    | search Country!="United States"

Only add like 20 more account names. I've made a csv titled sensitive_accounts.csv that's laid out as follows:

UserId,Name
john.doe@whateverdotcom,John Doe
jane.doe@whateverdotcom,Jane Doe
man.face@whateverdotcom,Man Face
onemore.example@whateverdotcom,Onemore Example

I've put this lookup into the query like this:

    sourcetype=office365 ResultStatus=Succeeded Operation=UserLoggedIn
    [inputlookup sensitive_accounts.csv]
        | iplocation ClientIP
        | search Country!="United States"

It runs for a few seconds and then returns no results -- I've verified that with the original query it does pull back results. Anyone have any ideas on what I'm doing wrong here? Bonus points if it's something really obvious -- I have a feeling it is.

**Edited to add appropriate iplocation argument (ClientIP), forgot to include that when I was sanitizing these queries -- thank you to the user who pointed that out!

[niketn]

@mraymer1, if your intent is to use inputlookup to search UserId from lookup file into the raw data then you should try the following (inputlookup should return only UserId as Name field might not be present in your raw event:

 sourcetype="office365" ResultStatus="Succeeded" Operation="UserLoggedIn"
 [| inputlookup sensitive_accounts.csv | table UserId]
  | stats count by ClientIP
  | iplocation ClientIP
  | search Country!="United States"

Please try out and confirm!

[deepashri_123 · Accepted solution]

Hey@mraymer1,

Inputlookup is a generating command and should be the first command used in search. In your case the subsearch | is missing.

Try running your query like this:
sourcetype=office365 ResultStatus=Succeeded Operation=UserLoggedIn
[| inputlookup sensitive_accounts.csv]
| iplocation ClientIP
| search Country!="United States"

OR

sourcetype=office365 ResultStatus=Succeeded Operation=UserLoggedIn
| lookup sensitive_accounts.csv UserId AS UserId OUTPUT Name | search Name=*
| iplocation ClientIP
| search Country!="United States"

Let me know if this helps!!

[mraymer1]

That second query structure worked! The only downside is the job runs exponentially slower than the original messy one. (434 seconds/command search/134 invocations vs .94 seconds/command search/15 invocations)

So now I'm troubleshooting why that difference is so extreme, since it needs this lookup functionality to ensure scalability for queries. Thank you for getting me in the right direction!

[thomast_splunk]

Try removing the | search Name=* so as to have: sourcetype=office365 ResultStatus=Succeeded Operation=UserLoggedIn
| lookup sensitive_accounts.csv UserId AS UserId OUTPUT Name
| iplocation ClientIP
| search Country!="United States"

[thomast_splunk]

It would appear that you should specify a field for the iplocation command. E.g. | iplocation src_ip

     sourcetype=office365 ResultStatus=Succeeded Operation=UserLoggedIn
     [inputlookup sensitive_accounts.csv]
         | iplocation src_ip
         | search Country!="United States"

[thomast_splunk]

Rather : sourcetype=office365 ResultStatus=Succeeded Operation=UserLoggedIn (UserId="john.doe@whateverdotcom" OR
UserId="jane.doe@whateverdotcom" OR UserId="man.face@ whateverdotcom" OR UserId="onemore.example@ whateverdotcom")
| iplocation src_ip
| search Country!="United States"