Splunk Search

How to create column from below output?

bhaskar5428
Explorer

Hi Team,

Am using below query and wanted to create table out of raw data 

splunk query - index=* ("Exception occurred during ORC" OR ("Response received, system status NOK") AND NOT "n. a." sourcetype="kube:container:*slimits*-service") namespace IN ("dk1692-e","dk1399-c","dk1371-c","dk1398-c","dk1400-d")

Sample out put 
1
2023-05-18 05:16:48,083 INFO [com.db.gtb.bankingapi.slimits.orc.service.internal.OrcServiceImpl] (task-pool-2) - [e2eCallReference: ] Response received, system status NOK for FCS

 

2
2023-05-14 22:32:18,020 ERROR [com.db.gtb.bankingapi.slimits.orc.scheduled.OrcScheduler] (task-pool-3) - [e2eCallReference: ] Exception occurred during ORC, due to Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: ems-pool - Connection is not available, request timed out after 5000ms..

 

3

2023-05-13 05:06:05.808 [INFO] [scheduling-1] orcCheck(OrcServiceImpl.java:60) - Response received, system status NOK for ROUTER

 

2023-05-13 05:06:13,067 INFO [com.db.gtb.bankingapi.slimits.orc.service.internal.OrcServiceImpl] (task-pool-2) - [e2eCallReference: ] Response received, system status NOK for EMS

 

---------------------------------------------------------------------------------------------------------------------------

Expected output 

Date                                      time                            Status 
2023-05-13                      05:06                      system status NOK for EMS
2023-05-14                      22:32                       Exception occurred during ORC

 

 

 

 

 

Labels (2)
Tags (1)
0 Karma

bhaskar5428
Explorer

some of report i observed records shows somehing in 3d format 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Good luck with that - I have no idea what that would even look like or where to start! 😎🤔😁🦄

0 Karma

bhaskar5428
Explorer

Could you please help to segregate date and time in separate column 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| eval date=strftime(_time, "%F")
| eval time=strftime(_time, "%T")
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Assuming you already have _time extracted as the timestamp for the event, try this to filter and extract

| regex _raw="system status NOK for EMS|Exception occurred during ORC"
| rex "(?<status>system status NOK for EMS|Exception occurred during ORC)"
0 Karma

bhaskar5428
Explorer

Please note that its not only for EMS and ORC , there are multiple keywords 
i wanted to find below and convert status column

Exception occurred during [ORC,SDS,FCS,ROUTER]

Response received, system status NOK for  [ORC,SDS,FCS,ROUTER]

Note - your provided query only search for EMS and ORC 

can you assist something like with
| rex field=_raw <Date>
| rex field=_raw <Time>
| rex field=_raw <Status>

 

Sample record 

 

2023-05-18 05:16:48,083 INFO [com.db.gtb.bankingapi.slimits.orc.service.internal.OrcServiceImpl] (task-pool-2) - [e2eCallReference: ] Response received, system status NOK for FCS
 
 
2023-05-14 22:32:18,020 ERROR [com.db.gtb.bankingapi.slimits.orc.scheduled.OrcScheduler] (task-pool-3) - [e2eCallReference: ] Exception occurred during ORC, due to Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: ems-pool - Connection is not available, request timed out after 5000ms..
 
 
2023-05-14 22:32:11,204 ERROR [com.db.gtb.bankingapi.slimits.orc.scheduled.OrcScheduler] (task-pool-2) - [e2eCallReference: ] Exception occurred during ORC, due to Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: tds-pool - Connection is not available, request timed out after 5000ms..
 
 
2023-05-14 22:31:53,882 ERROR [com.db.gtb.bankingapi.slimits.orc.scheduled.OrcScheduler] (task-pool-4) - [e2eCallReference: ] Exception occurred during ORC, due to Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: tds-pool - Connection is not available, request timed out after 5000ms..
 
 
2023-05-14 22:31:48,996 ERROR [com.db.gtb.bankingapi.slimits.orc.scheduled.OrcScheduler] (task-pool-4) - [e2eCallReference: ] Exception occurred during ORC, due to Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: tds-pool - Connection is not available, request timed out after 5000ms..
 
 
2023-05-14 22:31:48,897 ERROR [com.db.gtb.bankingapi.slimits.orc.scheduled.OrcScheduler] (task-pool-9) - [e2eCallReference: ] Exception occurred during ORC, due to Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: sds-pool - Connection is not available, request timed out after 5001ms..
 
 
2023-05-13 05:07:03,306 INFO [com.db.gtb.bankingapi.slimits.orc.service.internal.OrcServiceImpl] (task-pool-9) - [e2eCallReference: ] Response received, system status NOK for SDS
 
 
2023-05-13 05:06:53.020 [INFO] [scheduling-1] orcCheck(OrcServiceImpl.java:60) - Response received, system status NOK for ROUTER
 
 
2023-05-13 05:06:38,190 INFO [com.db.gtb.bankingapi.slimits.orc.service.internal.OrcServiceImpl] (task-pool-3) - [e2eCallReference: ] Response received, system status NOK for FCS
 
 
2023-05-13 05:06:19,503 INFO [com.db.gtb.bankingapi.slimits.orc.service.internal.OrcServiceImpl] (task-pool-10) - [e2eCallReference: ] Response received, system status NOK for FCS
 
 
2023-05-13 05:06:13,067 INFO [com.db.gtb.bankingapi.slimits.orc.service.internal.OrcServiceImpl] (task-pool-2) - [e2eCallReference: ] Response received, system status NOK for EMS
 
 
2023-05-13 05:06:05.808 [INFO] [scheduling-1] orcCheck(OrcServiceImpl.java:60) - Response received, system status NOK for ROUTER
 
 
2023-05-13 05:05:43,982 INFO [com.db.gtb.bankingapi.slimits.orc.service.internal.OrcServiceImpl] (task-pool-9) - [e2eCallReference: ] Response received, system status NOK for SDS
 
 
2023-05-13 05:05:35,650 INFO [com.db.gtb.bankingapi.slimits.orc.service.internal.OrcServiceImpl] (task-pool-5) - [e2eCallReference: ] Response received, system status NOK for EMS
Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

 

| regex _raw="system status NOK for (EMS|ORC|SDS|FCS|ROUTER)|Exception occurred during (EMS|ORC|SDS|FCS|ROUTER)"
| rex "(?<status>system status NOK for (EMS|ORC|SDS|FCS|ROUTER)|Exception occurred during (EMS|ORC|SDS|FCS|ROUTER))"
0 Karma

bhaskar5428
Explorer

Many Thanks below query working as expected 

index=* ("Exception occurred during ORC" OR ("Response received, system status NOK") AND NOT "n. a." sourcetype="kube:container:*slimits*-service") namespace IN ("dk1692-e","dk1399-c","dk1371-c","dk1398-c","dk1400-d")
| eval Date=strftime(_time, "%F")
| eval Time=strftime(_time, "%T")
| regex _raw="system status NOK for (EMS|ORC|SDS|FCS|ROUTER)|Exception occurred during (EMS|ORC|SDS|FCS|ROUTER)"
| rex "(?<Status>system status NOK for (EMS|ORC|SDS|FCS|ROUTER)|Exception occurred during (EMS|ORC|SDS|FCS|ROUTER))"
|table Date, Time, Status

 

is there any option to make more formatting and presentable 

 

bhaskar5428_0-1684404351459.png

 

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This is just the return from a search - you could create a dashboard and use a table (or other viz) to present the data in a different way.

In what way do you want to change the formatting and presentation?

0 Karma
Get Updates on the Splunk Community!

Leveraging Detections from the Splunk Threat Research Team & Cisco Talos

  Now On Demand  Stay ahead of today’s evolving threats with the combined power of the Splunk Threat Research ...

New in Splunk Observability Cloud: Automated Archiving for Unused Metrics

Automated Archival is a new capability within Metrics Management; which is a robust usage & cost optimization ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...