Splunk Search

Splunk Search Query

Awanish1212
Explorer

Suppose there are 10 events as "raw text" in Splunk in last 7 days as below :

Event 1 : 7/11/23 5:28:33.265 PM

"host":"111.123.23.34","level":1,"msg":"cricket score : 10","time":"2023-07-11T17:28:33.265Z"

Event 2 : 7/11/23 6:28:33.265 PM

"host":"111.123.23.34","level":2,"msg":"cricket score : 20","time":"2023-07-11T18:28:33.265Z"

Event 3 : 7/12/23 5:28:33.265 PM

"host":"111.123.23.34","level":3,"msg":"cricket score : 30","time":"2023-07-12T17:28:33.265Z"

Event 4 : 7/12/23 6:28:33.265 PM

"host":"111.123.23.34","level":4,"msg":"cricket score : 40","time":"2023-07-12T18:28:33.265Z"

Event 5 : 7/13/23 5:28:33.265 PM

"host":"111.123.23.34","level"5,"msg":"cricket score : 50","time":"2023-07-13T17:28:33.265Z"

Event 6 : 7/13/23 6:28:33.265 PM

"host":"111.123.23.34","level":1,"msg":"cricket score : 10","time":"2023-07-13T18:28:33.265Z"

Event 7 : 7/14/23 5:28:33.265 PM

"host":"111.123.23.34","level":2,"msg":"cricket score : 20","time":"2023-07-14T17:28:33.265Z"

Event 8 : 7/14/23 6:28:33.265 PM

"host":"111.123.23.34","level":3,"msg":"cricket score : 30","time":"2023-07-14T18:28:33.265Z"

Event 9 : 7/15/23 5:28:33.265 PM

"host":"111.123.23.34","level":4,"msg":"cricket score : 40","time":"2023-07-15T17:28:33.265Z"

Event 10 : 7/15/23 6:28:33.265 PM

"host":"111.123.23.34","level"5,"msg":"cricket score : 50","time":"2023-07-15T16:28:33.265Z"

So I need to create a Splunk query to get output as below in table format.

Date -  Sum of cricket Score on that particular date

Date - Total Cricket Score

2023-07-11 - 30

2023-07-12 - 70

2023-07-13 - 60

2023-07-14 - 50

2023-07-15 - 90

Request your help for the same.

Labels (5)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Awanish1212 ,

if you didn't do, you have to extract fields using the following regex:

| rex "\"cricket score : (?<cricket_score>[^\"]+)\",\"time\":\"(?<time>[^\"]+)"

that you can test at https://regex101.com/r/L1lFzb/1

then you have to extract the day from the time field and you can do this using strptime or substr:

at the end, you should try a search like the following:

<your_search>
| rex "\"cricket score : (?<cricket_score>[^\"]+)\",\"time\":\"(?<time>[^\"]+)"
| eval day=substr(time,1,10)
| stats sum(cricket_score) AS total BY day

Ciao.

Giuseppe

 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...