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!

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