Splunk Search

How do I search who is exporting aggregate data from my log files?

ATMO1
New Member

I am fairly new to Splunk and hoping someone could help with this. I have Index log files loaded onto Splunk, so to begin, I am searching keywords such as "Export" and the server name which works fine. Now I would want to know who is exporting aggregate data, so looking at a log file individually (without Splunk) I can see the sql script, and if the script contains a group by clause, then I can assume this is an aggregated export. Is there anyway I could do this in Splunk?

0 Karma

maciep
Champion

Actually, you may be able to search your logs just as they are. I'm still not sure where the user, but playing with just that sample log entry, something like this might work?

[your search]
| reverse 
| rex "^\S+\s+\S+\s+\S+\s+\S+\s+\S+\s+\S+\s+\- \[\w\s\d+(?<script_line>.+)" 
| where isnotnull(script_line) 
| stats list(script_line) as script by _time 
| where match(script,"(?i)group by")

Since splunk will show the "latest" events first, reverse the results so that script is in order (may not actually be needed). In each event, try to use rex to capture a line of the script. I'm very lazy with my regex,I'm sure that could be heavily improved upon.

No filter out events where no script line was found. Then combine the script values by timestamp into one multi-value field called script. I'm not sure how consistently each event of a script will have the same timestamp, but we might be able to do some time manipulation if they're not.

And finally, just look for events where the script matches group by.

I know this is a very limited sample and your actual log data may add more hurdles, but this might be a good start?

alt text

0 Karma

ATMO1
New Member

Apologies for the delay in reply. Thank you for reply this does bring back the log file in the code which is a good start for me! I'm not sure how the rex is working...... Is it looking for "Group By" in any line within any log?

0 Karma

maciep
Champion

I think some sample log entries would be helpful. For example, does adding "group by" to your search bring back entries you want or are the log files more complicated than that?

But most likely, splunk can get at the data you want, yes. We just need to better understand what that data looks like.

0 Karma

ATMO1
New Member

Hi maclep thanks for replying. The log files are complicated, they are generated from a SAS server. Nothing comes back if i search for the term "Group By"

0 Karma

maciep
Champion

Understood. Are you able to post some of the sample log entries, of course masking anything private? We'll need some idea what the data looks, otherwise we really won't know how to help you search it.

0 Karma

ATMO1
New Member

Do you mean from Splunk or directly from the log files?

0 Karma

maciep
Champion

either should be fine, I think. Are your events being parsed correctly in Splunk? Meaning, do the log files look broken up correctly in Splunk? Or do you have say multiple events in Splunk that you would consider to be one entry in the log file?

0 Karma

ATMO1
New Member

Some of the log file code (generally there is a lot more than this):

2016-03-15 11:25:48,364 [Main] INFO  SAS.EG.App [(null)] - Starting SEGuide
2016-03-15 11:25:48,614 [Main] INFO  SAS.EG.App [(null)] - Version: File:             C:\
InternalName:     SEGuide.exe
OriginalFilename: SEGuide.exe
FileVersion:      7.100.1.2711
FileDescription:  SAS Enterprise Guide 7.1
Product:          SAS Enterprise Guide 7.1
ProductVersion:   7.11 (7.100.1.2711)
Debug:            False
Patched:          False
PreRelease:       False
PrivateBuild:     False
SpecialBuild:     False
Language:         Language Neutral


Does that help? Not sure if i could upload a print screen of results from Splunk?
0 Karma

maciep
Champion

Sorry for the slow response, I don't have a lot of time to spend out here while at work.

What about an example where the script data is included? Is it similar but with a script section?

0 Karma

ATMO1
New Member

Hi below is the logfile containing the sql script:

2016-06-29 15:45:45,822 [14] DEBUG SAS.EG.JobManagement.WorkspaceJob [(null)] - (Id=1) OnExecuting() - getting page setup
2016-06-29 15:45:45,822 [14] DEBUG JobSpy [(null)] - Log for job [1] on server [SASApp] at [*******]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [t11                                                          The SAS System                             ***********]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [t ]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 1          ;*';*";*/;quit;run;]************
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 2          OPTIONS PAGENO=MIN;]*********
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 3          %LET _CLIENTTASKLABEL*********
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 4          %LET _C********************
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 5          %LET _CLIENTPROJ**************
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 6          %LET _CLIENTPROJE***************
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 7          ]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 8          ODS _ALL_ CLOSE;]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 9          OPTIONS DEV=ACTIVEX;]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 10         GOPTIONS XPIXELS=0 YPIXELS=0;]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 11         FILENAME EGSR TEMP;]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 13             STYLE=HtmlBlue]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 14             STYLESHEET=(URL="file:///R****************")]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 15             NOGTITLE]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 16             NOGFOOTNOTE]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 17             GPATH=&sasworklocation]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 18             ENCODING=UTF8]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 19             options(rolap="on")]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 20         ;]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [n NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 21         ]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 22         GOPTIONS ACCESSIBLE;]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 23         %_eg_conditional_dropds(WORK.MAT);]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 24         ]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 25         PROC SQL;]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 26            CREATE TABLE WORK.MAT AS]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 27            SELECT t1.DELSTAT_1,]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 28                   t1.DELMETH_D,]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 29                   /* COUNT************ */]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 30                     (COUNT(t1.*********)) AS COUNT_of_********]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 31               FROM **************]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 32               WHERE ********* = 1]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 33               GROUP BY t1.********,]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 34                        t1.********]
2016-06-29 15:45:45,838 [14] DEBUG JobSpy [(null)] - [s 35               ORDER BY t1.******,]
0 Karma

maciep
Champion

That does look like a fun log file! I do see group by in there. Is this log in Splunk? But you can find it by searching for "group by"?

I understand that if those are all separate events, getting just a group by event back may not be too helpful. But I wonder if maybe it's worth putting all of those script events into one large event before indexing? Not sure how easy/feasible that would be, but could be worth a shot.

Also, you mentioned wanting to know which user ran that. Is that in the log anywhere too?

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 ...