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-A12345
SERVER-A 
SERVER-A67890
SERVER-B 
SERVER-C 
SERVER-C34567

 

What I'm trying to get in a result:

  • If Computer has an ID or multiple IDs, return those rows but not the blanks
  • If Computer has ONLY blank ID, then return that row.

Expected Result:

ComputerID
SERVER-A12345
SERVER-A67890
SERVER-B 
SERVER-C34567

 

I'm sure I'm missing something simple... 

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

     

2 Replies

  • 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

     

    • ScottAllison's avatar
      ScottAllison
      Iron Contributor
      Mar 10, 2021
      This is exactly what I needed (not the datatable part, though 🙂 )!

      Thanks again as always, Clive!

Resources