SOLVED

Get scalar value from table

%3CLINGO-SUB%20id%3D%22lingo-sub-107923%22%20slang%3D%22en-US%22%3EGet%20scalar%20value%20from%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-107923%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20the%20new%20OMS%20Log%20Analytics%20query%20language%2C%20how%20do%20I%20get%20a%20scalar%20value%20out%20of%20a%20table%3F%3C%2FP%3E%3CP%3EThat%20is%2C%20what%20is%20the%20equivalent%20of%20this%20TSQL%20query%2C%20presuming%20Table1%20has%20a%20single%20row%3A%3C%2FP%3E%3CPRE%3ESelect%20%40ScalarVar%20%3D%20Column1%20from%20Table1%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-107923%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-108658%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20scalar%20value%20from%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-108658%22%20slang%3D%22en-US%22%3EGreat%20example%20by%20Evgeny%20For%20some%20small%20things%20you%20do%20not%20even%20need%20scalar%20like%3A%20let%20WinComps%20%3D%20Heartbeat%20%7C%20where%20OSType%20%3D%3D%20'Windows'%20%7C%20distinct%20Computer%3B%20Perf%20%7C%20where%20Computer%20in%20(WinComps)%20%7C%20limit%2010%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-108575%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20scalar%20value%20from%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-108575%22%20slang%3D%22en-US%22%3E%3CP%3EEvgeny%2C%3C%2FP%3E%3CP%3EPerfect.%20Thanks.%20I%20knew%20there%20was%20something%20like%20that%20in%20there%20somewhere%2C%20but%20I%20was%20having%20trouble%20finding%20it%20in%20the%20language%20reference.%20I%20see%20it%20now%20under%20Scalar%20Functions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-108571%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20scalar%20value%20from%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-108571%22%20slang%3D%22en-US%22%3E%3CP%3ETim%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou're%20looking%20for%20%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FScalar-functions%2Ftoscalar()%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Etoscalar()%3C%2FA%3E.%26nbsp%3BFor%20a%20slightly%20more%20advanced%20example%20of%20how%20to%20use%20it%20(together%20with%20makeset()%20and%20in())%2C%20check%20out%20the%20last%20section%20of%20our%20%3CA%20href%3D%22Narrowing%20results%20to%20a%20set%20of%20elements%3A%20let%2C%20makeset%2C%20toscalar%2C%20in%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eaggregations%20tutorial%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20pseudocode%20your%20example%2C%20you'd%20be%20looking%20at%20something%20like%3A%3C%2FP%3E%0A%3CP%3Elet%20myDateTime%20%3D%20toscalar(Table1%20%7C%20summarize%20max(TimeGenerated))%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETable%202%3C%2FP%3E%0A%3CP%3E%7C%20where%20TimeGenerated%20between%20((myDateTime%20-%205m)%20..%20(myDateTime%20%2B%205m))%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%7C%20...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-108325%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20scalar%20value%20from%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-108325%22%20slang%3D%22en-US%22%3E%3CP%3EStanislav%2C%3C%2FP%3E%3CP%3EI%26nbsp%3Bhave%20some%20understanding%20of%20the%20let%20statement.%3C%2FP%3E%3CP%3EIn%20all%20of%20the%20examples%2C%20you%20can%20use%20a%20let%20statement%20with%20a%20query%20to%20store%20a%20table%20result%2C%20or%20with%20a%20scalar%20expression%20to%20store%20a%20scalar%20result.%3C%2FP%3E%3CP%3EHow%20do%20I%20get%2C%20store%20and%20use%20scalar%20result%20that%20comes%20from%20a%20table%20query%3F%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20need%20to%20run%20one%20query%20that%20gets%20and%20stores%20the%20datetime%20from%20Column1%20of%20Table1%20of%20a%20particular%20single%20record%2C%20so%20that%20I%20can%20use%20that%20scalar%20value%20in%20subsequent%20queries.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-108169%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20scalar%20value%20from%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-108169%22%20slang%3D%22en-US%22%3EHi%20I%20am%20not%20very%20familiar%20with%20SQL%20but%20I%20think%20you%20are%20probably%20are%20looking%20for%20let%20statement%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FQuery-statements%2FLet-statement%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FQuery-statements%2FLet-statement%3C%2FA%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Using the new OMS Log Analytics query language, how do I get a scalar value out of a table?

That is, what is the equivalent of this TSQL query, presuming Table1 has a single row:

Select @ScalarVar = Column1 from Table1

 

5 Replies
Highlighted
Hi I am not very familiar with SQL but I think you are probably are looking for let statement: https://docs.loganalytics.io/docs/Language-Reference/Query-statements/Let-statement
Highlighted

Stanislav,

I have some understanding of the let statement.

In all of the examples, you can use a let statement with a query to store a table result, or with a scalar expression to store a scalar result.

How do I get, store and use scalar result that comes from a table query?

For example, I need to run one query that gets and stores the datetime from Column1 of Table1 of a particular single record, so that I can use that scalar value in subsequent queries.

Highlighted
Solution

Tim,

 

You're looking for toscalar(). For a slightly more advanced example of how to use it (together with makeset() and in()), check out the last section of our aggregations tutorial.

 

To pseudocode your example, you'd be looking at something like:

let myDateTime = toscalar(Table1 | summarize max(TimeGenerated)); 

Table 2

| where TimeGenerated between ((myDateTime - 5m) .. (myDateTime + 5m)) 

| ...

Highlighted

Evgeny,

Perfect. Thanks. I knew there was something like that in there somewhere, but I was having trouble finding it in the language reference. I see it now under Scalar Functions.

Highlighted
Great example by Evgeny For some small things you do not even need scalar like: let WinComps = Heartbeat | where OSType == 'Windows' | distinct Computer; Perf | where Computer in (WinComps) | limit 10