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?
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
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
@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...
I am glad it worked. Before we dig in further, can you accept the answer so that it will be helpful to others.
Done. Now request you to please help on tstats and datamodel query
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
@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
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
@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