Splunk Report using outer and inner query for a SessionID & Date/Time

[runiyal]

I have a logfile like this -

2024-06-14 09:34:45,504 INFO  [com.mysite.core.repo.BaseWebScript] [http-nio-8080-exec-43] ****** NEW WEBSCRIPT REQUEST ******
Server Path: http://repo.mysite.com:80
Service Path: /repo/service/company/upload
Query String: center=pc&contentType=reqDocExt&location=\\myloc\CoreTmp\app\pc\in\gwpc6285603725604350160.tmp&name=Dittmar%20-%20NO%20Contents%20-%20%20company%20Application%20(Please%20Sign)%20-%20signed&contentCreator=ALEXANDER BLANCO&mimeType=application/pdf&accountNum=09631604&policyNum=12980920&jobIdentifier=34070053
2024-06-14 09:34:45,505 INFO  [com.mysite.core.repo.upload.FileUploadWebScript] [http-nio-8080-exec-43] Uploading file to pc from \\myloc\CoreTmp\app\pc\in\gwpc628560372560435

2024-06-13 09:22:49,101 INFO  [com.mysite.core.repo.BaseWebScript] [http-nio-8080-exec-43] ****** NEW WEBSCRIPT REQUEST ******
Server Path: http://repo.mysite.com:80
Service Path: /repo/service/company/upload
Query String: center=pc&contentType=reqDocExt&location=\\myloc\CoreTmp\app\pc\in\gwpc5799838158526007183.tmp&name=wagnac%20%20slide%20coverage%20b&description=20% rule&contentCreator=JOSEY FALCON&mimeType=application/pdf&accountNum=09693720&policyNum=13068616

2024-06-13 09:22:49,101 INFO  [com.mysite.core.repo.upload.FileUploadWebScript] [http-nio-8080-exec-43] The Upload Service /repo/service/company/upload failed in 0.000000 seconds, null

2024-06-13 09:22:49,103 ERROR [org.springframework.extensions.webscripts.AbstractRuntime] [http-nio-8080-exec-43] Exception from executeScript: 051333149 Failed to execute web script.
org.springframework.extensions.webscripts.WebScriptException: 051333149 Failed to execute web script.
	at com.mysite.core.repo.BaseWebScript.execute(BaseWebScript.java:105)
	at org.repo.repo.web.scripts.RepositoryContainer.lambda$transactionedExecute$2(RepositoryContainer.java:556)
	at org.repo.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:450)
	at org.repo.repo.web.scripts.RepositoryContainer.transactionedExecute(RepositoryContainer.java:539)
	at org.repo.repo.web.scripts.RepositoryContainer.transactionedExecuteAs(RepositoryContainer.java:663)
	at org.repo.repo.web.scripts.RepositoryContainer.transactionedExecuteAs(RepositoryContainer.java:699)
	... 23 more
Caused by: java.lang.IllegalArgumentException: URLDecoder: Illegal hex characters in escape (%) pattern - Error at index 0 in: " r"
	at java.base/java.net.URLDecoder.decode(URLDecoder.java:232)
	at java.base/java.net.URLDecoder.decode(URLDecoder.java:142)
	at com.mysite.core.repo.util.RepositoryUtils.decodeValue(RepositoryUtils.java:465)
	at com.mysite.core.repo.BaseWebScript.getParameterMap(BaseWebScript.java:138)
	at com.mysite.core.repo.upload.FileUploadWebScript.executeImpl(FileUploadWebScript.java:37)
	at com.mysite.core.repo.BaseWebScript.execute(BaseWebScript.java:75)
	... 47 more

2024-06-13 09:22:49,124 INFO  [com.mysite.core.repo.BaseWebScript] [http-nio-8080-exec-53] ****** NEW WEBSCRIPT REQUEST ******
Server Path: http://repo.mysite.com:80
Service Path: /repo/service/company/search
Query String: center=cc&docId=a854dbad-af6e-43e3-af73-8ac66365e000

Now there are multiple log entries so we need to first check for the presence of this error "Illegal hex characters in escape (%) pattern". Then looking at the SessionID... in this case - [http-nio-8080-exec-43] but there can be lot of other and may be duplicate SessionID in the log, check the line starting with "Query String" with the same or close timestamp (HH:MM) and create a report like this -

AccountNumnber	PolicyNumber	Name					Location
09693720	13068616	wagnac%20%20slide%20coverage%20b	\\myloc\CoreTmp\app\pc\in\gwpc5799838158526007183.tmp

As you can see there are two entries in the logfile for the same SessionID http-nio-8080-exec-43 but we want record only for the entry where we got 1. Error "Illegal hex characters in escape" and 2. Entry originated at 2024-06-13 09:22.
We can compare _time too as request event and the error event can have difference in time. So, it will be better to search and compare with the timestamp strftime(_time, "%Y-%m-%d %H:%M"). This wau it will compare with Date, Hr, and Min.
BTW we might have same error with same SessionID in the log but it has to be different timestamp. So, it is very important to Chek for time also but with the formatted one.

I created one Splunk report. Inner and Outer query are able to provide results separately but when I merge and run, although it looking at the required events but not returning any data in the table -

index=myindex "Illegal hex characters in escape (%) pattern"
| rex field=_raw "\[http-nio-\d+-exec-(?<sessionID>\d+)\]" 
| eval outer_timestamp=strftime(_time, "%Y-%m-%d %H:%M")
| table outer_timestamp, sessionID
| join type=inner sessionID [ 
    search index=index "Query String" AND "myloc" AND "center=pc"
    | rex field=_raw "\[http-nio-\d+-exec-(?<sessionID>\d+)\]" 
    | rex "accountNum=(?<AccountNum>\d+)" 
    | rex "policyNum=(?<PolicyNum>\d+)" 
    | rex "name=(?<Name>[^&]+)" 
    | rex "description=(?<Description>[^&]+)"
    | rex "location=(?<Location>[^&]+)" 
    | eval inner_timestamp=strftime(_time, "%Y-%m-%d %H:%M")
    | table sessionID, AccountNum, PolicyNum, Name, Description, Location, inner_timestamp
] 
| where outer_timestamp = inner_timestamp
| table outer_timestamp, sessionID, AccountNum, PolicyNum, Name, Description, Location

What can be the issue? How can I get the desired result?

Thanks!

[yuanliu]

There can be several ways to do this.  Transaction is not the most efficient, but in this case, I want to use its maxspan feature because your "same or close timestamp" is very difficult to quantify.  The command is actually very simple after you reconstruct the data developers and error handlers put in there.

| rex "(\S+ +\S+) +(?<log_level>\S+) +\[(?<class>[^\[]+)\] +\[(?<threadId>[^\]]+)"
| rex "Query String: (?<query_string>.+)"
| rex "Service Path: (?<service_path>.+)"
| rex "The .+ Service (?<service_path>\S+)"
| rex "Caused by: (?<cause_exception>\S+): +(?<cause_error>.+)"
| transaction threadId startswith="log_level=INFO" endswith="log_level=ERROR" maxspan=1s
| where match(cause_error, "Illegal hex characters in escape")
| table accountNum	policyNum	name					location

Your sample data would give

accountNum	policyNum	name	location
09693720	13068616	wagnac%20%20slide%20coverage%20b	\myloc\CoreTmp\app\pc\in\gwpc5799838158526007183.tmp

Here is data emulation you can play with and compare with real data

| makeresults
| eval data = mvappend("2024-06-14 09:34:45,504 INFO  [com.mysite.core.repo.BaseWebScript] [http-nio-8080-exec-43] ****** NEW WEBSCRIPT REQUEST ******
Server Path: http://repo.mysite.com:80
Service Path: /repo/service/company/upload
Query String: center=pc&contentType=reqDocExt&location=\\myloc\CoreTmp\app\pc\in\gwpc6285603725604350160.tmp&name=Dittmar%20-%20NO%20Contents%20-%20%20company%20Application%20(Please%20Sign)%20-%20signed&contentCreator=ALEXANDER BLANCO&mimeType=application/pdf&accountNum=09631604&policyNum=12980920&jobIdentifier=34070053
2024-06-14 09:34:45,505 INFO  [com.mysite.core.repo.upload.FileUploadWebScript] [http-nio-8080-exec-43] Uploading file to pc from \\myloc\CoreTmp\app\pc\in\gwpc628560372560435",

"2024-06-13 09:22:49,101 INFO  [com.mysite.core.repo.BaseWebScript] [http-nio-8080-exec-43] ****** NEW WEBSCRIPT REQUEST ******
Server Path: http://repo.mysite.com:80
Service Path: /repo/service/company/upload
Query String: center=pc&contentType=reqDocExt&location=\\myloc\CoreTmp\app\pc\in\gwpc5799838158526007183.tmp&name=wagnac%20%20slide%20coverage%20b&description=20% rule&contentCreator=JOSEY FALCON&mimeType=application/pdf&accountNum=09693720&policyNum=13068616",

"2024-06-13 09:22:49,101 INFO  [com.mysite.core.repo.upload.FileUploadWebScript] [http-nio-8080-exec-43] The Upload Service /repo/service/company/upload failed in 0.000000 seconds, null",

"2024-06-13 09:22:49,103 ERROR [org.springframework.extensions.webscripts.AbstractRuntime] [http-nio-8080-exec-43] Exception from executeScript: 051333149 Failed to execute web script.
org.springframework.extensions.webscripts.WebScriptException: 051333149 Failed to execute web script.
	at com.mysite.core.repo.BaseWebScript.execute(BaseWebScript.java:105)
	at org.repo.repo.web.scripts.RepositoryContainer.lambda$transactionedExecute$2(RepositoryContainer.java:556)
	at org.repo.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:450)
	at org.repo.repo.web.scripts.RepositoryContainer.transactionedExecute(RepositoryContainer.java:539)
	at org.repo.repo.web.scripts.RepositoryContainer.transactionedExecuteAs(RepositoryContainer.java:663)
	at org.repo.repo.web.scripts.RepositoryContainer.transactionedExecuteAs(RepositoryContainer.java:699)
	... 23 more
Caused by: java.lang.IllegalArgumentException: URLDecoder: Illegal hex characters in escape (%) pattern - Error at index 0 in: \" r\"
	at java.base/java.net.URLDecoder.decode(URLDecoder.java:232)
	at java.base/java.net.URLDecoder.decode(URLDecoder.java:142)
	at com.mysite.core.repo.util.RepositoryUtils.decodeValue(RepositoryUtils.java:465)
	at com.mysite.core.repo.BaseWebScript.getParameterMap(BaseWebScript.java:138)
	at com.mysite.core.repo.upload.FileUploadWebScript.executeImpl(FileUploadWebScript.java:37)
	at com.mysite.core.repo.BaseWebScript.execute(BaseWebScript.java:75)
	... 47 more",

"2024-06-13 09:22:49,124 INFO  [com.mysite.core.repo.BaseWebScript] [http-nio-8080-exec-53] ****** NEW WEBSCRIPT REQUEST ******
Server Path: http://repo.mysite.com:80
Service Path: /repo/service/company/search
Query String: center=cc&docId=a854dbad-af6e-43e3-af73-8ac66365e000")
| mvexpand data
| rename data AS _raw
| extract
| rex "(?<_time>\S+ +\S+)"
| eval _time = strptime(_time, "%F %T.%N")
| sort - _time
``` data emulation above ```

[runiyal]

Thanks Yuan,

Issue I am seeing is that value for "location" is coming as empty. Whereas I can see there is data in raw for location. What can be the issue?

Thanks!

[yuanliu]

First, can you confirm that transaction grouped the correct events?

Second, do you mean to say that even though one of the events in a transaction is

2024-06-13 09:22:49,101 INFO  [com.mysite.core.repo.BaseWebScript] [http-nio-8080-exec-43] ****** NEW WEBSCRIPT REQUEST ******
Server Path: http://repo.mysite.com:80
Service Path: /repo/service/company/upload
Query String: center=pc&contentType=reqDocExt&location=\\myloc\CoreTmp\app\pc\in\gwpc5799838158526007183.tmp&name=wagnac%20%20slide%20coverage%20b&description=20% rule&contentCreator=JOSEY FALCON&mimeType=application/pdf&accountNum=09693720&policyNum=13068616

Splunk does not give you  location with value \\myloc\CoreTmp\app\pc\in\gwpc5799838158526007183.tmp?  This is nearly impossible but you can try add extract command after index search.  If you look at the emulation I listed above, I used extract to emulate Splunk's default action.