topic Re: Somehow unable to fetch Time , Earlier it was working in Splunk Search

Somehow unable to fetch Time , Earlier it was working

bhaskar5428 — Thu, 04 Apr 2024 05:06:46 GMT

===========================================

Query used
index=* namespace="dk1017-j" sourcetype="kube:container:kafka-clickhouse-snapshot-writer" message="*Snapshot event published*" AND message="*dbI-LDN*" AND message="*2024-04-03*" AND message="*"
|fields message
|rex field=_raw "\s+date=(?<BusDate>\d{4}-\d{2}-\d{2})"
|rex field=_raw "sourceSystem=(?<Source>[^,]*)"
|rex field=_raw "entityType=(?<Entity>\w+)"
|rex field=_raw "\"timestamp\":\"(?<Time>\d{4}-\d{2}-\d{2}[T]\d{2}:\d{2})" -- this is not working
|sort Time desc
|dedup Entity
|table Source, BusDate, Entity, Time

===========================================

This how raw data looks like

i would like to extract only time , also suggest how can i covert to AM/PM

Kindly provide solution.

Re: Somehow unable to fetch Time , Earlier it was working

scelikok — Thu, 04 Apr 2024 05:50:25 GMT

Hi @bhaskar5428,

Your rex command seems trying to extract Time field from @timestamp field. Can you please show the raw data by clicking "Show as raw text" selection under the raw event?

Splunk shows JSON events as formatted but rex works on real text itself. We cannot compare your regex and raw data using this screen capture.

Re: Somehow unable to fetch Time , Earlier it was working

bhaskar5428 — Thu, 04 Apr 2024 06:19:44 GMT

Unable to understand solution , could you please elaborate more

I see in raw data as below
eventTimestamp=2024-04-04T02:24:52.762129638)

i would like extract time from above like = 02:24

Re: Somehow unable to fetch Time , Earlier it was working

bhaskar5428 — Thu, 04 Apr 2024 06:27:59 GMT

Also please check below query which is working , however it does not giving me required output , i need only time. in Last column

===============================================================

index=* namespace="dk1017-j" sourcetype="kube:container:kafka-clickhouse-snapshot-writer" message="*Snapshot event published*" AND message="*dbI-LDN*" AND message="*2024-04-03*" AND message="*"
|fields message
|rex field=_raw "\s+date=(?<BusDate>\d{4}-\d{2}-\d{2})"
|rex field=_raw "sourceSystem=(?<Source>[^,]*)"
|rex field=_raw "entityType=(?<Entity>\w+)"
|rex field=_raw "eventTimestamp=(?<Time>\d{4}-\d{2}-\d{2}[T]\d{2}:\d{2})" --> Need only time
|sort Time desc
|dedup Entity
|table Source, BusDate, Entity, Time

================================================================

please check screenshot for more clear understanding

Re: Somehow unable to fetch Time , Earlier it was working

PickleRick — Thu, 04 Apr 2024 06:28:57 GMT

Your command says

"\"timestamp\":\"(?<Time>\d{4}-\d{2}-\d{2}[T]\d{2}:\d{2})"

So it will match only if you have a part of your event containing (of course the timestamp is just an example)

"timestamp":"2023-01-12T14:54

Since your event is formatted differently (most significantly, the "field" you're extracting from is not named "timestamp"), you need to adjust this regex.

Use https://regex101.com for checking/verifying your ideas.

As a side note - manipulating structured data (in your case - json) with regexes might not be the best idea.

Re: Somehow unable to fetch Time , Earlier it was working

bhaskar5428 — Thu, 04 Apr 2024 06:38:27 GMT

please check the sample raw data , where i need time only

Re: Somehow unable to fetch Time , Earlier it was working

bhaskar5428 — Thu, 04 Apr 2024 06:57:04 GMT

Yes, i released that its not "timestamp " and its changes to "eventTimestamp" in raw data
However modified query but still its not working.

======================================================================

index=* namespace="dk1017-j" sourcetype="kube:container:kafka-clickhouse-snapshot-writer" message="*Snapshot event published*" AND message="*dbI-LDN*" AND message="*2024-04-03*" AND message="*"
|fields message
|rex field=_raw "\s+date=(?<BusDate>\d{4}-\d{2}-\d{2})"
|rex field=_raw "sourceSystem=(?<Source>[^,]*)"
|rex field=_raw "entityType=(?<Entity>\w+)"
|rex field=_raw "\"eventTimestamp\":\"(?<Time>\d{4}-\d{2}-\d{2}[T]\d{2}:\d{2})" --> Please suggest here
|sort Time desc
|dedup Entity
|table Source, BusDate, Entity, Time

======================================================================

attaching sample raw screenshot for your reference

Re: Somehow unable to fetch Time , Earlier it was working

scelikok — Thu, 04 Apr 2024 07:37:06 GMT

Please try below,

|rex field=_raw "\"eventTimestamp\"=\"(?<Time>\d{4}-\d{2}-\d{2}[T]\d{2}:\d{2})"

Re: Somehow unable to fetch Time , Earlier it was working

bhaskar5428 — Thu, 04 Apr 2024 08:50:50 GMT

Still not working i replaced semicolon with "=" sign
Please check screenshot.

=============================================================================

Sample raw data

Re: Somehow unable to fetch Time , Earlier it was working

PickleRick — Thu, 04 Apr 2024 09:03:35 GMT

Watch your raw event carefully. Compare it with the regex. The difference is kinda obvious.

Re: Somehow unable to fetch Time , Earlier it was working

scelikok — Thu, 04 Apr 2024 09:50:54 GMT

|rex field=_raw "eventTimestamp=(?<Time>\d{4}-\d{2}-\d{2}[T]\d{2}:\d{2})"

Re: Somehow unable to fetch Time , Earlier it was working

bhaskar5428 — Thu, 04 Apr 2024 10:20:02 GMT

My apologies

i was using "eventTimestamp" instead of "@timestamp" in my rex command

i just realized and its working now , However i do not need date in last column need only time.
Please help how to do that.

please find below details

================================================================================

Query

index=* namespace="dk1017-j" sourcetype="kube:container:kafka-clickhouse-snapshot-writer" message="*Snapshot event published*" AND message="*dbI-LDN*" AND message="*2024-04-03*" AND message="*"
|fields message
|rex field=_raw "\s+date=(?<BusDate>\d{4}-\d{2}-\d{2})"
|rex field=_raw "sourceSystem=(?<Source>[^,]*)"
|rex field=_raw "entityType=(?<Entity>\w+)"
|rex field=_raw "\"@timestamp\":\"(?<Time>\d{4}-\d{2}-\d{2}[T]\d{2}:\d{2})" --> Please help Here
|sort Time desc
|dedup Entity
|table Source, BusDate, Entity, Time

================================================================================

Screenshot

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

raw data

{"@timestamp":"2024-04-04T02:25:59.366Z","level":"INFO","message":"Snapshot event published: SnapshotEvent(version=SnapshotVersion(sourceSystem=dbI-LDN, entityType=ACCOUNT, subType=, date=2024-04-03, version=1, snapshotSize=326718, uuid=8739e273-cedc-482b-b696-48357efc8704, eventTimestamp=2024-04-04T02:24:52.762129638), status=CREATED)","thread":"snapshot-checker-3","loggerName":"com.db.sdda.dc.kafka.snapshot.writer.InternalEventSender"}

Show syntax highlighted

Re: Somehow unable to fetch Time , Earlier it was working

bhaskar5428 — Thu, 04 Apr 2024 10:20:59 GMT

My apologies

i was using "eventTimestamp" instead of "@timestamp" in my rex command

i just realized and its working now , However i do not need date in last column need only time.
Please help how to do that.

please find below details

================================================================================

Query

index=* namespace="dk1017-j" sourcetype="kube:container:kafka-clickhouse-snapshot-writer" message="*Snapshot event published*" AND message="*dbI-LDN*" AND message="*2024-04-03*" AND message="*"
|fields message
|rex field=_raw "\s+date=(?<BusDate>\d{4}-\d{2}-\d{2})"
|rex field=_raw "sourceSystem=(?<Source>[^,]*)"
|rex field=_raw "entityType=(?<Entity>\w+)"
|rex field=_raw "\"@timestamp\":\"(?<Time>\d{4}-\d{2}-\d{2}[T]\d{2}:\d{2})" --> Please help Here
|sort Time desc
|dedup Entity
|table Source, BusDate, Entity, Time

================================================================================

Screenshot

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

raw data

Show syntax highlighted

Re: Somehow unable to fetch Time , Earlier it was working

scelikok — Thu, 04 Apr 2024 13:27:46 GMT

Hi @bhaskar5428 ,

You need to change the regex capture group to cover only time, like below;

| rex field=_raw "\"@timestamp\":\"\d{4}-\d{2}-\d{2}[T](?<Time>\d{2}:\d{2})"