SOLVED

Filter null results

%3CLINGO-SUB%20id%3D%22lingo-sub-225203%22%20slang%3D%22en-US%22%3EFilter%20null%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-225203%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20super%20simple%20query%20to%20pull%20MachineName%20and%20OSBuild%20versions.%3C%2FP%3E%3CP%3EI%20would%20like%20the%20query%20to%20only%20return%20machines%20where%20the%20value%20of%20OSBuild%20is%20not%20null.%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20have%20tried%20so%20far%3A%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EMachineInfo%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20OSBuild%20!%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%24null%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20ComputerName%2C%20OSBuild%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EI%20have%20tried%20explicitly%20excluding%20%22null%22%2C%20but%20it%20doesn't%20work.%20%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EI%20have%20also%20tried%20using%20a%20regular%20expression%20to%20search%20for%20any%20number%3A%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EMachineInfo%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BOSBuild%20contains%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%5B0-9%5D*%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BComputerName%2C%20OSBuild%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EThis%20generates%20an%20error.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EI%20know%20there%20is%20something%20obvious%20I%20am%20doing%20wrong.%20Just%20curious%20if%20anyone%20can%20shed%20some%20light%20on%20where%20I%20am%20going%20wrong%3F%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-225203%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-225293%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20null%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-225293%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F78468%22%20target%3D%22_blank%22%3E%40Travis%20Roberts%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20reply!%20This%20did%20the%20trick.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20didn't%20come%20across%20%22isnotnull%22%20in%20my%20search%20for%20a%20solution%2C%20although%20I%20suspect%20it%20was%20staring%20me%20in%20the%20face.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20part%20of%20the%20problem%20that%20I%20am%20dealing%20with%20a%20numerical%20field%20as%20opposed%20to%20a%20string%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20eventually%20like%20to%20turn%20this%20into%20a%20statical%20query%20where%20it%20will%20group%20all%20the%20OS%20values%20together%2C%20then%20give%20me%20a%20count%20of%20each.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-225278%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20null%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-225278%22%20slang%3D%22en-US%22%3E%3CP%3ETry%20this%3C%2FP%3E%3CP%3EMachineInfo%3C%2FP%3E%3CP%3E%7C%26nbsp%3Bwhere%20isnotnull%20(OSBuild)%3C%2FP%3E%3CP%3E%7C%26nbsp%3Bsummarize%26nbsp%3Bby%26nbsp%3BComputerName%2C%20OSBuild%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%E2%80%99t%20test%20on%20the%20demo%20site%2C%20but%20using%20the%20same%20with%20the%20heartbeat%20schema%20gives%20the%20results%20you%20may%20be%20looking%20for.%26nbsp%3B%20There%20is%20another%20command%2C%20isempty%20or%20isnotempty%20that%20does%20similar%20for%20string%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi,

 

I am working on a super simple query to pull MachineName and OSBuild versions.

I would like the query to only return machines where the value of OSBuild is not null.

Here is what I have tried so far:

MachineInfo
| where OSBuild != "$null"
| summarize by ComputerName, OSBuild
 
I have tried explicitly excluding "null", but it doesn't work.
I have also tried using a regular expression to search for any number:
MachineInfo
where OSBuild contains "[0-9]*"
summarize by ComputerName, OSBuild
This generates an error.
 
I know there is something obvious I am doing wrong. Just curious if anyone can shed some light on where I am going wrong?
2 Replies
Highlighted
Solution

Try this

MachineInfo

| where isnotnull (OSBuild)

| summarize by ComputerName, OSBuild

 

I can’t test on the demo site, but using the same with the heartbeat schema gives the results you may be looking for.  There is another command, isempty or isnotempty that does similar for string data.

Highlighted

@Travis Roberts Thank you for your reply! This did the trick.

 

I didn't come across "isnotnull" in my search for a solution, although I suspect it was staring me in the face.

 

Is part of the problem that I am dealing with a numerical field as opposed to a string?

 

I would eventually like to turn this into a statical query where it will group all the OS values together, then give me a count of each.