topic Re: Require help to create query for table in Splunk Search

Require help to create query for table

Mrig342 — Wed, 03 Feb 2021 11:47:34 GMT

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

Re: Require help to create query for table

Mrig342 — Wed, 03 Feb 2021 11:52:32 GMT

One correction:

In the query use ABCEnvironment_MY instead of BPMEnvironment_SG

Re: Require help to create query for table

manjunathmeti — Wed, 03 Feb 2021 12:24:26 GMT

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.

Re: Require help to create query for table

Mrig342 — Wed, 03 Feb 2021 12:59:29 GMT

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.

Re: Require help to create query for table

manjunathmeti — Wed, 03 Feb 2021 13:05:10 GMT

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

Re: Require help to create query for table

Mrig342 — Wed, 03 Feb 2021 13:10:37 GMT

Yes manjunathmeti,

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

Please suggest.

Re: Require help to create query for table

manjunathmeti — Wed, 03 Feb 2021 13:38:13 GMT

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

Re: Require help to create query for table

Mrig342 — Wed, 03 Feb 2021 13:59:11 GMT

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.

Re: Require help to create query for table

manjunathmeti — Wed, 03 Feb 2021 14:10:12 GMT

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

Re: Require help to create query for table

Mrig342 — Wed, 03 Feb 2021 14:58:12 GMT

Thanks manjunathmeti..!!

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

Any other modification would be appreciated..!!