Forum Discussion

ScottAllison's avatar
ScottAllison
Iron Contributor
Mar 10, 2021
Solved

Need help with a query with multiple if/thens

So I'm having a hard time coming up with a query that will get me the intended results. Any help would be appreciated.   My data looks like this: Computer ID SERVER-A 12345 SERVER-A  ...
azure monitor
Query Language
  • CliveWatson's avatar
    CliveWatson
    Mar 10, 2021

    ScottAllison 
    I'm not sure if I missed the 'simple' as well, but this works, using the datatable!

    datatable (Computer:string,ID:string)
[
'SERVER-A','12345',
'SERVER-A','',
'SERVER-A','67890',
'SERVER-B','',	 
'SERVER-C','', 
'SERVER-C','34567'
]
| summarize countif(toreal(ID) > 1), make_set(Computer),make_set_if(ID, isnotempty(ID)) by Computer 
| summarize by Computer, tostring(set_ID)
| extend set_ID = iif(set_ID == '[]',' ',set_ID)
| mv-expand ID=todynamic(set_ID) to typeof(string)
| project-away set_ID

     

CliveWatson's avatar
CliveWatson
Former Employee
Mar 10, 2021

ScottAllison 
I'm not sure if I missed the 'simple' as well, but this works, using the datatable!

datatable (Computer:string,ID:string)
[
'SERVER-A','12345',
'SERVER-A','',
'SERVER-A','67890',
'SERVER-B','',	 
'SERVER-C','', 
'SERVER-C','34567'
]
| summarize countif(toreal(ID) > 1), make_set(Computer),make_set_if(ID, isnotempty(ID)) by Computer 
| summarize by Computer, tostring(set_ID)
| extend set_ID = iif(set_ID == '[]',' ',set_ID)
| mv-expand ID=todynamic(set_ID) to typeof(string)
| project-away set_ID

 

Resources