I apologize in advance for the long question...I want to make sure to describe the issue thoroughly.
The basic goal:
I am trying to get a table from one database (let's call this the source table) to be replicated and stay replicated exactly within our Splunk database. I do not want to have historical data in the Splunk database but rather a direct copy of the source table in the Splunk database. This means that when new data is updated in the source table, I would like to have the data in the Splunk database updated as well (not inserted as a new row in the Splunk table). We would like to create charts, alerts, etc off of the data within Splunk.
Paths already taken:
I have used the DB Connect app to push data from the source database. Item 1 and 2 shows the results of that effort:
Path 1. The DB Inputs' "Batch Input" type would be an ideal for what I am trying to accomplish. My understanding is that "Batch Input" drops the existing Splunk database and recreates the Splunk table anew from the source table every time. The problem with this solution is that the source table is huge. Dropping the existing Splunk table and regenerating it from scratch every hour or even every day would be an enormous use of data.
Path 2. The DB Inputs' "Rising Column" type looked promising but seems to violate my goal of not inserting the data from my source table into the Splunk table.
Path 3. I have started using the Splunk C# SDK to program a solution to this issue. My thought is that if use a scheduled C# service to:
a. get the data from the source table;
b. perform a search and purge the targeted existing data in the Splunk table (via a "search query | delete" SearchOneShotAsync method call) and;
c. and then insert (via a SendAsync method call) that I can "update" the data in the Splunk database.
Thus keeping a replica table in Splunk without the disadvantaged discussed above.
Here's the code (finally right? 🙂
public class SplunkFacade
{
private readonly ILogger _log;
private readonly IConfig _config;
private string _indexName;
public SplunkFacade(ILogger log, IConfig config)
{
_log = log;
_config = config;
}
public void UpdateEventData(string indexName)
{
_log.Verbose("UpdateEventData");
_indexName = indexName;
using (var service = new Service(Scheme.Https, _config.SplunkUri, _config.SplunkPort))
{
_log.Verbose("Connecting to {0}:{1} ", service.Context.Host, service.Context.Port);
try
{
var fakeKeyToUpdate = "5";
var fakeValueToUpdate = "Jim Jones";
LogOnAsync(service).Wait();
PurgePreviousValuesAsync(service, fakeKeyToUpdate).Wait();
InsertIntoSplunkAsync(service, fakeKeyToUpdate, fakeValueToUpdate).Wait();
}
catch (Exception ex)
{
_log.Fatal(ex, "Error inserting");
throw;
}
finally
{
LogOffAsync(service).Wait();
}
}
}
private async Task LogOnAsync(Service service)
{
await service.LogOnAsync(_config.SplunkUserName, _config.SplunkPassword);
}
private async Task LogOffAsync(Service service)
{
await service.LogOffAsync();
}
private async Task InsertIntoSplunkAsync(Service service, string key, string value)
{
ITransmitter transmitter = service.Transmitter;
await transmitter.SendAsync(String.Format("my_id={0} my_name={1}", key, value), _indexName);
}
private async Task PurgePreviousValuesAsync(Service service, string key)
{
var searchResults = await service.SearchOneShotAsync(GetPurgeSplunkSql(key));
foreach (var result in searchResults)
{
//write out the raw event
_log.Information("Result from Query -> {0}", result.ToString());
}
}
private string GetPurgeSplunkSql(string key)
{
var result = String.Format(@"search index=""{0}"" my_id=""{1}"" | delete", _indexName, key);
//var result = "search index=test-index my_id=5 | delete";
return result;
}
}
The question:
First off, please feel free to tell me if there's a more effective mechanism to achieve this goal. I am open to other ways to solve this issue.
The code above runs and the InsertIntoSplunkAsync method inserts test data into the Splunk database nicely. The PurgePreviousValuesAsync method does not, however, delete the previous values even though the searchResults object shows no errors. It does not work, even when the hard-coded version without quotes is used, which works when directly run against the web server with or without quotes (see image below showing it working on the web server).
When I run this code three times in the Visual Studio debugger, I expect to see only one instance of "my_id=5" but I see instead three. See the second image below showing three events. Perhaps the SearchOneShotAsync is not capable of deleting, but only retrieving...if so, is there a way for me to remove the values in searchResults in the foreach loop via a RemoveAsync method call? Or perhaps there another SDK method capable of the "... | delete" query?
So the question, how do I achieve the goal above? Either through fixing the existing code or using a better path.
I recognize that this is an unusual task to do with Splunk, but I'd appreciate any suggestions.
Thank you for reading and for any help that you can give me.
Best regards,
Michael
