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?

    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

  • 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