How to extract the below mentioned fields from the raw date to interesting fields to apt the efficient query

[Muni9066]

Hi Team,

I was trying to find out the workstations clock out of sync logs in splunk by using the below query. but I can not getting the expected logs.  Also, could not able to see the previous time field in interesting fields but it was existing in raw message field. can someone help me out how to make this query efficient...? 

index=*_win sourcetype=wineventlog EventCode=4616 category="Security State Change"
| stats max(Previous Time) by asset_id
| where isnull(lastTime)
| addinfo
| eval hourDiff=floor((info_max_time-info_min_time)/3600)
| fields dest,should_timesync,hourDiff

Thanks in advance

Previous Time: ‎2023‎-‎08‎-‎09T09:18:00.490316500Z 

[gcusello]

Hi @Muni9066 ,

after a stats command you have only the fields listed in the command itself, in your case you have only max(Previous Time) (it's always better to use the AS after a function in stats) and asset_id.

So the other used fields (lastTime, dest, should_timesync) aren't available.

Ciao.

Giuseppe

[ITWhisperer]

From what I understand, the "Previous Time" field has not been extracted from the raw logs?

If so, and you need help extracting the field, please share examples of the raw log event in a code block </> so we can assist you in extracting the field.

By the way, in order to do mathematical operations on fields, such as max(), the values should be numeric not strings, so you may need to also consider parsing the string to convert it to a numeric (strptime() function)