Query Log Analytics Workspace for IP

%3CLINGO-SUB%20id%3D%22lingo-sub-805466%22%20slang%3D%22en-US%22%3EQuery%20Log%20Analytics%20Workspace%20for%20IP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805466%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20query%20a%20specific%20log%20analytics%20workspace%20for%20an%20IP%20address%20.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-805466%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-806217%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20Log%20Analytics%20Workspace%20for%20IP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392442%22%20target%3D%22_blank%22%3E%40Mr_PBKAC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20do%20a%20simple%20(but%20inefficient)%20search%20-%20please%20edit%20to%20match%20your%20IP%20address.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Esearch%20%22192.168%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20know%20the%20Table%20that%20contains%20the%20data%20that%20really%20helps%3F%26nbsp%3B%20This%20next%20query%20brings%20back%20all%20the%20Tables%20that%20contain%20the%20IP%20address%2C%20if%20you%20don't%20already%20know%20them%2C%20that%20way%20we%20can%20now%20just%20search%20within%20a%20Table%20to%20improve%20the%20query%20time%20efficiency.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Esearch%20%22192.168%22%0A%7C%20summarize%20count()%20by%20Type%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20results%20of%20query%20%232%2C%20the%20Type%20Column%20list%20the%20Tables%20that%20have%20192.168*%20in%20them.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EType%3C%2FTH%3E%0A%3CTH%3Ecount_%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EWindowsFirewall%3C%2FTD%3E%0A%3CTD%3E97742%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3ENetworkMonitoring%3C%2FTD%3E%0A%3CTD%3E125670%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EAzureNetworkAnalytics_CL%3C%2FTD%3E%0A%3CTD%3E40%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20we%20know%20the%20three%20Tables%20that%20have%20that%20IP%20Address%2C%20you%20can%20just%20get%20(for%20example)%2010%20rows%20of%20data%20that%20match%20that%20IP.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EWindowsFirewall%0A%7C%20search%20%22192.168%22%0A%7C%20limit%2010%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EResults%20of%20Query%203%20(I%20just%20show%20one%20row%20for%20clarity)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20style%3D%22width%3A%203101px%3B%22%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%20style%3D%22width%3A%20130px%3B%22%3E%24table%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%20115px%3B%22%3ETenantId%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%20103.75px%3B%22%3ESourceSystem%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%20131.25px%3B%22%3EComputer%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%20105px%3B%22%3ETimeGenerated%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2051.25px%3B%22%3ECommunicationDirection%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2063.75px%3B%22%3EFirewallAction%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2048.75px%3B%22%3EProtocol%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2073.75px%3B%22%3ESourceIP%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2091.25px%3B%22%3EDestinationIP%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2091.25px%3B%22%3ERemoteIP%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3ESourcePort%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2091.25px%3B%22%3EFullDestinationAddress%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EDestinationPort%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3ERequestSizeInBytes%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EInfo%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%20115px%3B%22%3EMG%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%20126.25px%3B%22%3ETimeCollected%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%20115px%3B%22%3EManagementGroupName%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EMaliciousIP%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EIndicatorThreatType%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EDescription%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3ETLPLevel%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EConfidence%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3ESeverity%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EFirstReportedDateTime%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3ELastReportedDateTime%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EIsActive%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EReportReferenceLink%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EAdditionalInformation%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EMaliciousIPLongitude%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EMaliciousIPLatitude%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%2040px%3B%22%3EMaliciousIPCountry%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%20130px%3B%22%3EType%3C%2FTH%3E%0A%3CTH%20style%3D%22width%3A%20798.75px%3B%22%3E_ResourceId%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20style%3D%22width%3A%20130px%3B%22%3EWindowsFirewall%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%20115px%3B%22%3Eb438b4f6-912a-46d5-9cb1-b44069212abc%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%20103.75px%3B%22%3EOpsManager%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%20131.25px%3B%22%3EContosoAppSrv1%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%20105px%3B%22%3E2019-08-15T06%3A33%3A10Z%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2051.25px%3B%22%3ESEND%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2063.75px%3B%22%3EALLOW%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2048.75px%3B%22%3EICMP%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2073.75px%3B%22%3E10.6.0.28%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2091.25px%3B%22%3E192.168.1.4%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2091.25px%3B%22%3E192.168.1.4%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3Enull%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2091.25px%3B%22%3E192.168.1.4%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3Enull%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3Enull%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%20115px%3B%22%3E00000000-0000-0000-0000-000000000001%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%20126.25px%3B%22%3E2019-08-15T08%3A59%3A58.86Z%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%20115px%3B%22%3EAOI-b438b4f6-912a-46d5-9cb1-b44069212abc%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3Enull%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3Enull%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3Enull%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%2040px%3B%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%20130px%3B%22%3EWindowsFirewall%3C%2FTD%3E%0A%3CTD%20style%3D%22width%3A%20798.75px%3B%22%3E%2Fsubscriptions%2Fe4272367-5645-4c4e-9c67-3b74b59a6982%2Fresourcegroups%2Fcontosoazurehq%2Fproviders%2Fmicrosoft.compute%2Fvirtualmachines%2Fcontosoappsrv1%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20can%20see%203%20columns%20that%20have%20that%20data%2C%20you%20don't%20say%20what%20you%20want%20to%20do%20with%20it%20when%20found%2C%20maybe%20something%20like%20this%2C%20which%20filters%20on%20a%20particular%20column%20(DestinationIP)%20again%20improving%20the%20query%20execution%3F%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EWindowsFirewall%0A%7C%20where%20DestinationIP%20%20%3D%3D%20%22192.168.1.4%22%0A%7C%20summarize%20count()%20by%20Computer%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3BRun%20the%20above%20query%20from%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAAwvPzEvJLy92yyxKLU%252FMyeHlqlEoz0gtSlVwSS0uycxLLMnMz%252FMMUFCwtVVQMrQ00jM0s9Az1DNRAiksLs3NTSzKrEpVSM4vzSvR0FRIqlRwzs8tKC1JLVIAAImmW4VaAAAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820122%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20Log%20Analytics%20Workspace%20for%20IP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20for%20hyour%20help.%20The%20query%20can%20be%20simple%20and%20broad%20or%20granular.%20Also%20is%20there%20a%20default%20workspace%20associated%20withthe%20MS%20Azure%20security%20center%20widget.%20Where%20you%20can%20see%20all%20security%20alerts%20for%20your%20instance.%20Where%20are%20those%20logs%20stored%3F%20or%20is%20unique%20for%20each%20setup%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128201i2FAD426CB1328B5A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I want to query a specific log analytics workspace for an IP address . 

2 Replies
Highlighted

@Mr_PBKAC 

 

You could do a simple (but inefficient) search - please edit to match your IP address.

search "192.168"

 

If you know the Table that contains the data that really helps?  This next query brings back all the Tables that contain the IP address, if you don't already know them, that way we can now just search within a Table to improve the query time efficiency.

 

search "192.168"
| summarize count() by Type 

 

The results of query #2, the Type Column list the Tables that have 192.168* in them.

Type count_
WindowsFirewall 97742
NetworkMonitoring 125670
AzureNetworkAnalytics_CL 40

 

Now we know the three Tables that have that IP Address, you can just get (for example) 10 rows of data that match that IP.  

 

WindowsFirewall
| search "192.168"
| limit 10

 

Results of Query 3 (I just show one row for clarity):

 

$table TenantId SourceSystem Computer TimeGenerated CommunicationDirection FirewallAction Protocol SourceIP DestinationIP RemoteIP SourcePort FullDestinationAddress DestinationPort RequestSizeInBytes Info MG TimeCollected ManagementGroupName MaliciousIP IndicatorThreatType Description TLPLevel Confidence Severity FirstReportedDateTime LastReportedDateTime IsActive ReportReferenceLink AdditionalInformation MaliciousIPLongitude MaliciousIPLatitude MaliciousIPCountry Type _ResourceId
WindowsFirewall b438b4f6-912a-46d5-9cb1-b44069212abc OpsManager ContosoAppSrv1 2019-08-15T06:33:10Z SEND ALLOW ICMP 10.6.0.28 192.168.1.4 192.168.1.4 null 192.168.1.4 null null   00000000-0000-0000-0000-000000000001 2019-08-15T08:59:58.86Z AOI-b438b4f6-912a-46d5-9cb1-b44069212abc           null           null null   WindowsFirewall /subscriptions/e4272367-5645-4c4e-9c67-3b74b59a6982/resourcegroups/contosoazurehq/providers/microsoft.compute/virtualmachines/contosoappsrv1

 

I can see 3 columns that have that data, you don't say what you want to do with it when found, maybe something like this, which filters on a particular column (DestinationIP) again improving the query execution?

WindowsFirewall
| where DestinationIP  == "192.168.1.4"
| summarize count() by Computer 

 

 Run the above query from here: Go to Log Analytics and Run Query

 

Highlighted

@Clive Watson 

 

thank you for hyour help. The query can be simple and broad or granular. Also is there a default workspace associated withthe MS Azure security center widget. Where you can see all security alerts for your instance. Where are those logs stored? or is unique for each setup?

 

clipboard_image_0.png