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!

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