Splunk Search

Splunk rex bring back sql query

ATMO1
New Member

Hi Guys,

I'm hoping someone can help. I have log data which is generated from SAS EG. I want to create a report which shows the username, log location date and sql code (generated by sas eg which is shown in the log).

So far I have the following Splunk search which works great brings back the username, month, year and location.

index="xxxx_logs" UserName
|dedup source, sourcetype, date_month, date_year, UserName
|rex "\s+(?<UserName>UserName \d+)" 
|table UserName, date_month, date_year, source
|sort date_year, date_month 
|rename source as "Log Name/Location", date_month as "Month Generated", date_year as "Year Generated"

Now I would want a column in the search to show the sql search but am confused on how to do so. The actual log shows the sql code as below:
2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [PROC SQL;]
2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [ CREATE VIEW WORK.SORT AS]
2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [ SELECT T.MAIN_DESC, T.SUM_of_ATT]
2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [ FROM REF as T]
2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [;]
2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [QUIT;]

So I would like to have in my report the sql quid from [PROC SQL;] up to [QUIT;]............. Is this possible? Any help will be greatly appreciated. Thank you

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

I'm assuming from your example that
- all the SQL lines get the same timestamp
- the user field is attached to each SQL line.
- the SQL are in individual event lines, rather than already rolled together for you.

This generates test data with a couple dups at different times for the same user...

| makeresults  
| eval user="John Doe!!!!Tran Nguyen!!!!Carlos Castaneda!!!!John Doe!!!!Carlos Castaneda"  
| makemv delim="!!!!" user
| mvexpand user 
| streamstats count 
| eval _time = _time + count 
| eval mydata="2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [PROC SQL;]!!!!2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [ CREATE VIEW WORK.SORT AS]!!!!2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [ SELECT T.MAIN_DESC, T.SUM_of_ATT]!!!!2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [ FROM REF as T]!!!!2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [;]!!!!2017-04-04 12:49:33,452 [19] DEBUG JobSpy [(null)] - [QUIT;]" 
| makemv delim="!!!!" mydata 
| mvexpand mydata 
| rename mydata as _raw 

This adds a sequence number onto the front of each _raw so that the transaction command won't sort them out of order, then uses transaction to roll them together.

| streamstats count as rawseq by user _time 
| eval rawseq =rawseq+100000 
| eval _raw = tostring(rawseq)." "._raw 
| fields - rawseq 
| transaction _time user mvraw=t

... and this pulls out the SQL commands

| rex  "JobSpy \[[^\]]*\] - \[(?<SQLCommand>[^\]]*)\]"

With the SQLCommand field being a multivalue field looking like this...

PROC SQL;
CREATE VIEW WORK.SORT AS
SELECT T.MAIN_DESC, T.SUM_of_ATT
FROM REF as T
;
QUIT;

... or, if you prefer you can reformat it further...

| eval SQLCommand2=mvjoin(SQLCommand," ")

... and present the same data in a non-mv field like this

PROC SQL; CREATE VIEW WORK.SORT AS SELECT T.MAIN_DESC, T.SUM_of_ATT FROM REF as T ; QUIT;
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Can you add other event sample which contains UserName? Based on your logs samples in the question, the SQL query is splitted into multiple entries, so how can we correlate it to a user?

woodcock
Esteemed Legend

Are these lines inside of a bigger event or is each line an event? Show more sample data (all related lines for a few SQL searches).

0 Karma

ATMO1
New Member

Apologies for the delayed response. I'm fairly new to Splunk so only kow basics at the moment, below i've shown more lines this is within the log file. The SQL code is usually under [PROC SQL;] .. These logs are generated from SAS to show what users are building in their query. My main aim is to add the sql code into a report in Splunk and possibly set it up to extract as a field? Would this be possible. I've looked into look ups but I don't think that is possible....

2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [OPTIONS DEV=ACTIVEX;]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [GOPTIONS XPIXELS=0 YPIXELS=0;]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [FILENAME EGSR TEMP;]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [    STYLE=HtmlBlue]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [    STYLESHEET=(URL="file:///R:/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [    NOGTITLE]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [    NOGFOOTNOTE]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [    GPATH=&sasworklocation]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [    ENCODING=UTF8]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [    options(rolap="on")]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [;]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - []
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [GOPTIONS ACCESSIBLE;]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [%_eg_conditional_dropds(WORK.QUERY_F);]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - []
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [PROC SQL;]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [   CREATE TABLE WORK.QUERY_F AS ]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [   SELECT t1.FYEAR, ]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [          t1.RE, ]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [          /* SUM_of_FLAG */]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [            (SUM(t1.FLAG)) FORMAT=11. AS SUM_of_FLAG, ]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [          /* Out of Hours */]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [            (Case When t1.TIME between '0900' and '1700' then '9 to 5' else 'Out of hours' End) AS ]
2017-04-24 12:11:47,617 [18] DEBUG JobSpy [(null)] - [            'Out of Hours'n]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [      FROM H900.HE_1415 t1]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [           LEFT JOIN H9.ACATEGORY t2 ON (t1.CAT = t2.AE_ACATEGORY_CODE AND ]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [          (t2.IS_CURRENT = 1))]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [           LEFT JOIN H900.AGE_BANDS t3 ON (t1.AR_CALC = t3.AG AND (t3.AG_BAND = 'nary'))]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [      WHERE t3.AG < 5 AND t1.RE = '0F' AND ( t2.ADE = '1' OR ]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [           t2.AGORY_CODE = '9' )]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [      GROUP BY t1.FYEAR,]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [               t1.RE,]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [               (CALCULATED 'Out of Hours'n);]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [QUIT;]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - []
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [GOPTIONS NOACCESSIBLE;]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - []
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - []
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - []
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - []
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [%LET _CLIENTTASKLABEL=;]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [%LET _CLIENTPROCESSFLOWNAME=;]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [%LET _CLIENTPROJECTPATH=;]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [%LET _CLIENTPROJECTNAME=;]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - []
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [;*';*";*/;quit;run;]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - [ODS _ALL_ CLOSE;]
2017-04-24 12:11:47,632 [18] DEBUG JobSpy [(null)] - []
2017-04-24 12:11:47,632 [18] DEBUG SAS.EG.JobManagement.WorkspaceJob [(null)] - (Id=1) OnExecuting() - setting up data structures
2017-04-24 12:11:47,632 [18] DEBUG SAS.EG.JobManagement.WorkspaceJob [(null)] - (Id=1) OnExecuting() - submitting code to server
2017-04-24 12:11:47,648 [18] DEBUG SAS.EG.JobManagement.Job [(null)] - (Id=1) OnStatusChanged() - entering
2017-04-24 12:11:47,648 [18] DEBUG SAS.EG.JobManagement.Job [(null)] - (Id=1) OnStatusChanged() - leaving
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...