How to extract the daily value from a log in JSON format and create table?

[shirsho13]

Hi, I have a Splunk log which logs messages in the following JSON format - 

   @timestamp: 2020-08-28T11:24:27.289-04:00
   @version: 1
   ACTIVE_PROFILE: prod-east
   HOSTNAME: XXXXXXXX
   appName: autopay-instance-fulfillment
   level: INFO
   level_value: 20000
   logger_name: abc.xyz.AccountServiceImpl
   message: Number of records retrieved from accounts table : 67
   thread_name: pool-7-thread-1

I want to extract the value that shows up in the message (after "The number of records retrieved") as a number every day and create a table using those values. Could I please have some guidance regarding that? 

Thank you in advance!

[Nisha18789]

Hello @shirsho13 , if you are looking for the record count data to be shown in a table with timestamp, you can use. You can add additional fields in the table commadn which you want to see in the result table.

... | rex "Number of records retrieved from accounts table : (?<count>\d+"

|table _time, count

In case you want this count to be summed up per day, you can set up a report/alert to run below query at the end of everyday and add the values to lookup:

... | rex "Number of records retrieved from accounts table : (?<count>\d+"

| eval date=strftime(_time,"%Y-%m-%d")

| stats sum(count) as records_count by date | outputlookup  recods_data_master.csv append=true

Hope this helps!

[richgalloway]

Assuming you're already extracting the timestamp from the events, this search should do the job.

... | rex "Number of records retrieved from accounts table : (?<recordCount>\d+"
| stats sum(recordCount) as records by HOSTNAME