Splunk Search Query

[Awanish1212]

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.

[gcusello]

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