topic Re: Need help to with query to create a table in Splunk Search

Need help to with query to create a table

Mrig342 — Wed, 03 Feb 2021 07:52:15 GMT

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

Re: Need help to with query to create a table

ITWhisperer — Wed, 03 Feb 2021 08:10:18 GMT

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

Re: Need help to with query to create a table

Mrig342 — Wed, 03 Feb 2021 09:38:43 GMT

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

Re: Need help to with query to create a table

ITWhisperer — Wed, 03 Feb 2021 10:29:38 GMT

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

Re: Need help to with query to create a table

Mrig342 — Wed, 03 Feb 2021 11:06:12 GMT

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