Splunk Search

How to filter IIS logs with regular expression?

sachdeva_2007
Explorer

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

 

Labels (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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+)"
---
If this reply helps you, an upvote would be appreciated.

sachdeva_2007
Explorer

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.

 

Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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)?

---
If this reply helps you, an upvote would be appreciated.
0 Karma

sachdeva_2007
Explorer

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

0 Karma

sachdeva_2007
Explorer

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.

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

What have you tried so far?

---
If this reply helps you, an upvote would be appreciated.
0 Karma

sachdeva_2007
Explorer

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
 
0 Karma

richgalloway
SplunkTrust
SplunkTrust

 

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

 

---
If this reply helps you, an upvote would be appreciated.
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!