Require help to create query for table

[Mrig342]

Hi All,

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..

Using the below query I am able to get the table:

index=main host IN(abc,xyz)
| 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>[^\"]+)"
| 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

However when I try to minimize the search to a particular environment using the below query the "Sync_State" field goes blank.

index=main host IN(abc,xyz)
| 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_SG
| 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 someone please help me edit the query to fulfill my expectation..

[manjunathmeti]

hi @Mrig342 ,

Fields ENV_NAME and Sync_State are not there in the same log (OR same row after extracting fields).  So when you filter results on the field ENV_NAME, the field Sync_State is gone. Just run without stats command and check.

index=main host IN(abc,xyz)
| 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_SG

So, you need to filter the field ENV_NAME after the stats command.

index=main host IN(abc,xyz)
| 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>[^\"]+)" 
| fields App_Name, Sync_State, Last_Deployed, Temp_Version, ENV_NAME
| stats latest(*) as * by App_Name 
| search ENV_NAME= ABCEnvironment_MY
| table App_Name, Sync_State,Last_Deployed, Temp_Version

If this reply helps you, an upvote/like would be appreciated.

[Mrig342]

Hi manjunathmeti,

Thank you for your response..!!

The query is working only for ENV_NAME=ABCEnvironment_MY. While for other environments such as ABCEnvironment_SG,ABCEnvironment_CN,ABCEnvironment_AU etc it shows as no results found.

Please suggest on this..

Thank you.

[manjunathmeti]

Remove | search ENV_NAME="ABCEnvironment_MY" from search query see ifENV_NAME contains other values.

[Mrig342]

Yes manjunathmeti,

After removing | search ENV_NAME= ABCEnvironment_MY, I can see the values in ENV_NAME field.

Please suggest.

[manjunathmeti]

It is working with the sample data you provided, check this query:

| makeresults 
| eval _raw="_raw
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
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
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\"
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\"" 
| multikv forceheader=1 
| 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>[^\"]+)" 
| fields App_Name, Sync_State, Last_Deployed, Temp_Version, ENV_NAME
| stats latest(*) as * by App_Name 
| search ENV_NAME="ABCEnvironment_AU"
| table App_Name, Sync_State,Last_Deployed, Temp_Version, ENV_NAME

[Mrig342]

Hi manjunathmeti,

The query works fine for the sample logs that I provided. However I will be having thousands of such logs and it's not possible to add all those logs in the search query with eval command.

Please help me in creating the query to meet my expectations.

Thank you.

NOTE: We have about 7 to 10 application in each environment. We are trying to create a table of the logs from the two hosts which will contain the App_Name,Sync_State,Last_Deployed and Temp_Version for each environment.

[manjunathmeti]

Then, the below query should work for you:

| 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>[^\"]+)" 
| fields App_Name, Sync_State, Last_Deployed, Temp_Version, ENV_NAME
| stats values(*) as * by App_Name

[Mrig342]

Thanks manjunathmeti..!!

But this query didn't work either. I am not getting the output as desired. 

Any other modification would be appreciated..!!

[Mrig342]

One correction:

In the query use ABCEnvironment_MY instead of BPMEnvironment_SG