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!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...