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

     

  • Not that it would 100% fix things, but the latest version of the Onion Orion Platform is 2020.2.6 HF4 (at the time of this writing).  I know that some date/time things were remedied in that release and subsequent hotfixes, but I'm not sure if they are the ones you are running up against.

  • Many layers this Onion platform Grinning

  • I feel like there's an "Ogres are like onions" joke just waiting to happen.  You'd think after nearly 20 years of writing "Orion" countless times and far, far fewer writing "onion" my fingers would get it correct most of the time.

  • 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?

    Quick+Availability+Report.xml
    <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

Reply
  • 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

Children