Parsing from SAS SSL_Request Log

[SasPerson85]

Hello,

I am trying to parse the ssl_request log from our sas bi portal. The field I am trying to parse is the query field within this log.

Examples of these strings:

GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content%2Bdav%3A%2F%2FWebDAV%2FUGAdmDataMgt%2FSlate-Banner%2520Sync%2520Issues.xlsx%2FContent HTTP/1.1

GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content%2Bdav%3A%2F%2FWebDAV%2FUGAdmDataMgt%2FSlate%2520Suspected%2520Matches.xlsx%2FContent HTTP/1.1

GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content%2Bdav%3A%2F%2FWebDAV%2FFunnels%2FApplication_Type_Funnel.pdf%2FContent HTTP/1.1

GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content%2Bdav%3A%2F%2FWebDAV%2FEnrollment%2FFall%2520Enrollment%2520and%2520Prior%2520Enrollment%2520Report.pdf%2FContent HTTP/1.1

GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content%2Bdav%3A%2F%2FWebDAV%2FUGStudentSuccess%2FRetention%2520Fall%2520to%2520Fall%2520with%2520Comparisons.pdf%2FContent HTTP/1.1

So I am wanting to parse out the pink and green portions of these strings that I highlighted into two new columns. The entire part to the left of the pink text is consistent for all strings. Starting with the pink text and to the right to the end of the string can vary in words, html, etc...

For these examples I would like to see a end results like:

Group	Report
UGAdmDataMgt	Slate-Banner Sync Issues.xlsx
UGAdmDataMgt	Slate Suspected Matches.xlsx
Funnels	Application_Type_Funnel.pdf
Enrollment	Fall Enrollment and Prior Enrollment Report.pdf
UGStudentSuccess	Retention Fall to Fall with Comparisons.pdf

My code so far for testing:

data temp1;
      infile "\\have\e$\SAS\Config\Lev1\Web\WebServer\logs\ssl_request_2019-07-15-17.21.log" lrecl=100000 dsd dlm=' ' end=end missover ;
      input  datetime :$21. offset :$6. ip :$14. sslver :$8. encrypt :$25. query :$2500. session :$8.;
run;
data temp2;
      infile "\\have\e$\SAS\Config\Lev1\Web\WebServer\logs\ssl_request_2019-07-15-17.08.log" lrecl=100000 dsd dlm=' ' end=end missover ;
      input  datetime :$21. offset :$6. ip :$14. sslver :$8. encrypt :$25. query :$2500. session :$8.;
run;
data temp3;
      infile "\\have\e$\SAS\Config\Lev1\Web\WebServer\logs\ssl_request_2019-07-15-14.22.log" lrecl=100000 dsd dlm=' ' end=end missover ;
      input  datetime :$21. offset :$6. ip :$14. sslver :$8. encrypt :$25. query :$2500. session :$8.;
run;
data temp4;
      infile "\\have\e$\SAS\Config\Lev1\Web\WebServer\logs\ssl_request_2019-07-10-22.34.log" lrecl=100000 dsd dlm=' ' end=end missover ;
      input  datetime :$21. offset :$6. ip :$14. sslver :$8. encrypt :$25. query :$2500. session :$8.;
run;
data temp5;
      infile "\\have\e$\SAS\Config\Lev1\Web\WebServer\logs\ssl_request_2019-07-06-01.03.log" lrecl=100000 dsd dlm=' ' end=end missover ;
      input  datetime :$21. offset :$6. ip :$14. sslver :$8. encrypt :$25. query :$2500. session :$8.;
run;
data temp_test;
	set temp:;
	if substr(query,1,56) = 'GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content';
	if ip ne 'xxx.xx.x.xx';
run;

Sample output:

Thanks in advance.

[Kurt_Bremser · Accepted solution]

See this code for extracting the two items from your query fiekd:

options urlencoding=utf8;

data test;
infile datalines truncover;
input query $200.;
query = urldecode(urldecode(query)); /* double urldecode takes care of double characters */
query = substr(query,index(query,'/WebDAV/') + 8);
group = scan(query,1,'/');
report = scan(query,2,'/');
drop query;
datalines;
GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content%2Bdav%3A%2F%2FWebDAV%2FUGAdmDataMgt%2FSlate-Banner%2520Sync%2520Issues.xlsx%2FContent HTTP/1.1
GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content%2Bdav%3A%2F%2FWebDAV%2FUGAdmDataMgt%2FSlate%2520Suspected%2520Matches.xlsx%2FContent HTTP/1.1
GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content%2Bdav%3A%2F%2FWebDAV%2FFunnels%2FApplication_Type_Funnel.pdf%2FContent HTTP/1.1
GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content%2Bdav%3A%2F%2FWebDAV%2FEnrollment%2FFall%2520Enrollment%2520and%2520Prior%2520Enrollment%2520Report.pdf%2FContent HTTP/1.1
GET /SASPortal/viewItem.do?com.sas.portal.ItemId=Content%2Bdav%3A%2F%2FWebDAV%2FUGStudentSuccess%2FRetention%2520Fall%2520to%2520Fall%2520with%2520Comparisons.pdf%2FContent HTTP/1.1
;

proc print data=test noobs;
run;

Result:

group               report

UGAdmDataMgt        Slate-Banner Sync Issues.xlsx                  
UGAdmDataMgt        Slate Suspected Matches.xlsx                   
Funnels             Application_Type_Funnel.pdf                    
Enrollment          Fall Enrollment and Prior Enrollment Report.pdf
UGStudentSuccess    Retention Fall to Fall with Comparisons.pdf