Need help to with query to create a table

[Mrig342]

Hi,

I have the below types of logs in in two different hosts in my index:

HOST= abc

log1: Tue Feb 2 19:07:26 EST 2021 Host Id :19804 Host Name : abcd Host Status : Running App Id :3403927 Label Name : com.abc.mx.xyz Synchronization : In Sync State : Running Number of template version : 48

log2: Tue Feb 2 19:07:26 EST 2021 Host Id :19804 Host Name : wxyz Host Status : Running App Id :27736 Label Name : com.abcde.abcdefgh Synchronization : Out of Sync State : Running Number of template version : 1

HOST= xyz

log1: 2021-02-03 02:12:49.896, APP_NAME="com.abc.mx.xyz", APP_TEMP_NAME="com.abc.mx.xyz-1", APP_TEMP_VER="1.1.5", LASTDEPLOYED="2019-09-24 13:38:05.047", ENV_NAME="ABCEnvironment_MY"

log2: 2021-02-03 02:12:49.896, APP_NAME="com.abcde.abcdefgh", APP_TEMP_NAME="com.abcde.abcdefgh", APP_TEMP_VER="3.1.0.20201126030342320", LASTDEPLOYED="2020-11-27 13:01:49.959", ENV_NAME="ABCEnvironment_AU"

Here want to create a table as below with fields from both the hosts:

App_Name                          Sync_State          Last_Deployed                             Temp_Version

com.abc.mx.xyz                 In Sync                   2019-09-24 13:38:05.047        1.1.5

com.abcde.abcdefgh       Out of Sync          2020-11-27 13:01:49.959         3.1.0.20201126030342320

and so on...

I created this query to create the table:

index=main host IN(abc,xyz) | rex field=_raw "(?ms)Label\s+Name\s:\s(?<App_Name>\w+\S+)" | rex field=_raw "(?ms)Synchronization\s:\s(?<Sync_State>[\w\s]+Sync)\sState" | rex field=_raw "(?ms)LASTDEPLOYED=\"(?P<Last_Deployed>[^\"]+)" | rex field=_raw "(?ms)APP_TEMP_VER=\"(?P<Temp_Version>[^\"]+)" | stats values(Sync_State) as Sync_State by App_Name | mvexpand Sync_State | table App_Name,Sync_State,Last_Deployed,Temp_Version

But I am failing to get the desired output as with this query the "Last_Deployed" and "Temp_Version" fields come empty.

Can someone please help me in creating the right query to create the table in desired manner..

[ITWhisperer]

You need to include an extraction of APP_NAME into App_Name from the second of events (host xyz) and you need to include Last_Deployed and Temp_Version in the stats command (otherwise, it gets removed from the pipeline)

index=main host IN(abc,xyz) 
| rex field=_raw "(?ms)Label\s+Name\s:\s(?<App_Name>\w+\S+)" 
| rex field=_raw "(?ms)APP_NAME=\"(?P<App_Name>[^\"]+)"
| rex field=_raw "(?ms)Synchronization\s:\s(?<Sync_State>[\w\s]+Sync)\sState" 
| rex field=_raw "(?ms)LASTDEPLOYED=\"(?P<Last_Deployed>[^\"]+)" 
| rex field=_raw "(?ms)APP_TEMP_VER=\"(?P<Temp_Version>[^\"]+)" 
| stats values(Sync_State) as Sync_State values(Last_Deployed) as Last_Deployed  values(Temp_Version) as Temp_Version by App_Name 
| mvexpand Sync_State 
| table App_Name,Sync_State,Last_Deployed,Temp_Version

[Mrig342]

Hi ITWhisperer,

Thank you for your prompt response.

The query seems to be fine however it is not coming out in the table as expected. The table that forms out of the query is:

App_Name                          Sync_State          Last_Deployed                             Temp_Version

com.abc.mx.xyz                 In Sync                  2018-08-21 07:59:59.653        1.0.10.20190510004347647

                                                                                   2019-02-06 12:49:23.062        1.0.11.20210120114351539   
                                                                                   2021-01-27 12:25:34.845        1.0.7.20180816080749984
                                                                                   2021-01-27 13:17:30.922
                                                                                   2021-01-27 13:41:12.389
                                                                                   2021-01-27 14:09:38.631

com.abcde.abcdefgh       Out of Sync        2018-08-21 07:06:54.036        3.1.0.20180730083057277

                                                                                  2018-11-18 05:53:26.057         3.1.0.20181025094824195
                                                                                  2020-11-27 12:25:29.846         3.1.0.20201126030342320
                                                                                  2020-11-27 12:46:02.675
                                                                                  2020-11-27 12:53:16.905
                                                                                  2020-11-27 13:01:49.959

and so on..

While my expectation is to create the table with the latest "Last_Deployed" and latest "Temp_Version" as below:

App_Name                          Sync_State          Last_Deployed                             Temp_Version

com.abc.mx.xyz                 In Sync                  2021-01-27 14:09:38.631          1.0.11.20210120114351539

com.abcde.abcdefgh       Out of Sync         2020-11-27 13:01:49.959          3.1.0.20201126030342320

and so on...

Can you please help me to get the table in the desired way..

[ITWhisperer · Accepted solution]

index=main host IN(abc,xyz) 
| rex field=_raw "(?ms)Label\s+Name\s:\s(?<App_Name>\w+\S+)" 
| rex field=_raw "(?ms)APP_NAME=\"(?P<App_Name>[^\"]+)"
| rex field=_raw "(?ms)Synchronization\s:\s(?<Sync_State>[\w\s]+Sync)\sState" 
| rex field=_raw "(?ms)LASTDEPLOYED=\"(?P<Last_Deployed>[^\"]+)" 
| rex field=_raw "(?ms)APP_TEMP_VER=\"(?P<Temp_Version>[^\"]+)" 
| stats values(Sync_State) as Sync_State latest(Last_Deployed) as Last_Deployed  latest(Temp_Version) as Temp_Version by App_Name 
| mvexpand Sync_State 
| table App_Name,Sync_State,Last_Deployed,Temp_Version

[Mrig342]

Thank you ITWhisperer...!!

The query has worked. But I am seeing that if I want to minimize the search to a particular environment then I am not getting the Sync_State in the table.

I have used the below query:

index=int_gcg_apac_solace_166076 host IN(mwgtc-dbcld23u-scan,mwgtc-tiblc36u.nam.nsroot.net)
| rex field=_raw "(?ms)Host\s+Name\s:\s(?<Host_Name>\w+)"
| rex field=_raw "(?ms)Label\s+Name\s:\s(?<App_Name>\w+\S+)"
| rex field=_raw "(?ms)APP_NAME=\"(?P<App_Name>[^\"]+)"
| rex field=_raw "(?ms)Synchronization\s:\s(?<Sync_State>[\w\s]+Sync)\sState"
| rex field=_raw "(?ms)LASTDEPLOYED=\"(?P<Last_Deployed>[^\"]+)"
| rex field=_raw "(?ms)APP_TEMP_VER=\"(?P<Temp_Version>[^\"]+)"
| rex field=_raw "(?ms)ENV_NAME=\"(?P<ENV_NAME>[^\"]+)"
| search ENV_NAME=BPMEnvironment_MY
| stats values(Sync_State) as Sync_State latest(Last_Deployed) as Last_Deployed latest(Temp_Version) as Temp_Version by App_Name
| mvexpand Sync_State
| table App_Name,Sync_State,Last_Deployed,Temp_Version

Can you please help me edit the query to fulfill my expectation..