All Apps and Add-ons

Cisco Secure ACS 5.x

williambeattie
Engager

I am looking for a method or sql query to ingest Cisco Secure ACS Monitoring and Report Viewer data into Splunk.

  • In Cisco Secure ACS I followed the instructions to setup an external MS SQL database.

  • Under "Monitoring Configuration > System Configuration > Remote Database Settings" I pointed to the database I just created and set "Export Every Minute(s)" to the lowest setting 20min.

  • Then in Splunk using "Splunk DB Connect" I connected to the ACS SQL Database and can use the SQL App to query the database and see data.

Splunk search also works to query the data and works remarkably well. I just need to figure out how to ingest this data into Splunk.

Has anyone done this?

Here are the Queries I made to display data

ACS Radius Authentication

| dbquery "ACS-OURSQLSERVER" "select *\nfrom dbo.acsmessagecatalog, 
dbo.acsradiusauthentication\nwhere dbo.acsmessagecatalog.MESSAGECODE = 
dbo.acsradiusauthentication.MessageCode;" 

| convert timeformat="%Y-%m-%d %T" mktime(ACSTimestamp)
| fieldformat ACSTimestamp=strftime(ACSTimestamp,"%Y-%m-%d %T") 

|Table ACSTimestamp, ACSServer, MESSAGECLASS, MESSAGETEXT, ACSSessionID, AccessService, ServiceSelectionPolicy, AuthorizationPolicy, UserName, IdentityStore, AuthenticationMethod, NetworkDeviceName, IdentityGroup, NetworkDeviceGroups, Response, CallingStationID, NASPort, ServiceType, AuditSessionID, CTSSecurityGroup, FailureReason, UseCase, FramedIPAddress, NASIdentifier, NASIPAddress, NASPortId, CiscoAVPair, ADDomain, RadiusResponse, ACSUserName, RadiusUserName, SelectedIdentityStore, AuthenticationIdentityStore, AuthorizationExceptionPolicyMa, ExternalPolicyServerMatchedRul, GroupMappingPolicyMatchedRule, IdentityPolicyMatchedRule, NASPortType, QueryIdentityStores, SelectedAuthorizationProfiles, SelectedExceptionAuthorization, SelectedQueryIdentityStores, EapAuthentication, NADFailure, Passed, Failed

ACS TACACS Authentication

| dbquery "ACS-OURSQLSERVER" "select *\nfrom dbo.acsmessagecatalog, 
dbo.acstacacsauthentication\nwhere dbo.acsmessagecatalog.MESSAGECODE = 
dbo.acstacacsauthentication.MessageCode;" 

| convert timeformat="%Y-%m-%d %T" mktime(ACSTimestamp)
| fieldformat ACSTimestamp=strftime(ACSTimestamp,"%Y-%m-%d %T") 

|Table ACSTimestamp, ACSServer, MESSAGECLASS, MESSAGETEXT, ACSSessionID, AccessService, ServiceSelectionPolicy, AuthorizationPolicy, UserName, IdentityStore, AuthenticationMethod, AuthenType, NetworkDeviceName, DeviceIPAddress, IdentityGroup, NetworkDeviceGroups, Response, PriviligeLevel, FailureReason, ADDomain, AuthenMethod, GroupMappingPolicyMatchedR, IdentityPolicyMatchedRule, QueryIdentityStores, RemoteAddress, SelectedAuthenticationIdenti, SelectedQueryIdentityStores, Service, AVPair, ExecutionSteps, OtherAttributes, SelectedShellProfile, AuthorizationExceptionPolicyMa, ResponseTime, Passed, Failed
0 Karma
1 Solution

eric_budke
Path Finder

You can send it via syslog. It is a little time consuming initially, but go to System Administration->Log Configuration->Logging Categories->Global and edit all of them and add the syslog destination in.

You have to set up a remote log target first in System Administration->Log Configuration->Remote Log Targets

View solution in original post

eric_budke
Path Finder

You can send it via syslog. It is a little time consuming initially, but go to System Administration->Log Configuration->Logging Categories->Global and edit all of them and add the syslog destination in.

You have to set up a remote log target first in System Administration->Log Configuration->Remote Log Targets

williambeattie
Engager

My SQL query shows prettier info but the info I was looking for is now getting ingested. Many thanks Eric

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...