The following Splunk Query will return a list of operating systems used within IIS logs traffic. It essentially uses a lookup to check the user agent against a known list. *NOTE* The app TA-browscap_express – HTTP User Agent lookup with browscap must be installed sourcetype=iis |dedup JSESSIONID | eval http_user_agent=urldecode(cs_User_Agent) | lookup browscap_lookup_express http_user_agent OUTPUT ua_platform_description […]
User Agent – Browser Details & Information for IIS
This Splunk query will reference a lookup table to return user agent (browser information) within IIS logs. Specifically the output will list browser name and version, crawler, and mobile. It will give a count based on visits not hits (hence the dedup). Depending on the length of time this query can take a very long […]
Total Unique Browsers detected in IIS logs
The following Splunk search query will show a count of unique browsers (calculation to include version) that hit a given website within IIS logs: sourcetype=iis | stats dc(cs_User_Agent)
Weekday Web Traffic Summary in IIS
The following Splunk query will show a summary of all weekday activity for a given website in IIS. sourcetype=”iis” (NOT date_wday=saturday) (NOT date_wday=sunday)| stats count(JSESSIONID) as Value| eval Metric=”Total Hits on Weekdays”| append[ search sourcetype=”iis” (NOT date_wday=saturday) (NOT date_wday=sunday)| stats dc(JSESSIONID) as Value| eval Metric=”Total Visits on Weekdays”]| append[ search sourcetype=”iis” (NOT date_wday=saturday) (NOT date_wday=sunday) […]
Weekend Web Traffic Summary in IIS
The following Splunk query will return a summary of weekend activity for a given IIS hosted website. sourcetype=”iis” (date_wday=saturday OR date_wday=sunday) | stats count(JSESSIONID) as Value | eval Metric=”Total Hits on Weekends” | append [ search sourcetype=”iis” (date_wday=saturday OR date_wday=sunday) | stats dc(JSESSIONID) as Value | eval Metric=”Total Visits on Weekends”] | append [ search […]
Visits by Hour of the Day in IIS
The following Splunk query will list the total visits for each hour in a given time range. sourcetype=”iis” | top limit=24 date_hour | sort +date_hour | rename count as Visits | rename date_hour as “Hour of the Day”
Total Hits on Most Active Day in IIS
The following Splunk query will return the total number of hits on the most active day in a given time range within an IIS environment: sourcetype=”iis” | top limit=1 date_mday | rename count as Value| fieldformat Value=tostring(Value,”commas”) | eval Metric=”Number of hits on Most active date” | fields – date_mday, count, percent | fields Metric, […]
Total Hits on Least Active Day in IIS
The following Splunk Query will return the total number of hits to a web site on the least active day of a given time range: sourcetype=”iis” | rare limit=1 date_mday | rename count as Value | fieldformat Value=tostring(Value,”commas”)| eval Metric=”Number of hits on least active date” | fields – date_mday, count, percent | fields Metric, […]
Most Active Day and Least Active Day for IIS Web Traffic
The following Splunk query will return the most active and the least active days for web traffic in an IIS environment: sourcetype=”iis” | bucket span=1d _time | top limit=1 _time | eval Date=strftime(_time, “%m/%d/%Y”) | eval Metric=”Most Active Date” | append [ search sourcetype=”iis” | bucket span=1d _time | rare limit=1 _time | eval Date=strftime(_time, […]
Visits by Days of the Week in IIS
The following Splunk query will show the number of web visits for each weekday: sourcetype=”iis” | eval uppercase=upper(substr(date_wday,1,1)).substr(date_wday,2)|dedup JSESSIONID| top limit=7 uppercase | eval sort_field=case(uppercase==”Sunday”,1, uppercase==”Monday”,2, uppercase==”Tuesday”,3, uppercase==”Wednesday”,4, uppercase==”Thursday”,5, uppercase==”Friday”,6, uppercase==”Saturday”,7) | sort + sort_field | fields – sort_field |rename count as Visits | rename uppercase as “Day of the Week”
Top 5 Visiting Countries in IIS
The following Splunk query will list the top 5 visiting countries using the built in “iplocation” feature in Splunk: sourcetype=”iis” | iplocation c_ip |top limit=5 Country | eval percent = round(percent,2) . ” %” | rename count as Views | rename percent as Percent
Rename _time field in a TimeChart
When running a timechart splunk search query you may wish to rename the field _time. In order to do this you must first save the search to a dashboard or report. Once saved edit the source and add the following in the panel: <option name=”charting.axisTitleX.text”>Date</option> This can be added right before the closing “</chart>” code.
Splunk License Usage Over the Last 30 Days
The following Splunk Search will show license usage over the past 30 days: index=_internal source=*license_usage.log type=”RolloverSummary” earliest=-30d@d | eval _time=_time – 43200 | bin _time span=1d | stats latest(b) AS b by slave, pool, _time | timechart span=1d sum(b) AS “volume” fixedrange=false | join type=outer _time [ search index=_internal source=*license_usage.log type=”RolloverSummary” earliest=-30d@d | eval _time=_time […]
Splunk License Gauge
This Splunk search query will show current license usage | rest splunk_server=local /services/licenser/pools | rename title AS Pool | search [rest splunk_server=local /services/licenser/groups | search is_active=1 | eval stack_id=stack_ids | fields stack_id] | join type=outer stack_id [rest splunk_server=local /services/licenser/stacks | eval stack_id=title | eval stack_quota=quota | fields stack_id stack_quota] | stats sum(used_bytes) as used max(stack_quota) […]
Repeated Unsuccessful Logon Attempts in Linux
The following Splunk search query will return results for failed login attempts in a Linux environment for a specified time range. The regular expressions are defined within the search string, however if you already extracted the necessary fields you can ignore the regex section. sourcetype=linux_secure | eval Date=strftime(_time, “%Y/%m/%d”) | rex “.*:\d{2}\s(?<hostname>\S+)” | rex […]
Clean or Delete Data in a given Source
The following Splunk query will wipe or clear a data source. Ensure you specify an index else it will wipe the given source for all indexes. source=path\to\sourcename | delete
Linux Cron Job Information
This splunk query example uses regex (regular expressions) to extract information on Linux cron jobs. *Note* this query has not been extensively tested sourcetype=”cron” | eval Date=strftime(_time, “%Y/%m/%d”) | rex “.*:\d{2}\s(?<hostname>\S+)” | rex “]:\sfinished(?<Info>.*)” | stats count by Date, hostname, Info
Failed Windows Remote Desktop Connection Attempt
The following splunk query example will return results on any Windows remote desktop connection attempts. This could be a result of a bad password, invalid user name, or any number of other reasons. Ensure the Splunk App for Windows is installed grab it here: https://apps.splunk.com/app/742/ Windows Server 2008 and Newer: source=WinEventLog:Security sourcetype=WinEventLog:security Logon_Type=10 EventCode=4625 | […]
Accounts Deleted within 24 Hours of Creation
This splunk query unmodified will return results on any account regardless of duration, however it uses an “eval case” argument to determine what is “critical” (such as accounts deleted within a day of being created) or what is simply note worthy (normal behavior). Ensure the Splunk App for Windows is installed grab it here: https://apps.splunk.com/app/742/ […]
Escalation of Privileges via SU in Linux
The following splunk query example will return a list of users who escalated privileges on any host in a given time range. The query will count by day, if you need to count in a shorter or longer time range modify the “Date=strftime” value below. *NOTE* if the host field is being autoextracted (for instance […]
