Hello everyone, I am trying to join using "Table" as common field, here is my query. index=prod source=A | stats count by PROD TABLENAME_PROD Partition_Column_PROD INI_TRANS_PROD Table Column Trans | sort TABLENAME_PROD | join type=left Table [ search index=preprod source=B | stats count by CAP TABLENAME_CAP Partition_Column_CAP INI_TRANS_CAP Table Column Trans |sort TABLENAME_CAP ] | table Partition_Column_PROD Partition_Column_CAP The values that i am getting here is not matching to those if i run both commands separately and join there output manually(keeping Table as common field) I.e values of Partition_Column_CAP,Partition_Column_PROD , of this query should match with values of Partition_Column_CAP , Partition_Column_PROD which i will get if i would run these queries separately . original Output of above query Partition_Column_PROD Partition_Column_CAP (ACS_ID, ACS_ID) (ACS_ID) (ADDR_ID, ADDR_ID) (ADDR_ID) (CITY, CITY, ADDR_ID, ADDR_ID) (ADDR_ID) (ALFRESCO_MSTR_REC_ID) (ALFRESCO_MSTR_REC_ID) (APPL_ID, APPL_ID) (APPL_ID) (ACS_METHD_ID, ACS_METHD_ID) (ACS_METHD_ID) (APPL_CMPNT_ID, APPL_CMPNT_ID) (APPL_CMPNT_ID) (CMPNT_TYP_ID, CMPNT_TYP_ID) (APPL_CMPNT_ID) Expected output Partition_Column_PROD Partition_Column_CAP (ACS_ID, ACS_ID) (ACS_ID) (ADDR_ID, ADDR_ID) (ADDR_ID) (CITY, CITY, ADDR_ID, ADDR_ID) (CITY, ADDR_ID) (ALFRESCO_MSTR_REC_ID) (ALFRESCO_MSTR_REC_ID) (APPL_ID, APPL_ID) (APPL_ID) (ACS_METHD_ID, ACS_METHD_ID) (ACS_METHD_ID) (APPL_CMPNT_ID, APPL_CMPNT_ID) (APPL_CMPNT_ID) (CMPNT_TYP_ID, CMPNT_TYP_ID) (CMPNT_TYP_ID) In above results Partition_Column_PROD and Partition_Column_CAP are from both searches(search and sub search) which is joined manually. There are no repetitive values for the second part of search query ,for ex the field Partition_Column_CAP has these 3 different values (ACS_ID) , (ADDR_ID) ,(CITY, ADDR_ID) and each event has unique value now when i add this second part of search query to the join command i start seeing repetitive values for the same mentioned field mentioned above, which should not be the case ... View more

Hello everyone, In my query if my field value(Current_Day,Current_Day_Actual,Current_Day_Average,DifferenceFromAverage) is zero then i am not getting the proper output. For ex: This is the result from my query Hour_Of_Day Current_Day Current_Day_Actual Current_Day_Average DifferenceFromAverage 01 Wed 4 2 2 03 Wed 10 5 5 04 Wed 4 3 1 05 Wed 32 23 9 06 Wed 68 130 -62 For "hour_of_day"=01 i am not getting the output, could any one help me in this. this the query: index=*** eventType=*** "target{}.alternateId"=*** earliest=-4w | rename target{}.alternateId AS "id" | eval Hour_Of_Day = strftime(_time, "%H") | eval Week_Day = strftime(_time,"%a") | eval Today=strftime(now(),"%a") | eval Current_Day=if(Week_Day=Today,Today,null()) | stats count(id) AS "Total_Login" by Hour_Of_Day,Current_Day | eval DailyLogins=(Total_Login/4) | stats values(DailyLogins) AS "Current_Day_Average" by Hour_Of_Day,Current_Day | eval Current_Day_Average=ceil(Current_Day_Average) | join Hour_Of_Day [ search index=*** eventType=*** "target{}.alternateId"=**** earliest=@d | rename target{}.alternateId AS "id" | eval Hour_Of_Day = strftime(_time, "%H") | eval time_date = strftime(_time,"%w") | stats count(id) AS "Current_Day_Actual" by Hour_Of_Day, time_date | chart values(Current_Day_Actual) AS Current_Day_Actual by Hour_Of_Day | table Hour_Of_Day Current_Day_Actual] | eval DifferenceFromAverage=(Current_Day_Actual-Current_Day_Average) | table Hour_Of_Day,Current_Day,Current_Day_Actual,Current_Day_Average,DifferenceFromAverage ... View more

Hello All, I am working the below search - When I am running these two main which joined using join command are giving me intended results. But, when the searches are joined, the results from the second search doesn’t come up and yields incorrect results. This "join Hour_Of_Day" join I have highlighted in the search Could anyone help or let me know my mistake. Little description of the search, This search captures user login information from VPC oss_logging of the all the users who have successfully authenticated via OKTA. index=auto_vsc host=dfw* sourcetype=oss_logging userid | rex "userId\=(?P\w+)" | rex "userId\=\s+(?P\w+)"| rex "cuid\=(?P\w+)" | transaction userId source maxspan=1s startswith="SAMLSessionInterceptor(getLoginUser:247)" |search userId!=null| eval time1=_time | eval time=round(time1)| convert ctime(time)| eval fullname =time.".".cuid| table fullname Description: Once user is authenticated via OKTA and gathers information successfully from VPC. There are chances that iWarranty response fails(e.g., iWarranty DB is down) and this query gives failures results from iWarranty managed logs. index=auto_iwarranty sourcetype="*managed_log*" "Content-Type: application/json" | rex "ID\:\s(?P[^\s]+)\s" | rex "dealerId\"\:\"(?P\w*)" | rex "dealerBAC\"\:\"(?P\w*)" | rex "Address\:\s(?P.*)" | rex "cuid\"\:\"(?P\w*)" | rex "resultMessage\"\:\"(?P(.*?)(?=\"))" | transaction ID host requestURL maxspan=10m "Content-Type: application/json" keepevicted=true | where requestURL="https://prism-ins-osb-prod.int.ally.com:443/cps-iw-services/services/cps-businessPartner/businesspartner/dealer/" | search resultMessage="E9010: Error – unable to establish a connection to the service. If this continues, please call the NAMSC or your account manager." | eval customer=if(match(cuid, "^(?!G65UFBC9$)(?!BLINKER1$)(?!UserId00$)(?!BLINKER1$)(?!ROUTE001$)(?!Test1234$)(?!CARVANA1$)(?!CREDITIQ$)(?!SHIFT123$)([A-Z]+\d+.*)$"), "UI", "ThirdParty") | search customer="UI" | eval time=_time| convert ctime(time)| eval fullname =time.".".cuid| dedup fullname | table fullname | union [ search index=auto_iwarranty sourcetype="*managed_log*" "Content-Type: application/json" | rex "ID\:\s(?P[^\s]+)\s" | rex "dealerId\"\:\"(?P\w*)" | rex "dealerBAC\"\:\"(?P\w*)" | rex "Address\:\s(?P.*)" | rex "cuid\"\:\"(?P\w*)" | rex "resultMessage\"\:\"(?P(.*?)(?=\"))" | transaction ID host requestURL maxspan=10m "Content-Type: application/json" keepevicted=true | where requestURL="https://prism-ins-osb-prod.int.ally.com:443/cps-iw-services/services/cps-rating/dealer/programs" | search resultMessage="null: java.sql.SQLException: weblogic.jdbc.extensions.PoolDisabledSQLException: weblogic.common.resourcepool.ResourceDisabledException: Pool CLMDataSource is Suspended, cannot allocate resources to applications.." OR resultMessage="null: java.sql.SQLException: weblogic.jdbc.extensions.PoolDisabledSQLException: weblogic.common.resourcepool.ResourceDisabledException: Pool CLMDataSourceWithTimeout is Suspended, cannot allocate resources to applications.." | eval customer=if(match(cuid, "^(?!G65UFBC9$)(?!BLINKER1$)(?!UserId00$)(?!BLINKER1$)(?!ROUTE001$)(?!Test1234$)(?!CARVANA1$)(?!CREDITIQ$)(?!SHIFT123$)(?!ABCDEFGH$)([A-Z]+\d+.*)$"), "UI", "ThirdParty")|search customer="UI" | eval time1=_time | eval time=round(time1)|convert ctime(time)| eval fullname =time.".".cuid| eval fullname1 =time.".".dealer.".".cuid] | dedup fullname1 |table fullname Description: Once user is authenticated via OKTA, there are chances that VPC request fails (e.g., session issue). This query gives failure results from VPC managed logs. index=auto_vsc sourcetype="*managed_log*" IllegalStateException earliest=-4w latest=@d | rex "session\sid\s\"(?P<mysession>\w+)" | rex "invalid\:\s(?P<mysession>\w+)" | join mysession [search index=auto_vsc host=dfw* sourcetype=oss_logging earliest=-4w latest=@d | rex "sessionId\s\=\s(?P<mysession>.*?)\!" | rex "userId\s=\s(?P<userID>\w+)"| rex "cuid\=(?P<cuid>\w+)" | rex "sessionId\s\=\s(?P<mysession>\w+)"| transaction userId source maxspan=1s startswith="SAMLSessionInterceptor(getLoginUser:247)" ]| eval time=_time| convert ctime(time)|eval fullname =time.".".cuid | table fullname Below is the full search: index=auto_vsc host=dfw* sourcetype=oss_logging userid earliest=-4w latest=@d | rex "userId\=(?P\w+)" | rex "userId\=\s+(?P\w+)"| rex "cuid\=(?P\w+)" | transaction userId source maxspan=1s startswith="SAMLSessionInterceptor(getLoginUser:247)" |search userId!=null| eval time1=_time | eval time=round(time1)| convert ctime(time)| eval fullname =time.".".cuid| table fullname | append [search index=auto_iwarranty sourcetype="*managed_log*" "Content-Type: application/json" earliest=-4w latest=@d | rex "ID\:\s(?P[^\s]+)\s" | rex "dealerId\"\:\"(?P\w*)" | rex "dealerBAC\"\:\"(?P\w*)" | rex "Address\:\s(?P.*)" | rex "cuid\"\:\"(?P\w*)" | rex "resultMessage\"\:\"(?P(.*?)(?=\"))" | transaction ID host requestURL maxspan=10m "Content-Type: application/json" keepevicted=true | where requestURL="https://prism-ins-osb-prod.int.ally.com:443/cps-iw-services/services/cps-businessPartner/businesspartner/dealer/" | search resultMessage="E9010: Error – unable to establish a connection to the service. If this continues, please call the NAMSC or your account manager." | eval customer=if(match(cuid, "^(?!G65UFBC9$)(?!BLINKER1$)(?!UserId00$)(?!BLINKER1$)(?!ROUTE001$)(?!Test1234$)(?!CARVANA1$)(?!CREDITIQ$)(?!SHIFT123$)([A-Z]+\d+.*)$"), "UI", "ThirdParty") | search customer="UI" | eval time=_time| convert ctime(time)| eval fullname =time.".".cuid| dedup fullname | table fullname | union [ search index=auto_iwarranty sourcetype="*managed_log*" "Content-Type: application/json" earliest=-4w latest=@d | rex "ID\:\s(?P[^\s]+)\s" | rex "dealerId\"\:\"(?P\w*)" | rex "dealerBAC\"\:\"(?P\w*)" | rex "Address\:\s(?P.*)" | rex "cuid\"\:\"(?P\w*)" | rex "resultMessage\"\:\"(?P(.*?)(?=\"))" | transaction ID host requestURL maxspan=10m "Content-Type: application/json" keepevicted=true | where requestURL="https://prism-ins-osb-prod.int.ally.com:443/cps-iw-services/services/cps-rating/dealer/programs" | search resultMessage="null: java.sql.SQLException: weblogic.jdbc.extensions.PoolDisabledSQLException: weblogic.common.resourcepool.ResourceDisabledException: Pool CLMDataSource is Suspended, cannot allocate resources to applications.." OR resultMessage="null: java.sql.SQLException: weblogic.jdbc.extensions.PoolDisabledSQLException: weblogic.common.resourcepool.ResourceDisabledException: Pool CLMDataSourceWithTimeout is Suspended, cannot allocate resources to applications.." | eval customer=if(match(cuid, "^(?!G65UFBC9$)(?!BLINKER1$)(?!UserId00$)(?!BLINKER1$)(?!ROUTE001$)(?!Test1234$)(?!CARVANA1$)(?!CREDITIQ$)(?!SHIFT123$)(?!ABCDEFGH$)([A-Z]+\d+.*)$"), "UI", "ThirdParty")|search customer="UI" | eval time1=_time | eval time=round(time1)|convert ctime(time)| eval fullname =time.".".cuid| eval fullname1 =time.".".dealer.".".cuid] | dedup fullname1 |table fullname] | append [search index=auto_vsc sourcetype="*managed_log*" IllegalStateException earliest=-4w latest=@d | rex "session\sid\s\"(?P\w+)" | rex "invalid\:\s(?P\w+)" | join mysession [search index=auto_vsc host=dfw* sourcetype=oss_logging earliest=-4w latest=@d | rex "sessionId\s\=\s(?P.*?)\!" | rex "userId\s=\s(?P\w+)"| rex "cuid\=(?P\w+)" | rex "sessionId\s\=\s(?P\w+)"| transaction userId source maxspan=1s startswith="SAMLSessionInterceptor(getLoginUser:247)" ]| eval time=_time| convert ctime(time)|eval fullname =time.".".cuid | table fullname]| eventstats count as result by fullname | where result=1 | eval name=split(fullname, ".")| eval FieldB=mvindex(name,0)| eval FieldC=mvindex(name,1)|eval Hour_Of_Day = strftime(strptime(FieldB, "%m/%d/%Y %H:%M:%S"), "%H") |eval Week_Day = strftime(strptime(FieldB, "%m/%d/%Y %H:%M:%S"),"%a") |eval Today=strftime(now(),"%a") |eval Current_Day=if(Week_Day=Today,Today,null()) |stats count AS "Total_Login" by Hour_Of_Day,Current_Day |eval DailyLogins=(Total_Login/4) | stats values(DailyLogins) AS "Current_Day_Average" by Hour_Of_Day,Current_Day | eval Current_Day_Average=ceil(Current_Day_Average)| ***join Hour_Of_Day*** [search index=auto_vsc host=dfw* sourcetype=oss_logging userid earliest=-d@d latest=@d | rex "userId\=(?P\w+)" | rex "userId\=\s+(?P\w+)"| rex "cuid\=(?P\w+)" | transaction userId source maxspan=1s startswith="SAMLSessionInterceptor(getLoginUser:247)" |search userId!=null| eval time1=_time | eval time=round(time1)| convert ctime(time)| eval completename =time.".".cuid| table completename | append [search index=auto_iwarranty sourcetype="*managed_log*" "Content-Type: application/json" earliest=-d@d latest=@d | rex "ID\:\s(?P[^\s]+)\s" | rex "dealerId\"\:\"(?P\w*)" | rex "dealerBAC\"\:\"(?P\w*)" | rex "Address\:\s(?P.*)" | rex "cuid\"\:\"(?P\w*)" | rex "resultMessage\"\:\"(?P(.*?)(?=\"))" | transaction ID host requestURL maxspan=10m "Content-Type: application/json" keepevicted=true | where requestURL="https://prism-ins-osb-prod.int.ally.com:443/cps-iw-services/services/cps-businessPartner/businesspartner/dealer/" | search resultMessage="E9010: Error – unable to establish a connection to the service. If this continues, please call the NAMSC or your account manager." | eval customer=if(match(cuid, "^(?!G65UFBC9$)(?!BLINKER1$)(?!UserId00$)(?!BLINKER1$)(?!ROUTE001$)(?!Test1234$)(?!CARVANA1$)(?!CREDITIQ$)(?!SHIFT123$)([A-Z]+\d+.*)$"), "UI", "ThirdParty") | search customer="UI" | eval time=_time| convert ctime(time)| eval completename =time.".".cuid| dedup completename | table completename | union [ search index=auto_iwarranty sourcetype="*managed_log*" "Content-Type: application/json" earliest=-d@d latest=@d | rex "ID\:\s(?P[^\s]+)\s" | rex "dealerId\"\:\"(?P\w*)" | rex "dealerBAC\"\:\"(?P\w*)" | rex "Address\:\s(?P.*)" | rex "cuid\"\:\"(?P\w*)" | rex "resultMessage\"\:\"(?P(.*?)(?=\"))" | transaction ID host requestURL maxspan=10m "Content-Type: application/json" keepevicted=true | where requestURL="https://prism-ins-osb-prod.int.ally.com:443/cps-iw-services/services/cps-rating/dealer/programs" | search resultMessage="null: java.sql.SQLException: weblogic.jdbc.extensions.PoolDisabledSQLException: weblogic.common.resourcepool.ResourceDisabledException: Pool CLMDataSource is Suspended, cannot allocate resources to applications.." OR resultMessage="null: java.sql.SQLException: weblogic.jdbc.extensions.PoolDisabledSQLException: weblogic.common.resourcepool.ResourceDisabledException: Pool CLMDataSourceWithTimeout is Suspended, cannot allocate resources to applications.." | eval customer=if(match(cuid, "^(?!G65UFBC9$)(?!BLINKER1$)(?!UserId00$)(?!BLINKER1$)(?!ROUTE001$)(?!Test1234$)(?!CARVANA1$)(?!CREDITIQ$)(?!SHIFT123$)(?!ABCDEFGH$)([A-Z]+\d+.*)$"), "UI", "ThirdParty")|search customer="UI" | eval time1=_time | eval time=round(time1)|convert ctime(time)| eval completename =time.".".cuid| eval fullname1 =time.".".dealer.".".cuid] | dedup fullname1 |table completename] | append [search index=auto_vsc sourcetype="*managed_log*" IllegalStateException earliest=-d@d latest=@d | rex "session\sid\s\"(?P\w+)"| rex "invalid\:\s(?P\w+)"| join mysession [search index=auto_vsc host=dfw* sourcetype=oss_logging earliest=-d@d latest=@d | rex field=_raw "sessionId\s\=\s(?P.*?)\!" | rex "userId\s=\s(?P\w+)"| rex "cuid\=(?P\w+)" | rex "sessionId\s\=\s(?P\w+)"| transaction userId source maxspan=1s startswith="SAMLSessionInterceptor(getLoginUser:247)"]| eval time=_time| convert ctime(time)|eval completename =time.".".cuid | table completename]| eventstats count as result by completename | where result=1 | eval name1=split(completename, ".")| eval FieldD=mvindex(name1,0)| eval FieldE=mvindex(name1,1)| eval Hour_Of_Day = strftime(strptime(FieldD, "%m/%d/%Y %H:%M:%S"), "%H") |eval time_date = strftime(strptime(FieldD, "%m/%d/%Y %H:%M:%S"),"%w") | stats count AS "Current_Day_Actual" by Hour_Of_Day, time_date |chart values(Current_Day_Actual) AS Current_Day_Actual by Hour_Of_Day] | eval DifferenceFromAverage=(Current_Day_Actual-Current_Day_Average) | table Hour_Of_Day, Current_Day_Actual,Current_Day_Average,DifferenceFromAverage ... View more