Cisco Secure ACS 5.x

[williambeattie]

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

[eric_budke · Accepted solution]

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]

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