This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Average Availability difference in SWQL Query and Web Report Writer

Hi

I am always getting different result as compare to SWQL Query and Web Report Writer.

Mentioned is Report of Average Availability LAST MONTH.

SWQL Result:

SELECT
concat(round(avg(Availability), 2), ' %' ) as supernet
FROM Orion.ResponseTime rt
where
monthdiff(datetime,getdate())=1 and rt.Node.CustomProperties.SLA_LTE ='supernet'

Web Report Result:

Parents
  • I've tried this on three different systems I manage and none of them are showing a disconnect between a report with this query, the query itself, and a custom widget with the query.  (I did have to skip the Custom Property filter because I don't have this one defined.)

    Couple of quick questions:

    • What version are you running?
    • What time zone are you (your browser) in?
    • What time zone is the Orion web server in?
    • What time zone is the SQL database in?

    These may have nothing to do with anything, but I want to rule them all out.

    Note: There's no need to call me out directly.  I'm subscribed to this space and review forums as I have time.  I was very busy over the last two weeks with THWACKcamp 2022, so my time has been devoted elsewhere.

  • Hi

    • What version are you running?  Orion Platform HF1, NCM, IPAM, NPM, NTA: 2020.2.5
    • What time zone are you (your browser) in? GMT+0500 (Karachi Standard Time)
    • What time zone is the Orion web server in? (UTC+05:00) Islamabad, Karachi
    • What time zone is the SQL database in? (UTC+05:00) Islamabad, Karachi

     

  • any confirm solution of the issue that iam facing ?

  • I want to make sure that the custom query (within the Orion Web console) is showing "different" values as well.

    If you go to a classic dashboard (any one you like) and add a Custom Query widget, place your query in the widget, do you get a different number or does it match the one from your report?

    Note: I had to comment out the custom property filter from my query because I don't have that CP defined.

    My thoughts behind this: There's an outside chance (probably very rare) that the SWQL Studio isn't doing automatic time zone adjustments the way we thing.  I want to rule that out as a possible issue.  By the way, what version of SWQL Studio are you running?  (I don't think this matters, but it's worth a check).

    Last thing: Save your report and export it to an XML and then attach it to a reply to this thread.  I'll import it and see if things are acting the same on my side.

  • both % availability on swql studio and Custom Query widget are same but different from web report writer. i.e. 93.04

    SELECT
    round(avg(Availability), 2) as supernet
    FROM Orion.ResponseTime rt
    where
    (MONTHDIFF ([rt].ObservationTimestamp, GETDATE()) =1) and rt.Node.CustomProperties.SLA_LTE ='supernet'

    also tried in SQL but its showing other value

    other custom properties for last month have difference but of only 0.01

  • What do you get if you don't use custom SWQL for the report?

    <Report xmlns="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    	<Category>Custom</Category>
    	<Configs xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Data">
    		<a:ConfigurationData i:type="b:TableConfiguration" xmlns:b="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Tables">
    			<a:DisplaySubTitle>Filtered on Node CP.City = 'Austin'</a:DisplaySubTitle>
    			<a:DisplayTitle>My Custom Table</a:DisplayTitle>
    			<a:RefId>fb4876dc-0757-44b7-acc4-028a6ff1e9ea</a:RefId>
    			<b:Columns>
    				<b:TableColumn>
    					<b:CellStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles">
    						<c:BackgroundColor i:nil="true"/>
    						<c:Borders i:nil="true"/>
    						<c:DisplayName i:nil="true"/>
    						<c:Font i:nil="true"/>
    						<c:Padding i:nil="true"/>
    						<c:RefId>00000000-0000-0000-0000-000000000000</c:RefId>
    						<c:TextAlign>Left</c:TextAlign>
    						<c:ZebraBackgroundColor i:nil="true"/>
    					</b:CellStyle>
    					<b:DataColumnName i:nil="true"/>
    					<b:DisplayName>Timestamp</b:DisplayName>
    					<b:Field xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection">
    						<c:DataTypeInfo>
    							<a:ApplicationType i:nil="true"/>
    							<a:DataType>
    								<a:Data>System.DateTime</a:Data>
    							</a:DataType>
    							<a:DeclType>DateTime</a:DeclType>
    							<a:DefaultAggregation>NotSpecified</a:DefaultAggregation>
    							<a:DefaultDataUnitId i:nil="true"/>
    							<a:DefaultTransformId i:nil="true"/>
    							<a:Description/>
    							<a:IsFavorite>false</a:IsFavorite>
    							<a:IsFilterBy>false</a:IsFilterBy>
    							<a:IsGroupBy>false</a:IsGroupBy>
    							<a:IsInherited>true</a:IsInherited>
    							<a:IsManaged>false</a:IsManaged>
    							<a:IsStatistic>true</a:IsStatistic>
    							<a:PreviewValue>3/8/2022 8:02:41 PM</a:PreviewValue>
    							<a:Units/>
    							<a:UtcTimeIsInLocalTime>false</a:UtcTimeIsInLocalTime>
    						</c:DataTypeInfo>
    						<c:DisplayName>Timestamp</c:DisplayName>
    						<c:NavigationPath>ResponseTimeHistory</c:NavigationPath>
    						<c:OwnerDisplayName>Response Time History</c:OwnerDisplayName>
    						<c:RefID>
    							<c:Data>Orion.ResponseTime|ObservationTimestamp|ResponseTimeHistory</c:Data>
    						</c:RefID>
    					</b:Field>
    					<b:FixedWidth i:nil="true"/>
    					<b:HeaderStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles">
    						<c:BackgroundColor i:nil="true"/>
    						<c:Borders i:nil="true"/>
    						<c:DisplayName i:nil="true"/>
    						<c:Font i:nil="true"/>
    						<c:Padding i:nil="true"/>
    						<c:RefId>00000000-0000-0000-0000-000000000000</c:RefId>
    						<c:TextAlign>Left</c:TextAlign>
    						<c:ZebraBackgroundColor i:nil="true"/>
    					</b:HeaderStyle>
    					<b:IsHTMLTagsAllowed>false</b:IsHTMLTagsAllowed>
    					<b:IsHidden>true</b:IsHidden>
    					<b:PercentWidth i:nil="true"/>
    					<b:Presenters i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Presentation"/>
    					<b:PropertyName>Response Time History/Timestamp</b:PropertyName>
    					<b:RefId>a1acc318-a903-3c31-fec0-f79871db7532</b:RefId>
    					<b:Summary>
    						<b:Calculation>NotSpecified</b:Calculation>
    						<b:CellStyle i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"/>
    					</b:Summary>
    					<b:TransformId/>
    					<b:ValidRange>NotSpecified</b:ValidRange>
    				</b:TableColumn>
    				<b:TableColumn>
    					<b:CellStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles">
    						<c:BackgroundColor i:nil="true"/>
    						<c:Borders i:nil="true"/>
    						<c:DisplayName i:nil="true"/>
    						<c:Font i:nil="true"/>
    						<c:Padding i:nil="true"/>
    						<c:RefId>00000000-0000-0000-0000-000000000000</c:RefId>
    						<c:TextAlign>Left</c:TextAlign>
    						<c:ZebraBackgroundColor i:nil="true"/>
    					</b:CellStyle>
    					<b:DataColumnName i:nil="true"/>
    					<b:DisplayName>Availability</b:DisplayName>
    					<b:Field xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection">
    						<c:DataTypeInfo>
    							<a:ApplicationType>Core.Percent</a:ApplicationType>
    							<a:DataType>
    								<a:Data>System.Double</a:Data>
    							</a:DataType>
    							<a:DeclType>Float</a:DeclType>
    							<a:DefaultAggregation>Average</a:DefaultAggregation>
    							<a:DefaultDataUnitId i:nil="true"/>
    							<a:DefaultTransformId i:nil="true"/>
    							<a:Description/>
    							<a:IsFavorite>false</a:IsFavorite>
    							<a:IsFilterBy>false</a:IsFilterBy>
    							<a:IsGroupBy>false</a:IsGroupBy>
    							<a:IsInherited>false</a:IsInherited>
    							<a:IsManaged>false</a:IsManaged>
    							<a:IsStatistic>true</a:IsStatistic>
    							<a:PreviewValue>0</a:PreviewValue>
    							<a:Units/>
    							<a:UtcTimeIsInLocalTime>false</a:UtcTimeIsInLocalTime>
    						</c:DataTypeInfo>
    						<c:DisplayName>Availability</c:DisplayName>
    						<c:NavigationPath>ResponseTimeHistory</c:NavigationPath>
    						<c:OwnerDisplayName>Response Time History</c:OwnerDisplayName>
    						<c:RefID>
    							<c:Data>Orion.ResponseTime|Availability|ResponseTimeHistory</c:Data>
    						</c:RefID>
    					</b:Field>
    					<b:FixedWidth i:nil="true"/>
    					<b:HeaderStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles">
    						<c:BackgroundColor i:nil="true"/>
    						<c:Borders i:nil="true"/>
    						<c:DisplayName i:nil="true"/>
    						<c:Font i:nil="true"/>
    						<c:Padding i:nil="true"/>
    						<c:RefId>00000000-0000-0000-0000-000000000000</c:RefId>
    						<c:TextAlign>Left</c:TextAlign>
    						<c:ZebraBackgroundColor i:nil="true"/>
    					</b:HeaderStyle>
    					<b:IsHTMLTagsAllowed>false</b:IsHTMLTagsAllowed>
    					<b:IsHidden>false</b:IsHidden>
    					<b:PercentWidth i:nil="true"/>
    					<b:Presenters xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Presentation">
    						<c:PresenterRef>
    							<c:PresenterId>orion.core.text.numericformat</c:PresenterId>
    							<c:Values>
    								<a:ContextValue>
    									<a:Name>NumericFormat</a:Name>
    									<a:Value>{0:0.00} %</a:Value>
    								</a:ContextValue>
    							</c:Values>
    						</c:PresenterRef>
    					</b:Presenters>
    					<b:PropertyName>Response Time History/Availability</b:PropertyName>
    					<b:RefId>231994fb-a99e-f2e0-f63a-39b7467bf3bc</b:RefId>
    					<b:Summary>
    						<b:Calculation>Average</b:Calculation>
    						<b:CellStyle i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"/>
    					</b:Summary>
    					<b:TransformId/>
    					<b:ValidRange>NotSpecified</b:ValidRange>
    				</b:TableColumn>
    			</b:Columns>
    			<b:DefaultStyle i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"/>
    			<b:Filter xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection">
    				<c:Expression i:nil="true"/>
    				<c:Limit>
    					<c:Count i:nil="true"/>
    					<c:Mode>ShowAll</c:Mode>
    					<c:OrionServerIDsToIgnore i:nil="true"/>
    					<c:Percentage i:nil="true"/>
    				</c:Limit>
    			</b:Filter>
    			<b:Indents i:nil="true"/>
    			<b:Sorts i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"/>
    			<b:SummarizeMode>NoDataSummarization</b:SummarizeMode>
    			<b:TimeField xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection">
    				<c:DataTypeInfo i:nil="true"/>
    				<c:DisplayName i:nil="true"/>
    				<c:NavigationPath i:nil="true"/>
    				<c:OwnerDisplayName i:nil="true"/>
    				<c:RefID>
    					<c:Data>Orion.ResponseTime|ObservationTimestamp|ResponseTimeHistory</c:Data>
    				</c:RefID>
    			</b:TimeField>
    		</a:ConfigurationData>
    	</Configs>
    	<DataSources xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection">
    		<a:DataSource>
    			<a:CommandText/>
    			<a:DynamicSelectionType>Advanced</a:DynamicSelectionType>
    			<a:EntityUri xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays"/>
    			<a:Filter>
    				<a:Child>
    					<a:Expr>
    						<a:Child>
    							<a:Expr>
    								<a:Child i:nil="true"/>
    								<a:NodeType>Field</a:NodeType>
    								<a:Value>Orion.NodesCustomProperties|City|CustomProperties</a:Value>
    							</a:Expr>
    							<a:Expr>
    								<a:Child i:nil="true"/>
    								<a:NodeType>Constant</a:NodeType>
    								<a:Value>Austin</a:Value>
    							</a:Expr>
    						</a:Child>
    						<a:NodeType>Operator</a:NodeType>
    						<a:Value>=</a:Value>
    					</a:Expr>
    				</a:Child>
    				<a:NodeType>Operator</a:NodeType>
    				<a:Value>AND</a:Value>
    			</a:Filter>
    			<a:MasterEntity>orion.nodes</a:MasterEntity>
    			<a:Name>Response Time (with CP Filter)</a:Name>
    			<a:NetObjectId/>
    			<a:RefId>f2d3c31d-a36a-4af6-a79a-8afd5ebad6b5</a:RefId>
    			<a:Type>Dynamic</a:Type>
    		</a:DataSource>
    	</DataSources>
    	<Description/>
    	<Footer xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Layout">
    		<a:CustomText>© SolarWinds Worldwide, LLC. All Rights Reserved.</a:CustomText>
    		<a:ShowCustomText>true</a:ShowCustomText>
    		<a:ShowPageNumber>true</a:ShowPageNumber>
    		<a:ShowTimestamp>true</a:ShowTimestamp>
    		<a:Visible>true</a:Visible>
    	</Footer>
    	<Header xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Layout">
    		<a:Logo>standard</a:Logo>
    		<a:SubTitle/>
    		<a:Title>Quick Availability Report</a:Title>
    		<a:Visible>true</a:Visible>
    	</Header>
    	<LimitationCategory>Default Folder</LimitationCategory>
    	<ModuleTitle i:nil="true"/>
    	<Name>Quick Availability Report</Name>
    	<OrionFeatureName i:nil="true"/>
    	<PageLayout xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Layout">
    		<a:Height>0</a:Height>
    		<a:Orientation i:nil="true"/>
    		<a:PublishingType>web</a:PublishingType>
    		<a:Size i:nil="true"/>
    		<a:Units i:nil="true"/>
    		<a:Width>960</a:Width>
    	</PageLayout>
    	<ReportGuid>fc698f36-4134-42b9-8cdb-cf0e48918c10</ReportGuid>
    	<Sections xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Layout">
    		<a:Section>
    			<a:BorderStyle xmlns:b="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles">
    				<b:Bottom i:nil="true"/>
    				<b:Color i:nil="true"/>
    				<b:Left i:nil="true"/>
    				<b:Right i:nil="true"/>
    				<b:Top i:nil="true"/>
    			</a:BorderStyle>
    			<a:Columns>
    				<a:SectionColumn>
    					<a:BorderStyle xmlns:b="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles">
    						<b:Bottom i:nil="true"/>
    						<b:Color i:nil="true"/>
    						<b:Left i:nil="true"/>
    						<b:Right i:nil="true"/>
    						<b:Top i:nil="true"/>
    					</a:BorderStyle>
    					<a:Cells>
    						<a:SectionCell>
    							<a:Config i:nil="true" xmlns:b="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Data"/>
    							<a:ConfigId>fb4876dc-0757-44b7-acc4-028a6ff1e9ea</a:ConfigId>
    							<a:DataSelectionRefId>f2d3c31d-a36a-4af6-a79a-8afd5ebad6b5</a:DataSelectionRefId>
    							<a:DisplayName>Custom Table</a:DisplayName>
    							<a:RefId>59df03a5-3628-4e89-8bc6-43e1230cf5b9</a:RefId>
    							<a:RenderProvider>SolarWinds.Reporting,Table</a:RenderProvider>
    							<a:TimeframeRefId>4ab4d489-ea2c-43ec-80e4-e527867e8c9d</a:TimeframeRefId>
    						</a:SectionCell>
    					</a:Cells>
    					<a:ColumnGutter i:nil="true"/>
    					<a:PercentWidth>100</a:PercentWidth>
    					<a:PixelWidth i:nil="true"/>
    					<a:RefId>853ce5f5-079c-4758-a5b2-f5b3e73b83ab</a:RefId>
    					<a:Subtitle/>
    					<a:Title/>
    				</a:SectionColumn>
    			</a:Columns>
    			<a:RefId>94f53620-7cdc-4c7f-a9a0-3145f9f5405b</a:RefId>
    		</a:Section>
    	</Sections>
    	<TimeFrames xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Timing">
    		<a:TimeFrame>
    			<a:DisplayName>Past Hour</a:DisplayName>
    			<a:IsStatic>false</a:IsStatic>
    			<a:RefId>79758077-849f-4dbb-bd62-39da8f60d25f</a:RefId>
    			<a:Relative>
    				<a:NamedTimeFrame>PastHour</a:NamedTimeFrame>
    				<a:Unit>Hour</a:Unit>
    				<a:UnitCount>1</a:UnitCount>
    			</a:Relative>
    			<a:Static i:nil="true"/>
    		</a:TimeFrame>
    		<a:TimeFrame>
    			<a:DisplayName>Last Month</a:DisplayName>
    			<a:IsStatic>false</a:IsStatic>
    			<a:RefId>4ab4d489-ea2c-43ec-80e4-e527867e8c9d</a:RefId>
    			<a:Relative>
    				<a:NamedTimeFrame>LastMonth</a:NamedTimeFrame>
    				<a:Unit>Month</a:Unit>
    				<a:UnitCount>1</a:UnitCount>
    			</a:Relative>
    			<a:Static i:nil="true"/>
    		</a:TimeFrame>
    	</TimeFrames>
    </Report>
    You should be able to download this report and import it (hover near the top right and click on the file name to download).

    Note: You'll need to change the filter from "City = 'Austin'" to "SLA_LTE = 'supernet'"

  • Hi

    first of all thanks for supporting me in this issue.

    What do you get if you don't use custom SWQL for the report? we need to add SLA % in modern dashboard that why trying to get same result in Custom SWQL query.

    i had imported urs provided report and its showing correct Last Month availability % as my report in web report writer is showing

  • Got it - given the context that you want this in a Modern Dashboard makes me understand the request better.

    I'd open a support case: "Modern Dashboard value does not match Web Report value."  You've got all the founding information here in this thread, so you can just include a link here.

    If you find a resolution, please post it back to this thread.

  • Thanks for opening case for this issue.

    sure i will post if i can any resolution for this issue.

  • any update on this and case number

  • I believe the expectation is you open your own case number under your customer SWID.

    Either way, I think your issue is potentially caused by timezones. 

    Observationtimestamp is stored in UTC time. The web console report appears to be doing the conversion for last month automatically.

    I think your query is comparing the stored UTC timestamp against the current local datetime so potentially 5 hour shift of data.

    I'd try something like:

    MONTHDIFF(TOLOCAL(ObservationTimestamp), GETDATE() ) = 1

  • I'm sorry if I was unclear - I can't open a case on behalf of customers - I don't have your details.  Once you open a case yourself, you can reference this thread.

Reply Children