Help with report creation please

[GIA]

hello I am pretty new using Splunk and I am being tasked to generate multiple of these kinds of reports in Splunk (original reports were from a SQL tool) 

I really need help in finding the right query for this. Especially how to include certain users and exclude others. 

your help is greatly appreciated!!

————-
* Collect all available log sources.

* Generate a report that shows Changes to System Sec Config events occurred on the previous day, grouped by source users. 

• Format: .csv, List of events , table with subset of fields (User, Date/Time, Event, Group, oHost, Host (Impacted), oLogin, VendorMsgID, Domain Impacted), Grouped by User

• Schedule: daily

• Search window: -24 hours

• Expiration: 30 days

# Technical Context

The following events are of interest

Vendor Message IDs - 4727, 4728, 4729, 4730, 4731, 4732, 4733, 4734, 4735, 4736, 4737, 4740, 4754, 4755, 4756, 4757, 4758, 4759, 4783, 4784, 4785, 4786, 4787, 4788, 4789, 4791, 631

AND User is NOT xxx, system, xxx, xxxx, xxxxx, 

AND User (Impacted) IS NOT (res group name) 

AND Host (Impacted) IS NOT %sc% (SQL PATTERN), %sd% (SQL PATTERN), ^sc.+, ^sd.+

[richgalloway]

I think you'll find you'll get more help from these forums if you show you've made some effort to solve the problem before posting.  Tell what query you've tried and how it didn't meet expectations.

SQL users new to SPL should see Splunk's SPL for SQL Users manual at https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/SQLtoSplunk

IMO, any requirement that calls for "all available log sources" hasn't been thought through enough.  It's a rare use case that needs every source; most will need specific sources.

We need to know more about the "report that shows Changes to System Sec Config events".  Splunk tracks its own config changes in the _configtracker index, but that information does not include user info.  If you refer to configs for other systems then please identify those systems.  Of course, those systems must be reporting info to Splunk.

The first step to creating a report is to create a search for the data that goes into that report.  Once that is producing the desired results, click on the "Save as" menu and select Report.  That is where you select the report format and schedule it.

The expiration time of a search/report must be set manually from the Jobs dashboard (Activity->Jobs).  IIRC, the maximum TTL is seven days.  The 30-day expiration date is too long and may use up the users' disk quota.

You can use the IN operator in the search command to filter message IDs and users.  SQL patterns are available using the like function within where and eval commands.  Use the table command to select fields for display and the stats command to group results.

index=foo VendorMsgID IN (4727, 4728, 4729, 4730, 4731, 4732, 4733, 4734, 4735, 4736, 4737, 4740, 4754, 4755, 4756, 4757, 4758, 4759, 4783, 4784, 4785, 4786, 4787, 4788, 4789, 4791, 631)
AND NOT User IN (xxx, system, xxx, xxxx, xxxxx)
AND User_Impacted != (res group name) 
| where NOT (match(Host_Impacted, "%sc%") OR match(Host_Impacted, "%sd%") OR match(Host_Impacted, "^sc.+") OR match(Host_Impacted, "^sd.+"))
| table User, _time, Event, Group, oHost, Host_Impacted, oLogin, VendorMsgID, Domain Impacted)
| stats values(*) as * by User

[GIA]

Hello,

Thank you so much. The event IDs listed are all regarding changes to the system. This report would be the "report that shows Changes to System Sec Config events". Regarding all logs, we have identified the specific ones. 

I am running the query you suggested but it's not giving any results. No error messages. Thanks again! 🙂

index=foo eventid IN (4727, 4728, 4729, 4730, 4731, 4732, 4733, 4734, 4735, 4736, 4737, 4740, 4754, 4755, 4756, 4757, 4758, 4759, 4783, 4784, 4785, 4786, 4787, 4788, 4789, 4791, 631) 

| fields user, action, subject, ProcessName

| stats min(_time) as FirstEvent max(_time) as LastEvent count by user, _time, action, subject, ProcessName

AND NOT User IN (list_of_users )

AND User_Impacted != (AD_Group)

| where NOT (match(Host_Impacted, "sc") OR match(Host_Impacted, "sd") OR match(Host_Impacted, "^sc.+") OR match(Host_Impacted, "^sd.+"))

| table User, _time, EventID, Group, Host, Host_Impacted, Login, VendorMsgID, Domain Impacted)

| stats values(*) as * by User

[richgalloway]

The query I provided was an example that must be customized for your environment.  At the very least, the index name "foo" must be changed to the name of the index that holds the event data.  The field names also may need to be changed.  Look at the events to see what is available to you.

[GIA]

Hi, my apologies for not being more specific. The query I provided was modified for security reasons. I am indeed replacing the placeholders like "foo"  with the correct indexes, plus the rest of the required data( host names etc.) Thank you.

[richgalloway]

One the query one pipe at a time (start with the first pipe and iteratively add the next) until you find the one that returns no results.  Share that command and we'll see if can figure out what's happening.