Automate Splunk Integration Tasks from PowerShell

The CData ADO.NET Provider for Splunk is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Splunk.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Splunk; this tutorial shows how to use the Provider to create, retrieve, update, and delete Splunk data.

Once you have acquired the necessary connection properties, accessing Splunk data in PowerShell can be enabled in three steps.

To authenticate requests, set the User, Password, and URL properties to valid Splunk credentials. The port on which the requests are made to Splunk is port 8089.

The data provider uses plain-text authentication by default, since the data provider attempts to negotiate TLS/SSL with the server.

If you need to manually configure TLS/SSL, see Getting Started -> Advanced Settings in the data provider help documentation.

1. Load the provider's assembly:

   
   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Splunk\lib\System.Data.CData.Splunk.dll")
       

2. Connect to Splunk:

    
   $conn= New-Object System.Data.CData.Splunk.SplunkConnection("user=MyUserName;password=MyPassword;URL=MyURL;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

3. Instantiate the SplunkDataAdapter, execute an SQL query, and output the results:

   
   $sql="SELECT Name, Owner from DataModels"
   
   $da= New-Object System.Data.CData.Splunk.SplunkDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.name $_.owner
   }
     

Update Splunk Data

$cmd =  New-Object System.Data.CData.Splunk.SplunkCommand("UPDATE DataModels SET Id='SampleDataset' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Splunk.SplunkParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Splunk Data

$cmd =  New-Object System.Data.CData.Splunk.SplunkCommand("INSERT INTO DataModels (Id) VALUES (@myId)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Splunk.SplunkParameter("@myId","SampleDataset")))
$cmd.ExecuteNonQuery()

Delete Splunk Data

$cmd =  New-Object System.Data.CData.Splunk.SplunkCommand("DELETE FROM DataModels WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Splunk.SplunkParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()