Captured fields are Account, RequestorCode, Service, and ElapsedTime. An Account will have multiple RequestorCode, and a RequestorCode will have multiple Service. ElapsedTime format is "d.ddms".
Initial query
index=
| table _time Account RequestorCode Service ElapsedTime
Sample event log (Possibly populate multivalued fields)
2018-08-16 21:00:00,000 [-dd.dd.dd.ddd:dddd-d] [INFO] [ ] [info:dd.dd.dd] (info.info.info) INFO Rei21 - Service=Echo for Account=Rei, SiteID=555 ElapsedTime=20.0ms on RequestorCode=XX1 Channel=info OperatorID=Rei21 Page=info TransactionID=info
Raw sample data
Account: Rei
RequestorCode: XX1
Service: FireTV
ElapsedTime: 80.0ms
Account : Rei
RequestorCode: XX1
Service: Echo
ElapsedTime: 20.0ms
Account : Rei
RequestorCode: XX1
Service: GoogleHome
ElapsedTime: 50.0ms
Account : Cliff
RequestorCode: XX1
Service: Google Home
ElapsedTime: 20.0ms
Account : Cliff
RequestorCode: XX1
Service: HomePod
ElapsedTime: 50.0ms
Account : Cliff
RequestorCode: XX2
Service: FireTV
ElapsedTime: 70.0ms
Sample data when FireTV and Echo are combined as Amazon
Account : Rei
RequestorCode: XX1
Service: Amazon
ElapsedTime: 100.0ms
Account : Rei
RequestorCode: XX1
Service: GoogleHome
ElapsedTime: 50.0ms
Account : Cliff
RequestorCode: XX1
Service: Google Home
ElapsedTime: 20.0ms
Account : Cliff
RequestorCode: XX1
Service: HomePod
ElapsedTime: 50.0ms
Account : Cliff
RequestorCode: XX2
Service: Amazon
ElapsedTime: 70.0ms
Expected output
Account: Rei
RequestorCode: XX1
Service: Amazon
ElapsedTime: 100.0ms
Account : Cliff
RequestorCode: XX1
Service: HomePod
ElapsedTime: 50.0ms
Account : Cliff
RequestorCode: XX2
Service: Amazon
ElapsedTime: 70.0ms
Give this a try
your base search
| table _time Acount RequestorCode Service ElapsedTime
| eval Service=if(Service="FireTV" OR Service="Echo","Amazon",Service)
| stats sum(ElapsedTime) as ElapsedTime by Acount RequestorCode Service
| eventstats max(ElapsedTime) as max by Account RequestorCode
| where ElapsedTime=max
| table Acount RequestorCode Service ElapsedTime
Give this a try
your base search
| table _time Acount RequestorCode Service ElapsedTime
| eval Service=if(Service="FireTV" OR Service="Echo","Amazon",Service)
| stats sum(ElapsedTime) as ElapsedTime by Acount RequestorCode Service
| eventstats max(ElapsedTime) as max by Account RequestorCode
| where ElapsedTime=max
| table Acount RequestorCode Service ElapsedTime
This is close. Is it possible to add if statement for sum(ElapsedTime) only when Service=Amazon? Otherwise, keep the original ElapsedTime. This query is adding ElapsedTime for all the Services that's not Service=Amazon.
So for other services, if there are multiple records, you just want the max? What if there are multiple records for each of Service="FireTV" and Service="Echo", (say there are 2 entries for FireTV and 2 for Echo) in those cases should all those elapsedTime should be summed?
You're correct! For other services, there are multiple records, and I want to get the max.
For my case, there's only going to be a unique set of Service="FireTV" and Service="Echo" per Account + RequestorCode. Echo, FireTV, Google Home, and HomePod are used as a mock example.
Give this a try
your base search
| table _time Acount RequestorCode Service ElapsedTime
| eval Service=if(Service="FireTV" OR Service="Echo","Amazon",Service)
| eventstats max(ElapsedTime) as max sum(eval(if(Service="Amazon",ElapsedTime,0))) as totalAmazon by Account RequestorCode
| where ElapsedTime=max OR (Service="Amazon")
| dedup Acount RequestorCode Service
| eval ElapsedTime=if(totalAmazon>0,totalAmazon,ElapsedTime)
| table Acount RequestorCode Service ElapsedTime
This works!! Thank you!
Why do you use eventstats instead of stats for this problem? @somesoni2
The eventstats does the aggregation without changing the current resultset (it adds columns to existing results without reducing rows). This way all rows were intact and aggregation was done for rows with Service=Amazon.
Prior to that, stats was doing aggregation for all, not only for Service=Amazon, so multiple values for a Account RequestorCode were getting collapsed into one.
Also, After eventstats, there would be two rows for Service=Amazon (from original Service=FireTV and Service=Echo), so that dedup would remove one of them. The eventstats would've added sum(ElapsedTime) to both rows, so anyone will serve your purpose.
@limalbert please do accept the answer if your query is resolved. Also do not forget to up vote the comments that helped! 🙂
@limalbert your sample data and output in question do not match. Sum does not seem to be based on Account and RequestorCode. So for first example both are always 123 and XX1 for three sample data. So the final ElapsedTime should be 150. Even if you plan to take Service for correlation it will not be as per your expected output. Service Names do not match between Sample Data and Output. Please clarify.
Also what is the requirement for highest elapsed time. Please explain with Sample Data again. If you do sum by Account and RequestorCode, then highest by same fields will not do anything unless you are changing the stats aggregating field in the by clause. If you also want Service to be accounted for this you can add that to by clause in stats.
Question is updated. Thank you for the feedback.
@limalbert,
Try,
index=yourindex "other search terms"
|table _time Service Account ElapsedTime RequestorCode
|stats sum(ElapsedTime) as totalElapsedTime,max(ElapsedTime) as highestElapsedTime by Account,RequestorCode
Unfortunately, this won’t work for my case. If the data is simpler, this will work. But, there will be multiple data based on Account and RequestorCode.
The elapsedTime from the service that needs to be combined has to be added together first. The, populate the highest elapsedTime based on the account and RequestorCode.
Ok I move this to the comment section but do yo have sample data which reflects the scenario better?
Sample data is updated in the question.