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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!