Splunk Search

How to combine two searches

alexspunkshell
Contributor

Hi All,

I want to combine "LogonIP" in two different searches and get 1 result.

 

Query 1 

index=testindex | table LogonIP Event_Date Event_Title Event_Severity UPN  MFAStatus

 

Query 2

index=o365 ClientIPAddress=* |stats count by ClientIPAddress |dedup ClientIPAddress |rename ClientIPAddress as "LogonIP"

 

If the Query 2 "LogonIP" count is greater than 20 (LogonIP>20) then, I want to join the result with Query 1 and ignore the result.

But if the search Query 2 LogonIP<20 then, I want to join the result with Query 1 and get the result.

@ITWhisperer @scelikok @soutamo @saravanan90 @thambisetty @gcusello @bowesmana   @to4kawa @woodcock  Please help here.

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @alexspunkshell,

in addition to the previous answer, you have to add the distinct count of users (UPN) so you can filter results on this value, please, try something like this:

index=testindex OR (index=o365 ClientIPAddress=*)
| eval LogonIP=coalesce(LogonIP,ClientIPAddress)
| stats values(Event_Date) AS Event_Date values(Event_Title) AS Event_Title values(Event_Severity) AS Event_Severity dc(UPN) AS dc_UPN values(UPN) AS UPN values(MFAStatus) AS MFAStatus count(eval(index=o365)) AS Count by LogonIP
| where dc_UPN>0

Ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @alexspunkshell,

you have to correlate events using the common fields, something like this:

index=testindex OR (index=o365 ClientIPAddress=*)
| eval LogonIP=coalesce(LogonIP,ClientIPAddress)
| stats values(Event_Date) AS Event_Date values(Event_Title) AS Event_Title values(Event_Severity) AS Event_Severity values(UPN) AS UPN values(MFAStatus) AS MFAStatus count(eval(index=o365)) AS Count by LogonIP

Ciao.

Giuseppe

alexspunkshell
Contributor

@gcusello Thanks for your reply.

I am getting results like the below screenshot.

Now I want the Query-1 "LogonIP" results (index=testindex ), need to check with Query2 (index=o365) "LogonIP".

And if the LogonIP are already used by 20+ users means, I want to filter in the results.

alexspunkshell_0-1623836857781.png

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @alexspunkshell,

in addition to the previous answer, you have to add the distinct count of users (UPN) so you can filter results on this value, please, try something like this:

index=testindex OR (index=o365 ClientIPAddress=*)
| eval LogonIP=coalesce(LogonIP,ClientIPAddress)
| stats values(Event_Date) AS Event_Date values(Event_Title) AS Event_Title values(Event_Severity) AS Event_Severity dc(UPN) AS dc_UPN values(UPN) AS UPN values(MFAStatus) AS MFAStatus count(eval(index=o365)) AS Count by LogonIP
| where dc_UPN>0

Ciao.

Giuseppe

venkatasri
SplunkTrust
SplunkTrust

Hi @alexspunkshell 

Can you try if this works for you,

 

index=testindex 
    [ search index=o365 ClientIPAddress=* 
    | stats count by ClientIPAddress 
    | dedup ClientIPAddress 
    | rename ClientIPAddress as "LogonIP" 
    | where count < 20 
    | return 1000 LogonIP] 
| table LogonIP Event_Date Event_Title Event_Severity UPN MFAStatus

 

----

An upvote would be appreciated if it helps!

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...