Splunk Search

Subset Search using in original search

staparia
Explorer

Hi Guys,

Problem Statement : i would want to search the url events in index=proxy having category as "Malicious Sources/Malnets" for last 30 days. Also using the same url from the above result, i would want to search in index=proxy having category="none"

This is the query i thought of :
index=proxy category="none"
[| search index="proxy"
category="Malicious Sources/Malnets"
| stats values(cs_host) as dest] | stats count by dest

but it doesnt execute. Could you please help?

0 Karma
1 Solution

jawaharas
Motivator

Try this. I assume 'cs_host' field exists in all the events of 'proxy' index.

index=proxy category="none" 
 [| search index="proxy" 
     category="Malicious Sources/Malnets" 
 | table cs_host] 
| stats count by cs_host

View solution in original post

0 Karma

jawaharas
Motivator

Try this. I assume 'cs_host' field exists in all the events of 'proxy' index.

index=proxy category="none" 
 [| search index="proxy" 
     category="Malicious Sources/Malnets" 
 | table cs_host] 
| stats count by cs_host
0 Karma

staparia
Explorer

@jawaharas Hey .. thanks this works. Hoever to further optimize this using tstats and datamodel..

I wrote the following query .. and it didnt work :

| tstats max(_time) AS _time summariesonly=t allow_old_summaries=t count from datamodel=Web.Web WHERE web.category="none"
| drop_dm_object_name("Web")
| search [| tstats max(_time) AS _time summariesonly=t allow_old_summaries=t count from datamodel=Web.Web WHERE web.category="Malicious Sources/Malnets" GROUPBY Web.dest
| drop_dm_object_name("Web") ]
| table dest

Could you please let me know .. where am i wrong in this one...

0 Karma

jawaharas
Motivator

I am glad it worked. Before we dig in further, can you accept the answer so that it will be helpful to others.

0 Karma

staparia
Explorer

Done. Now request you to please help on tstats and datamodel query

0 Karma

jawaharas
Motivator

To debug your SPL with subquery.

  1. Run your subquery using 'format' command
    | tstats max(_time) AS _time summariesonly=t allow_old_summaries=t count from datamodel=Web.Web WHERE web.category="Malicious Sources/Malnets" GROUPBY Web.dest
    | drop_dm_object_name("Web")| format

  2. This will help you to find the output format of subquery. Substitute the same in your actual SPL

    tstats max(_time) AS _time summariesonly=t allow_old_summaries=t count from datamodel=Web.Web WHERE web.category="none"
    | drop_dm_object_name("Web")
    | search [ {OUTPUT OF SUBQUERY}]
    | table dest

Ideally this will error out, as the 'table' keyword is missing.

  1. If you add 'table' keyword to subquery, it will work.

    | tstats max(_time) AS _time summariesonly=t allow_old_summaries=t count from datamodel=Web.Web WHERE web.category="none"
    | drop_dm_object_name("Web")
    | search [| tstats max(_time) AS _time summariesonly=t allow_old_summaries=t count from datamodel=Web.Web WHERE web.category="Malicious Sources/Malnets" GROUPBY Web.dest
    | drop_dm_object_name("Web") | table Web.dest]
    | table dest

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@staparia ,

If you are looking for URLs which are part of both categories, try this:
Assuming dest is the field name for urls

index=proxy (category="Malicious Sources/Malnets" OR category="none")
|stats dc(category) as count by dest |where count > 1
Happy Splunking!
0 Karma

staparia
Explorer

thanks for responding. This didnt yield the result as expected.

I would want same URL/dest having categories="Malicious Sources/Malnets" and "none". Please note "Malicious Sources/Malnets" category will be a different log and "none" category will be a different event/log

0 Karma

staparia
Explorer

@richgalloway ,
@renjith.nair

thanks for responding. This didnt yield the result as expected.

I would want same URL/dest having categories="Malicious Sources/Malnets" and "none". Please note events with "Malicious Sources/Malnets" category will be a different log and events with "none" category will be a different event/log

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...