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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...