topic Re: Subset Search using in original search in Splunk Search

Subset Search using in original search

staparia — Wed, 17 Jul 2019 08:36:18 GMT

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?

Re: Subset Search using in original search

renjith_nair — Wed, 17 Jul 2019 10:04:24 GMT

@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

Re: Subset Search using in original search

staparia — Thu, 18 Jul 2019 04:55:19 GMT

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

Re: Subset Search using in original search

staparia — Fri, 19 Jul 2019 12:20:47 GMT

@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

Re: Subset Search using in original search

jawaharas — Mon, 22 Jul 2019 06:24:19 GMT

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

Re: Subset Search using in original search

staparia — Wed, 30 Sep 2020 01:26:31 GMT

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

Re: Subset Search using in original search

jawaharas — Tue, 23 Jul 2019 23:56:51 GMT

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

Re: Subset Search using in original search

staparia — Wed, 24 Jul 2019 03:27:25 GMT

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

Re: Subset Search using in original search

jawaharas — Wed, 30 Sep 2020 01:26:40 GMT

To debug your SPL with subquery.

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

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