Forum Discussion
Use of arg_max and arg_min together
Hi,
I have a query which ultimately gives out all the info related to an IP address/ system. My current query is making use of arg_max, however I would also like to run arg_min. The final query result should capture the result of arg_max and arg_min
How do I use arg_max and arg_min together ?
SignalSciencesProd_CL
| where remoteIP_s in (watchlist_search_ip)
| extend XForwarded_json=parse_json(headersIn_s)
| mv-expand XForwarded_json
| where XForwarded_json[0] == "X-Real-IP"
| project TimeGenerated, Type, IPAddress=remoteIP_s, Instance=serverName_s,HostName=serverHostname_s, XForwardedIP=XForwarded_json[1], URL=strcat(todynamic(serverName_s),uri_s)
| summarize arg_max(TimeGenerated, *)//, arg_min(TimeGenerated, *)
Thank you !!
This is one way:
let min_=Heartbeat
| where TimeGenerated > ago(30m)
| where Computer =="JBOX10"
| summarize arg_min(TimeGenerated,ComputerIP, Computer)
| extend Title_='min'
;
let max_=Heartbeat
| where TimeGenerated > ago(30m)
| where Computer =="JBOX10"
| summarize arg_max(TimeGenerated,ComputerIP, Computer)
| extend Title_='max'
;
union min_, max_
| project TimeGenerated, ComputerIP, Computer, Title_
4 Replies
Your example would work "as is" however I'd advise you to not use "*" and select specific columns to make it more readable (you might what to rename some of the columns). e.g.
Heartbeat
| where TimeGenerated > ago(30m)
| where Computer =="JBOX10"
| summarize min_=arg_min(TimeGenerated,ComputerIP, Computer), max_=arg_max(TimeGenerated,ComputerIP, Computer)
Example using the demo data:Go to Log Analytics and run query
min_ ComputerIP Computer max_ ComputerIP1 Computer12023-11-16T11:12:24.3531826Z 20.29.245.93 JBOX10 2023-11-16T11:41:26.9028233Z 20.29.245.93 JBOX10 With renamed columns
Heartbeat
| where TimeGenerated > ago(30m)
| where Computer =="JBOX10"
| summarize min_=arg_min(TimeGenerated,ComputerIPmin=ComputerIP, Computermin=Computer),
max_=arg_max(TimeGenerated,ComputerIPmax=ComputerIP, Computermax=Computer)- Clive_Watson
Would it be possible to get the outputs of arg_min and arg_max in two separate rows as below ?
TimeGenerated ComputerIP1 Computer1
2023-11-16T11:12:24.3531826Z 20.29.245.93 JBOX10
2023-11-16T11:41:26.9028233Z 20.29.245.93 JBOX10This is one way:
let min_=Heartbeat
| where TimeGenerated > ago(30m)
| where Computer =="JBOX10"
| summarize arg_min(TimeGenerated,ComputerIP, Computer)
| extend Title_='min'
;
let max_=Heartbeat
| where TimeGenerated > ago(30m)
| where Computer =="JBOX10"
| summarize arg_max(TimeGenerated,ComputerIP, Computer)
| extend Title_='max'
;
union min_, max_
| project TimeGenerated, ComputerIP, Computer, Title_
