Hello, I was using the round function in my search to limit the results to 2 decimal places. I have gotten it to work for some numbers, but there are a few numbers that do not work, and typically display repeating decimals. I was wondering if there was a way to rectify this or possibly convert my functions into strings and limit the results to 2 decimal places that way?
Greetings @harshparikhxlrd,
You are rounding in this line: | eval dur = round(((hh * 3600) + (mm * 60) + ss),2)
, but then you take another average on this line: | stats avg(dur) as "Average Duration" by log, strr
which will sometimes give repeating decimals.
You just need to round after the last average instead of before it, so your query should look more like this:
index=monitoring sourcetype=PEGA:WinEventLog:Application (SourceName="RoboticLogging" OR SourceName="Application") (Type= "Information") ("TaskID=\"Logs\"")
| rex "Automation=\"(?<Auto>.+?)\""
| where Auto = "SDA_E_AuditLog"
| rex "Message=\"(?<log>.+?)\""
| rex "Duration:(?<hh>\d+):(?<mm>\d+):(?<ss>\d+\.\d+)"
| eval dur = (hh * 3600) + (mm * 60) + ss
| rex "UserID=\"UNTOPR\\\(?<UID>.+?)\""
| eval User = host." : ".UID
| stats avg(dur) as "Average Duration" by log, User
| eval "Average Duration" = round('Average Duration',2)
| stats list(log) as Automation list("Average Duration") as "Average Duration (seconds)" by User
| table User Automation "Average Duration (seconds)"
Cheers,
Jacob
Greetings @harshparikhxlrd,
You are rounding in this line: | eval dur = round(((hh * 3600) + (mm * 60) + ss),2)
, but then you take another average on this line: | stats avg(dur) as "Average Duration" by log, strr
which will sometimes give repeating decimals.
You just need to round after the last average instead of before it, so your query should look more like this:
index=monitoring sourcetype=PEGA:WinEventLog:Application (SourceName="RoboticLogging" OR SourceName="Application") (Type= "Information") ("TaskID=\"Logs\"")
| rex "Automation=\"(?<Auto>.+?)\""
| where Auto = "SDA_E_AuditLog"
| rex "Message=\"(?<log>.+?)\""
| rex "Duration:(?<hh>\d+):(?<mm>\d+):(?<ss>\d+\.\d+)"
| eval dur = (hh * 3600) + (mm * 60) + ss
| rex "UserID=\"UNTOPR\\\(?<UID>.+?)\""
| eval User = host." : ".UID
| stats avg(dur) as "Average Duration" by log, User
| eval "Average Duration" = round('Average Duration',2)
| stats list(log) as Automation list("Average Duration") as "Average Duration (seconds)" by User
| table User Automation "Average Duration (seconds)"
Cheers,
Jacob