topic Re: Need help to create column from below output in Splunk Search

How to create column from below output?

bhaskar5428 — Thu, 18 May 2023 12:19:23 GMT

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

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

Re: Need help to create column from below output

ITWhisperer — Thu, 18 May 2023 09:03:19 GMT

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

Re: Need help to create column from below output

bhaskar5428 — Thu, 18 May 2023 09:21:35 GMT

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

Re: Need help to create column from below output

ITWhisperer — Thu, 18 May 2023 09:24:03 GMT

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

Re: Need help to create column from below output

bhaskar5428 — Thu, 18 May 2023 09:31:05 GMT

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

Re: Need help to create column from below output

ITWhisperer — Thu, 18 May 2023 09:36:36 GMT

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

Re: Need help to create column from below output

bhaskar5428 — Thu, 18 May 2023 10:06:03 GMT

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

Re: Need help to create column from below output

ITWhisperer — Thu, 18 May 2023 11:05:32 GMT

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?

Re: Need help to create column from below output

bhaskar5428 — Thu, 18 May 2023 11:18:16 GMT

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

Re: Need help to create column from below output

ITWhisperer — Thu, 18 May 2023 12:16:58 GMT

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