Splunk Search

Inputlookup not functioning as expecting

mraymer1
Engager

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!

0 Karma
1 Solution

deepashri_123
Motivator

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

View solution in original post

0 Karma

niketn
Legend

@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!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

deepashri_123
Motivator

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

0 Karma

mraymer1
Engager

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!

0 Karma

thomast_splunk
Splunk Employee
Splunk Employee

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"

0 Karma

thomast_splunk
Splunk Employee
Splunk Employee

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"
0 Karma

thomast_splunk
Splunk Employee
Splunk Employee

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"

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...