Splunk Search

CSV Lookup for search query

keyu921
Explorer

I prepared csv to inputlookup to compare the Splunk logs.

adhoc.csv

//

Account,

test01,etc....

test02,etc....

//

my Query

index=msad sourcetype=msad [| inputlookup adhoc.csv | fields Account] 
Searching Period: Last 24 hours

Cross check adhoc.csv match the searching logs.

For those account did not perform any authentation which logs stored at index=msad, during search period, then show zero values.

 

0 Karma
1 Solution

gcusello
Legend

Hi @keyu921,

please try this:

index=msad sourcetype=msad 
| eval Account=lower(Account)
| stats count BY Account
| append [| inputlookup adhoc.csv | eval Account=lower(Account), count=0 | fields Account count] 
| stats sum(count) AS total BY Account
| where total=0

In this way,

  • total>o means that there are logs,
  • total=0 means that there aren't logs.

As you requested, I added at the end a filter to display only the ones without logs, but you could also create a dashboard displaying all the Accounts with their status.

Ciao.

Giuseppe

View solution in original post

0 Karma

gcusello
Legend

Hi @keyu921,

please try this:

index=msad sourcetype=msad 
| eval Account=lower(Account)
| stats count BY Account
| append [| inputlookup adhoc.csv | eval Account=lower(Account), count=0 | fields Account count] 
| stats sum(count) AS total BY Account
| where total=0

In this way,

  • total>o means that there are logs,
  • total=0 means that there aren't logs.

As you requested, I added at the end a filter to display only the ones without logs, but you could also create a dashboard displaying all the Accounts with their status.

Ciao.

Giuseppe

0 Karma

gcusello
Legend

hi @keyu921,

good for you.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

bowesmana
SplunkTrust
SplunkTrust

This will show you all the Account names from the CSV that did NOT have any entries in the msad log in the time period given. It first searches and counts all the data for the monitored accounts, then adds back all the accounts being monitored and just then looks for ones with no count value.

index=msad sourcetype=msad [| inputlookup adhoc.csv | fields Account] 
| stats count by Account
| append [
  | inputlookup adhoc.csv | fields Account
]
| stats values(count) as count by Account 
| where isnull(count)

Hope this helps

  

0 Karma

yeahnah
Communicator

Hi @keyu921 

It's not overly clear what you are asking for so I not going to try, however if you want to have an OR statement generated for you base search, from the adhoc.csv file, then you need to use the format command, as shown

 

... your search ... [| inputlookup adhoc.csv | fields Account | format] | ...

 

Substituted result of the subsearch would be

 

 ... your search ... ( ( Account="test1" ) OR ( Account="test2" ) ) | ...

 

 
Having said that, more often than not, the use of the lookup command is far more efficient ...

 

... your search ...
| lookup adhoc.csv Account AS Account OUTPUTNEW <...some new field in adhoc.csv...>
| where isnotnull(<some new field>) 

 

Check the Splunk docs for more details, if interested.

Hope this helps

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...