SOLVED

Query for Window Services State when one service is running and other one is stopped

%3CLINGO-SUB%20id%3D%22lingo-sub-837642%22%20slang%3D%22en-US%22%3EQuery%20for%20Window%20Services%20State%20when%20one%20service%20is%20running%20and%20other%20one%20is%20stopped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837642%22%20slang%3D%22en-US%22%3E%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23444444%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2015px%3B%20font-style%3A%20italic%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3EHi%2C%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23444444%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2015px%3B%20font-style%3A%20italic%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3EI%20am%20trying%20to%20write%20a%20query%20to%20get%20results%20when%20%E2%80%98Service%20A%E2%80%99%20is%20in%20running%20state%20and%20%E2%80%98Service%20B%E2%80%99%20is%20in%20stopped%20state.%20I%20am%20getting%200%20results%20when%20I%20am%20sure%20that%20it%20is%20not%20correct.%20Below%20is%20the%20query%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23444444%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2015px%3B%20font-style%3A%20italic%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3EEvent%3CBR%20style%3D%22box-sizing%3A%20border-box%3B%22%20%2F%3E%7C%20where%20EventLog%20%3D%3D%20%E2%80%98System%E2%80%99%20and%20EventID%20%3D%3D%207036%20and%20Source%20%3D%3D%20%E2%80%98Service%20Control%20Manager%E2%80%99%3CBR%20style%3D%22box-sizing%3A%20border-box%3B%22%20%2F%3E%7C%20parse%20kind%3Drelaxed%20EventData%20with%20*%20%E2%80%9D%20Windows_Service_Name%20%E2%80%9D%20Windows_Service_State%20%E2%80%9D%20*%3CBR%20style%3D%22box-sizing%3A%20border-box%3B%22%20%2F%3E%7C%20where%20Windows_Service_Name%20%3D%3D%20%E2%80%9CService%20A%E2%80%9D%20and%20Windows_Service_State%20%3D%3D%20%E2%80%9Crunning%E2%80%9D%3CBR%20style%3D%22box-sizing%3A%20border-box%3B%22%20%2F%3E%7C%20where%20Windows_Service_Name%20%3D%3D%20%E2%80%9CService%20B%E2%80%9D%20and%20Windows_Service_State%20%3D%3D%20%E2%80%9Cstopped%E2%80%9D%3CBR%20style%3D%22box-sizing%3A%20border-box%3B%22%20%2F%3E%7C%20sort%20by%20TimeGenerated%20desc%3CBR%20style%3D%22box-sizing%3A%20border-box%3B%22%20%2F%3E%7C%20project%20Computer%2C%20Windows_Service_Name%2C%20Windows_Service_State%2C%20TimeGenerated%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23444444%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2015px%3B%20font-style%3A%20italic%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23444444%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2015px%3B%20font-style%3A%20italic%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3EAppreciate%20your%20response.%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23444444%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2015px%3B%20font-style%3A%20italic%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3EThanks%20to%26nbsp%3B%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%3CA%20style%3D%22background-color%3A%20transparent%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%230077d4%3B%20text-decoration%3A%20underline%3B%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9172%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Stanislav%20Zhelyazkov%3C%2FA%3Efor%20his%20blog%20which%20helped%20me%20getting%20started%20with%20the%20query.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-837642%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-838006%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20Window%20Services%20State%20when%20one%20service%20is%20running%20and%20other%20one%20is%20stopped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-838006%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388899%22%20target%3D%22_blank%22%3E%40Ruheena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20believe%20the%20example%20query%20on%20the%20blog%20post%20is%20the%20following%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EEvent%0A%7C%20where%20EventLog%20%3D%3D%20'System'%20and%20EventID%20%3D%3D%207036%20and%20Source%20%3D%3D%20'Service%20Control%20Manager'%0A%7C%20parse%20kind%3Drelaxed%20EventData%20with%20*%20'%3CDATA%20name%3D%22param1%22%3E'%20Windows_Service_Name%20'%3C%2FDATA%3E%3CDATA%20name%3D%22param2%22%3E'%20Windows_Service_State%20'%3C%2FDATA%3E'%20*%0A%7C%20sort%20by%20TimeGenerated%20desc%0A%7C%20project%20Computer%2C%20Windows_Service_Name%2C%20Windows_Service_State%2C%20TimeGenerated%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EI%20currently%20do%20not%20have%20env%20to%20test%20the%20data%20but%20in%20order%20to%20do%20what%20you%20want%20to%20do%20you%20have%20to%20create%20two%20separate%20queries%26nbsp%3B%20and%20join%20or%20unify%20the%20results.%20I%20assume%20that%20by%20your%20request%20it%20is%20unification%20rather%20join.%20The%20query%20will%20be%20the%20following%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20ServiceARunning%20%3D%20Event%0A%7C%20where%20EventLog%20%3D%3D%20'System'%20and%20EventID%20%3D%3D%207036%20and%20Source%20%3D%3D%20'Service%20Control%20Manager'%0A%7C%20parse%20kind%3Drelaxed%20EventData%20with%20*%20'%3CDATA%20name%3D%22param1%22%3E'%20Windows_Service_Name%20'%3C%2FDATA%3E%3CDATA%20name%3D%22param2%22%3E'%20Windows_Service_State%20'%3C%2FDATA%3E'%20*%0A%7C%20where%20Windows_Service_Name%20%3D%3D%20'Service%20A'%20and%20%20Windows_Service_State%20%3D%3D%20'running'%3B%0AEvent%0A%7C%20where%20EventLog%20%3D%3D%20'System'%20and%20EventID%20%3D%3D%207036%20and%20Source%20%3D%3D%20'Service%20Control%20Manager'%0A%7C%20parse%20kind%3Drelaxed%20EventData%20with%20*%20'%3CDATA%20name%3D%22param1%22%3E'%20Windows_Service_Name%20'%3C%2FDATA%3E%3CDATA%20name%3D%22param2%22%3E'%20Windows_Service_State%20'%3C%2FDATA%3E'%20*%0A%7C%20where%20Windows_Service_Name%20%3D%3D%20'Service%20B'%20and%20%20Windows_Service_State%20%3D%3D%20'stopped'%20%7C%20union%20ServiceARunning%0A%7C%20sort%20by%20TimeGenerated%20desc%0A%7C%20project%20Computer%2C%20Windows_Service_Name%2C%20Windows_Service_State%2C%20TimeGenerated%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAdditionally%20if%20you%20have%20Change%20Tracking%20solution%20enabled%20you%20might%20want%20to%20use%20that%20as%20it%20has%20as%20low%20as%2030%20seconds%20of%20gathering%20data%20for%20stopped%2Fstarted%20services.%3C%2FP%3E%0A%3CP%3EThe%20example%20would%20be%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20SvcAStopped%20%3D%20ConfigurationChange%0A%7C%20where%20ConfigChangeType%20%3D%3D%20%22WindowsServices%22%20and%20SvcDisplayName%20%3D%3D%20'Windows%20Error%20Reporting%20Service'%20and%20SvcState%20%3D%3D%20'Stopped'%3B%0AConfigurationChange%20%0A%7C%20where%20ConfigChangeType%20%3D%3D%20%22WindowsServices%22%20and%20SvcDisplayName%20%3D%3D%20'Network%20Setup%20Service'%20and%20SvcState%20%3D%3D%20'Running'%20%0A%7C%20union%20SvcAStopped%0A%7C%20sort%20by%20TimeGenerated%20desc%0A%7C%20project%20Computer%2C%20SvcDisplayName%2C%20SvcState%2C%20TimeGenerated%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAs%20I%20have%20said%20we%20are%20doing%20union%20in%20both%20queries%20-%20basically%20just%20merging%20the%20two%20tables%20without%20having%20to%20match%20specific%20records.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20this%20answers%20your%20question.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-839434%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20Window%20Services%20State%20when%20one%20service%20is%20running%20and%20other%20one%20is%20stopped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-839434%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9172%22%20target%3D%22_blank%22%3E%40Stanislav%20Zhelyazkov%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20for%20your%20quick%20response%2C%20it%20is%20very%20helpful.%20I%20used%20'join'%20in%20the%20query%20and%20seeing%20the%20expected%20results.%3C%2FP%3E%0A%3CP%3E-Ruheena%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Microsoft

Hi,

I am trying to write a query to get results when ‘Service A’ is in running state and ‘Service B’ is in stopped state. I am getting 0 results when I am sure that it is not correct. Below is the query 

Event
| where EventLog == ‘System’ and EventID == 7036 and Source == ‘Service Control Manager’
| parse kind=relaxed EventData with * ” Windows_Service_Name ” Windows_Service_State ” *
| where Windows_Service_Name == “Service A” and Windows_Service_State == “running”
| where Windows_Service_Name == “Service B” and Windows_Service_State == “stopped”
| sort by TimeGenerated desc
| project Computer, Windows_Service_Name, Windows_Service_State, TimeGenerated

 

Appreciate your response.

Thanks to @Stanislav Zhelyazkov for his blog which helped me getting started with the query.

2 Replies
Highlighted
Solution

Hi@Ruheena 

I believe the example query on the blog post is the following:

Event
| where EventLog == 'System' and EventID == 7036 and Source == 'Service Control Manager'
| parse kind=relaxed EventData with * '<Data Name="param1">' Windows_Service_Name '</Data><Data Name="param2">' Windows_Service_State '</Data>' *
| sort by TimeGenerated desc
| project Computer, Windows_Service_Name, Windows_Service_State, TimeGenerated

I currently do not have env to test the data but in order to do what you want to do you have to create two separate queries  and join or unify the results. I assume that by your request it is unification rather join. The query will be the following:

let ServiceARunning = Event
| where EventLog == 'System' and EventID == 7036 and Source == 'Service Control Manager'
| parse kind=relaxed EventData with * '<Data Name="param1">' Windows_Service_Name '</Data><Data Name="param2">' Windows_Service_State '</Data>' *
| where Windows_Service_Name == 'Service A' and  Windows_Service_State == 'running';
Event
| where EventLog == 'System' and EventID == 7036 and Source == 'Service Control Manager'
| parse kind=relaxed EventData with * '<Data Name="param1">' Windows_Service_Name '</Data><Data Name="param2">' Windows_Service_State '</Data>' *
| where Windows_Service_Name == 'Service B' and  Windows_Service_State == 'stopped' | union ServiceARunning
| sort by TimeGenerated desc
| project Computer, Windows_Service_Name, Windows_Service_State, TimeGenerated

Additionally if you have Change Tracking solution enabled you might want to use that as it has as low as 30 seconds of gathering data for stopped/started services.

The example would be:

let SvcAStopped = ConfigurationChange
| where ConfigChangeType == "WindowsServices" and SvcDisplayName == 'Windows Error Reporting Service' and SvcState == 'Stopped';
ConfigurationChange 
| where ConfigChangeType == "WindowsServices" and SvcDisplayName == 'Network Setup Service' and SvcState == 'Running' 
| union SvcAStopped
| sort by TimeGenerated desc
| project Computer, SvcDisplayName, SvcState, TimeGenerated

As I have said we are doing union in both queries - basically just merging the two tables without having to match specific records.

 

I hope this answers your question.

 

Highlighted

Hi @Stanislav Zhelyazkov 

Thank you for your quick response, it is very helpful. I used 'join' in the query and seeing the expected results.

-Ruheena