How to create column from below output?

[bhaskar5428]

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

[bhaskar5428]

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

[ITWhisperer]

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

[bhaskar5428]

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

[ITWhisperer]

| eval date=strftime(_time, "%F")
| eval time=strftime(_time, "%T")

[ITWhisperer]

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)"

[bhaskar5428]

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

[ITWhisperer]

| 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))"

[bhaskar5428]

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 

[ITWhisperer]

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?