need help Top malware/suspicious site

[Steave4app]

Hi People,

I am using Bluecoat proxy at this time and I am trying to get the report based on Malicious/Suspicious. I am running below query.

sourcetype=bluecoat* categories("Malicious" OR "Phishing" OR "Suspicious") | fields add - status, - action, - host | stats count by host | sort – host

Raw log:

Feb 14 06:31:42 Feb 14 14:31:41 ProxySG: 3B0002 2017-02-14 14:31:41 1 src=x.x.x.x status=403 action=TCP_DENIED 803 379 method=GET protocol=http host=adgebra.co.in port=80 path=/Spike/spike.js http://tamil.oneindia.com/news/tamilnadu/da-case-who-is-a4-ilavarasi-274085.html useragent=Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36 categories=Web Ads/Analytics;Suspicious 74.117.128.45(97306393) UNKNOWN_EVENT pe_policy_action_log_message.cpp 44

How would I add URL info, action and status info into statistic result as those are not showing into default filed?

Kind Regards,
Steave

[DalJeanis]

You need to verify what fields have already been extracted. So, with your _raw event, look at the interesting fields and see what field (if any) the http://...html value has been loaded into.

If it has not been extracted into anything, then you will probably want to use a regex to load the URL data into a field that you can use the list aggregate command on.

Here's one link to a thread that deals with that. https://answers.splunk.com/answers/93003/regex-for-url-parsing.html

[gcusello]

Hi Steave4app,
to insert other fields in a stats command you can:

• insert it after "by" clause using that field as key in stats,
• before count, inserting values(URL) AS URL values(info) AS info values(action) AS action. The problem is that, if you have many values, your report could be unreadable.

In addition remember that this App uses Summary indexes, so you have to insert these fields in GROUPBY clause in tstats command.

Bye.
Giuseppe

[Steave4app]

Hi Cusello,

Happy to see you.

I have done that but it is not working. Interesting this is, they things are not describing as field.

status=403 action=TCP_DENIED 803 379 method=GET protocol=http host=adgebra.co.in port=80 path=/Spike/spike.js http://tamil.oneindia.com/news/tamilnadu/da-case-who-is-a4-ilavarasi-274085.html useragent=Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36 categories=Web

So if they are not field, how would it work into stats count by query?

Kind Regards,
Steave

[gcusello]

strange: in the default bcoat_proxysg extraction there are "action" and "http_referrer" (URL), I don't know what is "info".
Are you using the default App's sourcetype?
I used it, but rebuilding all dashboards because the old ones were created in extended XML (deprecated by Splunk).
Bye.
Giuseppe