Deployment Architecture

CHAR INDEX query same as MS SQL

puneethgowda
Communicator

alt text

index="prod_client_sync_dbconnect" sessionid!=sessionid | eval exception=substr(exception,1,150) | stats count as "Number of errors" by exception | rename exception as Exception | sort - "Number of errors"

We need to show the error till double colon (::) after double colon (::) whatever the error should truncate and display only before double colon (::).

Tags (1)
0 Karma

Richfez
SplunkTrust
SplunkTrust

EDIT: copy paste error again. I should just stop this morning until I finish this cup of coffee... 🙂

I'm assuming you want to replace your eval of exception below to make one that reads up to double colons? Use the following, then.

index="prod_client_sync_dbconnect" sessionid!=sessionid | rex field=_raw "^(?<exception>.*)::"  | stats count as "Number of errors" by exception | rename exception as Exception | sort - "Number of errors"

Let us know how it goes!

Happy Splunking!
Rich

0 Karma

puneethgowda
Communicator

alt text

Hi Rich,

Thanks a lot but nothing is happening with your query.

Cannot insert the value NULL into column 'city_cd', table 'HotelHubServices.dbo.pb_custmastclntsync_mc'; column does not allow nulls. INSERT fails. The statement has been terminated. :: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at TCT.DbConnect.Getrs(String SpName, SqlParameter[] SpParameters, Int32 TimeOut, Boolean ConnectLogDatabase) 
0 Karma

Richfez
SplunkTrust
SplunkTrust

There could have been more in your search that I didn't see because you didn't use the code button to format it. The only important part I did was replace your eval exception=... with the rex field=_raw ( ....

Or, do you want a SQL query to do this? I read your question as "Please help me make a Splunk search that does what in a SQL Query I'd describe as ..."

As shown my this run-anywhere example, the rex works. 🙂

| gentimes start=11/21/2016 end=11/22/2016
| eval test="Failed: 142352C Code: PH Power Hotel:: This is test that should be stripped" 
| rex field=test "^(?<exception>.*)::" 
| table test, exception

Gives results

test            exception   
Failed: 142352C Code: PH Power Hotel:: This is test that should be stripped     Failed: 142352C Code: PH Power Hotel 

If you need a SQL query, well, this isn't the right forums for that. But I can give you the hint that you'll wan to use your SQL language's variety of "search" or "find" to search for particular text "::" inside the field, and use that return position in your substring command. You can find examples using your favorite search engine.

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