Splunk Search

Subset Search using in original search

Engager

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

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

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

Engager

@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

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

Engager

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

0 Karma

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

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
0 Karma

Engager

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

Engager

@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