topic Re: How to filter IIS logs with regular expression? in Splunk Search

How to filter IIS logs with regular expression?

sachdeva_2007 — Fri, 26 Feb 2021 18:12:47 GMT

i do like to filter out Status code and Time Taken and other as fields

#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken

2021-02-20 06:56:41 W3SVC1 XXX 100.x.x.x HEAD / - 9004 - 10.x.x.x HTTP/1.1 - - - www.google.com:80 403 14 0 181 70 46

here status code is 403 and time-taken 46

Thanks in Advance

Re: How to filter IIS logs with regular expression?

richgalloway — Fri, 26 Feb 2021 20:32:39 GMT

When you say "filter out" do you mean you wish to use a regular expression to extract the sc-status and time-taken fields?

If so, try this:

... | rex "(?:\S+\s){16}(?<status>\d+)\s(?:\S+\s){4}(?<time_taken>\d+)"

Re: How to filter IIS logs with regular expression?

sachdeva_2007 — Mon, 01 Mar 2021 10:11:22 GMT

Thanks.

Got the header of fields of the logs as follow but what would the query to pull status code and order by Client wise.

also in the interesting fields status and time-taken not showing.

is this the site of "https://www.debuggex.com/cheatsheet/regex/pcre"

1 3/1/21
3:00:01.000 AM

#Fields: date time s-sitename s-computername s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs-version cs(User-Agent) cs(Cookie) cs(Referer) cs-host sc-status sc-substatus sc-win32-status sc-bytes cs-bytes time-taken
host = XXXXX
source = E:\acbsapi\Logs\wlUat\IIS\Site_wlUat\W3SVC1\u_ex210301.log
sourcetype = XXX

2 3/1/21
3:00:01.000 AM

#Date: 2021-03-01 00:00:01
host = XXX
source = E:\acbsapi\Logs\wlUat\IIS\Site_wlUat\W3SVC1\u_ex210301.log
sourcetype =XXX

3 3/1/21
2:00:03.000 AM

#Fields: date time s-sitename s-computername s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs-version cs(User-Agent) cs(Cookie) cs(Referer) cs-host sc-status sc-substatus sc-win32-status sc-bytes cs-bytes time-taken
host = XXX
source = E:\acbsapi\Logs\wlUat\IIS\Site_wlUat\W3SVC1\u_ex210301.log
sourcetype = XXX

4 3/1/21
2:00:03.000 AM

#Date: 2021-03-01 00:00:03
host = XXX
source = E:\acbsapi\Logs\wlUat\IIS\Site_wlUat\W3SVC1\u_ex210301.log
sourcetype = XXX

5 2/28/21
10:49:06.000 PM

2021-02-28 20:49:06 W3SVC1 XXX 100.72.153.196 GET /Portfolio/02/Loan/920029607/LoanTransaction absolutePosition=0&startDate=2021-02-26T00:00:00 9004 sa_esb_acbs_qa 100.72.210.0 HTTP/1.1 Apache-HttpClient/4.0.1+(java+1.5) - - acbs-api-uat-fhb.fisglobal.com 200 0 0 453 2159 143
host = XXX
source = E:\acbsapi\Logs\wlUat\IIS\Site_wlUat\W3SVC1\u_ex210228.log
sourcetype = XXX

Re: How to filter IIS logs with regular expression?

sachdeva_2007 — Mon, 01 Mar 2021 13:50:17 GMT

Saw the interesting fields with Status and time_taken. i was looking for query Client wise Hits and Average response time(time_taken)

Thanks in Advance.

Re: How to filter IIS logs with regular expression?

richgalloway — Mon, 01 Mar 2021 15:49:20 GMT

What have you tried so far?

Re: How to filter IIS logs with regular expression?

sachdeva_2007 — Mon, 01 Mar 2021 16:41:55 GMT

use these fields extract

index=xx_api | rex "^(?P<Date>[^ ]+)(?:[^\-\n]*\-){5}\s+(?P<Host_Name>[^ ]+)\s+(?P<Status>\d+)\s+(\d+\s+)+(?P<Time_Taken>.+)" | search Status=200

how can pull the report with

-Total Hits,

-avgerage(time-taken)

order by Client Wise

Regards

Re: How to filter IIS logs with regular expression?

sachdeva_2007 — Wed, 03 Mar 2021 14:35:47 GMT

Used below command and got the result but when i run second command not getting any result can you please suggest on this

* index=xxx sc_status=201

*index=xxx sc_status=201 AND sc_status=200

any help highly appreciated.

Re: How to filter IIS logs with regular expression?

richgalloway — Wed, 03 Mar 2021 15:35:36 GMT

The second query fails because it is not possible for the sc_status field to be both 200 and 201 at the same time. Perhaps you want index=xxx (sc_status=201 OR sc_status=200)?

Re: How to filter IIS logs with regular expression?

richgalloway — Wed, 03 Mar 2021 15:40:11 GMT

Try this.

index=xx_api | rex "^(?P<Date>[^ ]+)(?:[^\-\n]*\-){5}\s+(?P<Host_Name>[^ ]+)\s+(?P<Status>\d+)\s+(\d+\s+)+(?P<Time_Taken>.+)" | search Status=200 | stats count as "Total Hits", avg(Time_Taken) as Avg_Time_Taken by Host_Name