locked
What SQL table on the OperationsManagerDW hold the Server Disk usuage RRS feed

  • Question

  • Hello,

    I am in the middle of creating two sql queries to run on the SCOM OperationsManagerDW  database.

    1. Free disk space on a server and just of volume to check over the past 100 days.

    (i have done this)

    2. Disk usage on a server and just one volume over the past 100 days.

    The issue is I can not find a Tabel with the data for me to retrieve  this from.

    For the free space I am using the following

    use OperationsManagerDW
    select  Path, InstanceName as "Volume", cast(datetime as date) as "Date",
    round(Avg(case  when countername = 'Free Megabytes' then AverageValue end),2) As "Free Megabytes", 
    isnull(cast (((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' and Averagevalue!=0 then AverageValue end))*100)/1024 as decimal(10,2)),0) as "NOT TO BE USED Allocated GB"

    from OperationsManagerDW.Perf.vPerfDaily pvpr 
    inner join OperationsManagerDW.dbo.vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId
    = vpri.PerformanceRuleInstanceRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 

    Can someone help me out ?

    Thanks

    Friday, February 16, 2018 12:50 AM

Answers

  • Ohhhh......
    Got it , you want MB and % for a particular day with the total allotted size.

    Here we go:-

    select  Path, FullName, InstanceName, cast(datetime as date),
    Avg(case when countername = '% Free Space' then AverageValue end) As "% Free Space", 
    Avg(case when countername = 'Free Megabytes' then AverageValue end) As "Free Megabytes",


    --(((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' then AverageValue end))*100)/1024) as "Allocated GB"
    cast (((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' and Averagevalue!=0 then AverageValue end))*100)/1024 as decimal(10,2)) as "Allocated GB"


    from OperationsManagerDW.Perf.vPerfdaily pvpr 
    inner join OperationsManagerDW.dbo.vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId
    = vpri.PerformanceRuleInstanceRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 
    WHERE (CounterName Like '% Free Space' or 
                  CounterName Like 'Free Megabytes' and
                  InstanceName like 'C:' )
    and ObjectName like '%logicalDisk%' 
    --and DateTime >= getdate()-2
    --and DateTime <=''2014-06-17 05:22:41.000''
    --and FullName like ''''
    group by  Path, FullName, InstanceName, Cast(datetime as Date)
    --order by '% free space' ,Cast(datetime as Date)


    Run this on DW and you will get information about C: drive....likewise change this to D: or E: or etc. and for Linux change  logicalDisk to Logical Disk.

    Note:- It will give report for all windows server but for one you need to edit this line --and FullName like '''' and you need to give whole path as i have given like this

    and FullName like 'Microsoft.Windows.Server.6.2.LogicalDisk:serverfqdn;C:' and dont forget to remove -- bar from this line if you need output for one server only. 

    And you can also customize dates as per your requirements. 

    --and DateTime >= getdate()-2
    --and DateTime <=''2014-06-17 05:22:41.000''

    again if you are customizing it , please dont forget to remove -- from these lines otherwise it will mark as comment and will not execute.

    The output would be like below:- 

    Hope you have got now, what you are looking for.....

    Please mark as answer if it helps...


    Cheers, Gourav (Please do take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) or find my Facebook and LinkedIn link in profile.

    • Proposed as answer by GouravIN Monday, February 19, 2018 5:19 AM
    • Marked as answer by Goce_D Monday, February 19, 2018 9:31 PM
    Monday, February 19, 2018 5:09 AM

All replies

  • Hello,

    Use below query on DW database:

    select vManagedEntity.Path
     ,vManagedEntity.Name
     ,Perf.vperfDaily.Averagevalue
     ,Perf.vperfDaily.MinValue
     ,Perf.vperfDaily.MaxValue
      ,vPerformanceRule.ObjectName
     ,vPerformanceRule.CounterName
     ,Perf.vperfDaily.DateTime
     from Perf.vperfDaily
     join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vperfDaily.PerformanceRuleInstanceRowid
     join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
     join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vperfDaily.ManagedEntityRowId
     join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
     where --path like '%servername%' and
     Perf.vperfDaily.Datetime between '2018-02-13' and '2018-02-15'
     and vPerformanceRule.ObjectName like '%LogicalDisk%'
     and vPerformanceRule.CounterName like '%Free Space%'
     --and vRule.RuleDefaultName like '%Processor Information % Processor Time Total%'
     order by DateTime desc

    You can change data aggregation from daily to hourly i.e Perf.vperfdaily with Perf.vPerfhourly if required.

    https://social.technet.microsoft.com/Forums/systemcenter/en-US/42c6cad0-074b-4e98-956f-339261f7a405/get-scom-performance-report-listing-minmax-avg-and-std-dev-values-for-all-the-windwos-servers-for?forum=operationsmanagerauthoring#f8fbd2c6-ea9d-426c-bce6-73569b66e2a7

    (Please "Mark as Answer/Vote as Helpful" if applicable)

    Friday, February 16, 2018 3:01 AM
  • Hi 

    >>>2. Disk usage on a server and just one volume over the past 100 days.<<<

    You need One disk information of One server, no issues run the below query on DW

     

     select
     vManagedEntity.Path
     ,Perf.vperfdaily.DateTime
     ,vPerformanceRule.ObjectName
     ,vPerformanceRule.CounterName
     ,vManagedEntity.Name
     ,Perf.vperfdaily.Averagevalue
     ,Perf.vperfdaily.MinValue
     ,Perf.vperfdaily.MaxValue
     ,vRule.RuleDefaultName
     from Perf.vperfdaily
     join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vperfdaily.PerformanceRuleInstanceRowid

    join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
     join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vperfdaily.ManagedEntityRowId
     join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
     where vManagedEntity.Path in ('server.fqdn.com') and Perf.vperfdaily.Datetime >= DATEADD(Day, -100, GETDATE())
     and vPerformanceRule.ObjectName like 'LogicalDisk'
     and vPerformanceRule.CounterName = 'Free Megabytes'
     and vManagedEntity.Name = 'C:'
     order by DateTime desc

    You just need to given server fqdn inside '' and you can close change the instance C: or D: and so on. Customize daily report to hourly by changing Perf.vperfdaily in to Perf.vperfhourly  


    Cheers, Gourav (Please do take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) or find my Facebook and LinkedIn link in profile.



    • Edited by GouravIN Friday, February 16, 2018 3:46 AM
    Friday, February 16, 2018 3:42 AM
  • Thank you for sql query you sent me.  The only thing that I still need is to find the total disk usage instead of Free Megabytes.  

    I went to the vManagedEntity but unable to see a section for total disk size .

    Like I know I would have to use a formuula like total disk size - free megabytes = total disk used.

    How can I use the above script to help me find the solution ?

    thanks

    Sunday, February 18, 2018 8:38 PM
  • Hi,

    vManagedEntity is a generic "root" table for any class types, so it doesn't contain any class-specific values. Please refer to vManagedEntityPropertyValueHistory and select the most recent value for your ManagedEntityRowId and PropertySystemName = 'Size'

    Sunday, February 18, 2018 10:48 PM
  • Hi Max,

    Sorry I dont understand the " vManagedEntityPropertyValueHistory" , (i am not a sql person). like when I look at the tabels or views on the OperationManager DW I can not see any value call  vManagedEntityPropertyValueHistory.  There are a lot of Managedentyproperty / Monitor/ type ....

    Monday, February 19, 2018 12:01 AM
  • I am looking at this query and if there is a  " CounterName = 'Free Megabytes'"

    then it must some how know the "disk size" of the volume to calculate 'Free Megabytes'

    like how hard is it to get this data out of the OpeartionsManager DW for a server and a volume for each day for the past like 30 days ?

    Monday, February 19, 2018 2:07 AM
  • Goce, could you please explain more that what are looking for in a report. 
    one by one, so i can help you further. 

    Cheers, Gourav (Please do take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) or find my Facebook and LinkedIn link in profile.

    Monday, February 19, 2018 3:44 AM
  • Hello GouravIN



    Look at the below script.  It only works on the OM database. I can select a server and the volume. It give me the disk size of the volume on that server.  What I want to try and work out is how can use this aswell as the CounterName = 'Free Megabytes'  to calulate the current disk usuage of for server.FQDN , volume C for everyday for the past 30days.


     SELECT  [PrincipalName] 

          ,[Description_73629F4A_6999_08C0_A5CC_D5A6AC1BFC87]
          ,[DeviceID_DF2FF114_783D_E8EC_DC76_0FC98EF70DB4]
      ,[SizeInMBs_E70DED5C_3AE4_BDCD_1635_F40D15E608B7]
          ,[DriveType_67A23C3E_F435_A1B1_DE3E_D8C412E6D9D5]


      FROM [OperationsManager].[dbo].[MTV_Microsoft$Windows$Server$6$2$LogicalDisk]
      where PrincipalName = 'server.FQDN' AND

      DeviceID_DF2FF114_783D_E8EC_DC76_0FC98EF70DB4 = 'C:'


    Like in the OM DW how can there be a entry of CounterName = 'Free Megabytes' but not one for Disk Size .  It needs to know what the current disk size is to work out the Free Megabytes therfore OM DW needs to know the total amount of magabytes that is being consunmed for the volume.


    Monday, February 19, 2018 4:03 AM
  • Ohhhh......
    Got it , you want MB and % for a particular day with the total allotted size.

    Here we go:-

    select  Path, FullName, InstanceName, cast(datetime as date),
    Avg(case when countername = '% Free Space' then AverageValue end) As "% Free Space", 
    Avg(case when countername = 'Free Megabytes' then AverageValue end) As "Free Megabytes",


    --(((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' then AverageValue end))*100)/1024) as "Allocated GB"
    cast (((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' and Averagevalue!=0 then AverageValue end))*100)/1024 as decimal(10,2)) as "Allocated GB"


    from OperationsManagerDW.Perf.vPerfdaily pvpr 
    inner join OperationsManagerDW.dbo.vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId
    = vpri.PerformanceRuleInstanceRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 
    WHERE (CounterName Like '% Free Space' or 
                  CounterName Like 'Free Megabytes' and
                  InstanceName like 'C:' )
    and ObjectName like '%logicalDisk%' 
    --and DateTime >= getdate()-2
    --and DateTime <=''2014-06-17 05:22:41.000''
    --and FullName like ''''
    group by  Path, FullName, InstanceName, Cast(datetime as Date)
    --order by '% free space' ,Cast(datetime as Date)


    Run this on DW and you will get information about C: drive....likewise change this to D: or E: or etc. and for Linux change  logicalDisk to Logical Disk.

    Note:- It will give report for all windows server but for one you need to edit this line --and FullName like '''' and you need to give whole path as i have given like this

    and FullName like 'Microsoft.Windows.Server.6.2.LogicalDisk:serverfqdn;C:' and dont forget to remove -- bar from this line if you need output for one server only. 

    And you can also customize dates as per your requirements. 

    --and DateTime >= getdate()-2
    --and DateTime <=''2014-06-17 05:22:41.000''

    again if you are customizing it , please dont forget to remove -- from these lines otherwise it will mark as comment and will not execute.

    The output would be like below:- 

    Hope you have got now, what you are looking for.....

    Please mark as answer if it helps...


    Cheers, Gourav (Please do take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) or find my Facebook and LinkedIn link in profile.

    • Proposed as answer by GouravIN Monday, February 19, 2018 5:19 AM
    • Marked as answer by Goce_D Monday, February 19, 2018 9:31 PM
    Monday, February 19, 2018 5:09 AM
  • Thank you so much !!!!

    It is what I needed.  I really appreciate it !!!!!

    You have made my life easier :) 

    Monday, February 19, 2018 9:32 PM
  • No need of thanks buddy :)

    Keep SCOMing..... :)


    Cheers, Gourav (Please do take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) or find my Facebook and LinkedIn link in profile.

    Tuesday, February 20, 2018 3:32 AM
  • Hi Gourav,

    I am in need of pulling disk usage report for multiple server from SCOM databases. Required fields in the report is Date, Server Name, Drive Letter, Over All drive Size, Current free space in percent and in MB's. Below is the format that I am looking for and request you to help me with the script. Thanks in Advance.

    DateTime Server Name Disk Total Drive Size in GB Current Free Space in MB's Current Free Space in %
    2/5/2018 7:00 Server.FQDN D: 200 GB 580 MB 10%
    2/5/2018 7:00 Server.FQDN E: 500 GB 1250 MB 18%
    2/5/2018 7:00 Server.FQDN F: 300 GB 1500 MB 39%
    2/5/2018 7:00 Server.FQDN G: 1000 GB 18000 MB 72%
    2/5/2018 7:00 Server.FQDN C: 150 GB 10000 MB 30%
    2/5/2018 7:00 Server.FQDN D: 600 GB 9000 MB 15%
    Tuesday, February 20, 2018 1:12 PM
  • Hi Bala,

    You can comment the instance name in same query to get all the drive information. As this report already contains Free space MB, % free space, Total Space, Server Name, Date, Disk Partition everything. 

    So run this on DW:-

    select  Path, FullName, InstanceName, cast(datetime as date),
    Avg(case when countername = '% Free Space' then AverageValue end) As "% Free Space", 
    Avg(case when countername = 'Free Megabytes' then AverageValue end) As "Free Megabytes",


    --(((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' then AverageValue end))*100)/1024) as "Allocated GB"
    cast (((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' and Averagevalue!=0 then AverageValue end))*100)/1024 as decimal(10,2)) as "Allocated GB"


    from OperationsManagerDW.Perf.vPerfdaily pvpr 
    inner join OperationsManagerDW.dbo.vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId
    = vpri.PerformanceRuleInstanceRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 
    WHERE (CounterName Like '% Free Space' or 
                  CounterName Like 'Free Megabytes')
    and ObjectName like '%logicalDisk%' 
    --and DateTime >= getdate()-2
    --and DateTime <=''2014-06-17 05:22:41.000''
    --and FullName like ''''
    group by  Path, FullName, InstanceName, Cast(datetime as Date)
    --order by '% free space' ,Cast(datetime as Date)

    via you will get information for all the disk partition report along with % Free, Free MB, Server Name, Date, Disk Partition.

    Hope this helps.

    Please let me know if you need any further information!

     

    Cheers, Gourav (Please do take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) or find my Facebook and LinkedIn link in profile.

    Tuesday, February 20, 2018 4:27 PM
  • Hi Gourav,

    Thanks a lot for your valuable time and response. I tried above query by giving SERVER NAME'S in FULLNAME LIKE 'SERVER1.FQDN.com, SERVER2.FQDN.com, SERVER3.FQDN.COM'.  

    Below is the total query that I ran:

    select  Path, FullName, InstanceName, cast(datetime as date),
    Avg(case when countername = '% Free Space' then AverageValue end) As "% Free Space", 
    Avg(case when countername = 'Free Megabytes' then AverageValue end) As "Free Megabytes",

    --(((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' then AverageValue end))*100)/1024) as "Allocated GB"
    cast (((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' and Averagevalue!=0 then AverageValue end))*100)/1024 as decimal(10,2)) as "Allocated GB"

    from OperationsManagerDW.Perf.vPerfdaily pvpr 
    inner join OperationsManagerDW.dbo.vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId
    = vpri.PerformanceRuleInstanceRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 
    WHERE (CounterName Like '% Free Space' or 
                  CounterName Like 'Free Megabytes')
    and ObjectName like '%logicalDisk%' 
    and DateTime >= getdate()-30
    --and DateTime <=''2014-06-17 05:22:41.000''
    and FullName like 'SERVER1.FQDN.com, SERVER2.FQDN.com, SERVER3.FQDN.COM'
    group by  Path, FullName, InstanceName, Cast(datetime as Date)
    --order by '% free space' ,Cast(datetime as Date)

    When I ran the above query it got executed without any errors but I got empty output with no values in it. Kindly request you to help me on this. Thanks in Advance.

    Output:

    Path FullName InstanceName No column name %Free space Free Megabytes Allocated GB


    Wednesday, February 21, 2018 12:42 PM
  • and DateTime >= getdate()-30

    --and DateTime <=''2014-06-17 05:22:41.000''
    and FullName like 'SERVER1.FQDN.com, SERVER2.FQDN.com, SERVER3.FQDN.COM'
    group by  Path, FullName, InstanceName, Cast(datetime as Date)
    --order by '% free space' ,Cast(datetime as Date)

    When I ran the above query it got executed without any errors but I got empty output with no values in it. Kindly request you to help me on this. Thanks in Advance.

    Output:

    Path FullName InstanceName No column name %Free space Free Megabytes Allocated GB


    This is because of above mistakes; you need to put these things in below manner:-

    and ObjectName like '%logicalDisk%' 
    and DateTime >= getdate()-30
    and DateTime >='2018-01-01 05:22:41.000'
    and FullName like 'Microsoft.Windows.Server.6.2.LogicalDisk:serverFQDN;C:'
    group by  Path, FullName, InstanceName, Cast(datetime as Date)
    order by '% free space' ,Cast(datetime as Date)

    Hope you got clarity, Now elaborating One on One:-

    looks like you need to fetch report for last 30 days, When you are trying to fetch report for last 30 then you also need to define the date since from where it need to start and you missed that coz you have comment that line via -- so  and DateTime >='2018-01-01 05:22:41.000' give in this manner and please change the arrow sign <= to >= like i have done. 

    And full name would be your object full name coz here we are accessing SCOM's views so we need to define object full name like i have given. but for server 2008 family it would be   

    Microsoft.Windows.Server.2008.LogicalDisk:serverFQDN;C: and change the last C: to D: and E: so on but keep in mind you can only fetch one at a time.

    And in the last line when we are fetching report for particular time then we need to arrange it via time so remove comment -- from this line as well like below

    order by '% free space' ,Cast(datetime as Date)

    So The new query would be like this 

    select  Path, FullName, InstanceName, cast(datetime as date),
    Avg(case when countername = '% Free Space' then AverageValue end) As "% Free Space", 
    Avg(case when countername = 'Free Megabytes' then AverageValue end) As "Free Megabytes",


    --(((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' then AverageValue end))*100)/1024) as "Allocated GB"
    cast (((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' and Averagevalue!=0 then AverageValue end))*100)/1024 as decimal(10,2)) as "Allocated GB"


    from OperationsManagerDW.Perf.vPerfdaily pvpr 
    inner join OperationsManagerDW.dbo.vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId
    = vpri.PerformanceRuleInstanceRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 
    WHERE (CounterName Like '% Free Space' or 
                  CounterName Like 'Free Megabytes' and
                  InstanceName like 'C:' )
    and ObjectName like '%logicalDisk%' 
    and DateTime >= getdate()-30
    and DateTime >='2018-01-01 05:22:41.000'
    and FullName like 'Microsoft.Windows.Server.6.2.LogicalDisk:serverfqdn;C:'
    group by  Path, FullName, InstanceName, Cast(datetime as Date)
    order by '% free space' ,Cast(datetime as Date)

    Hope this helps but it will give information of last 30 days of a one disk partition at a time :)

    Please post back if need still any help, will share your feedback :)


    Cheers, Gourav (Please do take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) or find my Facebook and LinkedIn link in profile.

    Wednesday, February 21, 2018 1:32 PM
  • Hi Gourav,

    Me again.  Do you know why OperationsManagerDW does not hold the value of Allocated GB and why we need to work it out ?

    Cause we have noticed that when we use the script you made sometimes the Allocated GB for a volume changes (as you can see the Allocated GB should be 900, but it jumps around)

    

    Friday, March 16, 2018 3:40 AM
  • Hi Goce,

    Surprised to hear this issue from your side because i am running this query for months but have not got anything like that so cross check i have run this today as well for a specific server then again i got no issues, please find the SS below :-

    what can i suggest that you check your name and your server behavior as well. is it Vm server and disk fluctuation is going on it. And for further try this to run 

    select  Path, FullName, InstanceName, cast(datetime as date),
    Avg(case when countername = '% Free Space' then AverageValue end) As "% Free Space", 
    Avg(case when countername = 'Free Megabytes' then AverageValue end) As "Free Megabytes",


    --(((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' then AverageValue end))*100)/1024) as "Allocated GB"
    cast (((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' and Averagevalue!=0 then AverageValue end))*100)/1024 as decimal(10,2)) as "Allocated GB"


    from OperationsManagerDW.Perf.vPerfdaily pvpr 
    inner join OperationsManagerDW.dbo.vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId
    = vpri.PerformanceRuleInstanceRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 
    WHERE (CounterName Like '% Free Space' or 
                  CounterName Like 'Free Megabytes')
    and ObjectName like '%logicalDisk%' 
    and DateTime >= getdate()-30
    and DateTime >='2018-01-01 05:22:41.000'
    and FullName like 'Microsoft.Windows.Server.2008.LogicalDisk:ServerFQDN;C:'
    group by  Path, FullName, InstanceName, Cast(datetime as Date)
    --order by '% free space' ,Cast(datetime as Date)

    it will work for you not and what i will suggest run the below one as well and check what will come in FullName column coz it will object full name.

    select  Path, FullName, InstanceName, cast(datetime as date),
    Avg(case when countername = '% Free Space' then AverageValue end) As "% Free Space", 
    Avg(case when countername = 'Free Megabytes' then AverageValue end) As "Free Megabytes",


    --(((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' then AverageValue end))*100)/1024) as "Allocated GB"
    cast (((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' and Averagevalue!=0 then AverageValue end))*100)/1024 as decimal(10,2)) as "Allocated GB"


    from OperationsManagerDW.Perf.vPerfdaily pvpr 
    inner join OperationsManagerDW.dbo.vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId
    = vpri.PerformanceRuleInstanceRowId 
    inner join OperationsManagerDW.dbo.vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 
    WHERE (CounterName Like '% Free Space' or 
                  CounterName Like 'Free Megabytes')
    and ObjectName like '%logicalDisk%' 
    and DateTime >= getdate()-30
    and DateTime >='2018-01-01 05:22:41.000'
    --and FullName like 'Microsoft.Windows.Server.2008.LogicalDisk:Serverfqdn;C:'
    group by  Path, FullName, InstanceName, Cast(datetime as Date)
    --order by '% free space' ,Cast(datetime as Date)

    Put the Fullname from this query and paste name in first query then you might get clear results.  and keep these save for future..... post further if any confusion :)


    Cheers, Gourav Please remember to mark the replies as answers if it helped.

    Friday, March 16, 2018 4:34 AM
  • Guys!

    I pray you! NEVER calculate allocated space from free MB and %. It works quite well when it's about 50% free, but it gives you enormous calculation error when close to 0%

    there is another table in DW, which keeps track on inventoried property changes. Have a look at the query below. It accepts managed entity row id (say of logical disk entity for a particular C: drive) and return a history of all properties:

    declare @ManagedEntityRowId as INT = xxx
    
    	SELECT b.ManagedEntityRowId,b.TopLevelHostManagedEntityRowId,b.PropertySystemName,b.PropertyGuid
    		,b.FromDateTime,b.PropertyValue
    		,CASE WHEN MAX(b.FromDateTime)OVER(PARTITION BY b.ManagedEntityRowId,b.PropertyGuid) = b.FromDateTime THEN 1 ELSE 0 END AS [TheLatestValue]
    	FROM (
    		SELECT a.ManagedEntityRowId,a.TopLevelHostManagedEntityRowId,a.PropertySystemName
    			,a.PropertyGuid,MIN(a.FromDateTime) AS [FromDateTime],a.PropertyValue
    		FROM (
    			SELECT bme.ManagedEntityRowId,bme.TopLevelHostManagedEntityRowId
    				,metypeprop.PropertySystemName,meprop.PropertyGuid,meprop.FromDateTime,meprop.PropertyValue
    				,ROW_NUMBER()OVER(PARTITION BY bme.ManagedEntityRowId,meprop.PropertyGuid ORDER BY meprop.FromDateTime ASC) AS [asc]
    				,ROW_NUMBER()OVER(PARTITION BY bme.ManagedEntityRowId,meprop.PropertyGuid,meprop.PropertyValue ORDER BY meprop.FromDateTime DESC) AS [desc]
    			FROM dbo.vManagedEntity bme
    			JOIN dbo.vManagedEntityPropertySet meprop ON meprop.ManagedEntityRowId = bme.ManagedEntityRowId
    			JOIN dbo.vManagedEntityTypeProperty metypeprop ON metypeprop.PropertyGuid = meprop.PropertyGuid
    			WHERE bme.ManagedEntityRowId = @ManagedEntityRowId
    		) a	
    		GROUP BY a.ManagedEntityRowId,a.TopLevelHostManagedEntityRowId
    			,a.PropertySystemName
    			,a.PropertyGuid,a.PropertyValue,a.[asc]+a.[desc]
    	) b

    Cheers

    Max

    Sunday, March 18, 2018 8:11 PM
  • Hi Max,

    I will give it a go and get back to you.

    thanks

    Sunday, March 18, 2018 9:55 PM
  • Hi Max,

    what will be XXX in this query , what i need to give in place of it? and can i directly access this ManagedEntityRowId from DW coz when i have run this "select * from ManagedEntityRowId" it revert nothing except error.

    declare @ManagedEntityRowId as INT = xxx


    Cheers, Gourav Please remember to mark the replies as answers if it helped.


    • Edited by GouravIN Monday, March 19, 2018 2:35 AM
    Monday, March 19, 2018 2:34 AM
  • Hi Gourav,

    It's an internal unique primary key of the class instance. You can get it using vManagedEntity view as per query below

    declare @ManagedEntityRowId as int
    
    select @ManagedEntityRowId = diskme.ManagedEntityRowId
      from vManagedEntity diskme
      join vManagedEntity parentme on diskme.TopLevelHostManagedEntityRowId = parentme.ManagedEntityRowId
      where parentme.Name = 'server.domain.local'
        and diskme.Name = 'C:'

    OR

    just extract from your query using vme alias. Then you can either join, or cross apply, or use sub-select (it will be the easiest option).

    You can also create a stored function, which is based on the property query, to return just the latest disk size and then use cross apply to call it in your report to produce "Allocated" column.

    NB! Query in this post relays on the fact, that there is no other objects called "X:", but disk instances.

    Monday, March 19, 2018 8:28 PM
  • Ok Guys,

    Here we go . First i created a new view in the OperationsManagerDW with the following

    SELECT        Perf.vPerfDaily.AverageValue, dbo.vPerformanceRuleInstance.InstanceName, dbo.vPerformanceRule.ObjectName, dbo.vPerformanceRule.CounterName, dbo.vPerformanceRuleInstance.LastReceivedDateTime, 
                             Perf.vPerfDaily.DateTime, dbo.vManagedEntity.DWCreatedDateTime, dbo.vManagedEntity.ManagedEntityGuid, dbo.vManagedEntity.ManagedEntityDefaultName, dbo.vManagedEntity.DisplayName, 
                             dbo.vManagedEntity.Name, dbo.vManagedEntity.Path, dbo.vManagedEntity.FullName
    FROM            Perf.vPerfDaily INNER JOIN
                             dbo.vPerformanceRuleInstance ON Perf.vPerfDaily.PerformanceRuleInstanceRowId = dbo.vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN
                             dbo.vManagedEntity ON Perf.vPerfDaily.ManagedEntityRowId = dbo.vManagedEntity.ManagedEntityRowId INNER JOIN
                             dbo.vPerformanceRule ON dbo.vPerformanceRuleInstance.RuleRowId = dbo.vPerformanceRule.RuleRowId
    WHERE        (dbo.vPerformanceRule.ObjectName = 'Logicaldisk') AND (dbo.vPerformanceRule.CounterName <> 'Current Disk Queue Length') AND (dbo.vPerformanceRule.CounterName <> 'Avg. Disk sec/Transfer') AND 
                             (dbo.vPerformanceRule.CounterName <> '% Idle Time') AND (dbo.vPerformanceRule.CounterName <> 'Disk Bytes/sec') AND (dbo.vPerformanceRule.CounterName <> '% Free Space')

    This give me all the servers with the total disk size and free disk space values

    Then I ran the following sql query and it works

    use OperationsManagerDW
    select p."Drive",p."Total Space (GB)",p."Used Space (GB)",p.path,p.datetime as "Date" from (
    select a.path path,a.datetime Datetime ,a.instancename "Drive", round((b.averagevalue/1024),2) "Total Space (GB)",  
    round(((case when a.[CounterName] <> 'TotalDiskSize' and b.countername<>'Free Megabytes' then b.averageValue - a.averagevalue  end)/1024),2) "Used Space (GB)"
    from [dbo].[DHPW-Server-Logical-Disk_total_Used_Space] a, [dbo].[XXXX-Server-Logical-Disk_total_Used_Space] b 
      where a.datetime=b.datetime
      and a.instanceName=b.instanceName
      and a.path=b.path) p 

    where (p."Used Space (GB)" is not null) AND

    DateTime >= getdate()-100
    and p.path like 'Server.FQDN' 
     AND p.Drive like 'ServerVolume'

    It works !!!

    Wednesday, March 21, 2018 9:27 PM
  • Hi Goce_D

    Yes, you query works until:

    1. Someone add more performance rules for disks. What you do is exclude counter you don't need. But a counter may be added at any time -- you'll must to update your exclusion list.
    2. Where did you get "TotalDiskSize" counter? It doesn't exist in standard Windows OS SCOM MPs.

    For instance, your view query returns only "Free Megabytes" counter, but nothing else.

    Cheers

    Max

    Wednesday, March 21, 2018 10:33 PM
  • hello,

    for Question 1. We dont need the other entries for what we are doing .

    For Question 2 . When i created the view , that instance "Free Megabytes"  appeared.

    It is from the following web site

    http://scom-2012.blogspot.com.au/2012/07/setup-disk-report-in-scom-2012-part-1.html

    Also please note I am not a SQL person and I am trying to get my head around SCOM. 

    Thursday, March 22, 2018 1:15 AM
  • That's exactly what I'm tried to say: you added a custom performance data collection rule. If tomorrow Microsoft adds another rule in their MP, or your colleague adds a similar custom collection rule -- your SQL query will fail, and no one will be able to figure why. Thus, you shall use include criteria, not exclude one.

    Next point -- there is no standard performance collection rule from Microsoft, like in the blog post you linked, for a strong reason. This is just not smart to fill a DB with millions repetitive values. You may have disks size not being changed for years, but storing the same number in DB. That's why disk size value should be queried from discovery data history -- please see my previous post.

    Thursday, March 22, 2018 3:10 AM
  • Hi Gourav

    I have same requirement of pulling the disk space on all servers as like above. But we are using monitoring tool : HP operations manager. My question is whether we can run the same script in our tool as well?

    Thanks

    Kesava

    Thursday, October 4, 2018 3:13 PM
  • You probably can't as it's DB will have different tables.

    Cheers


    Sam (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) Blog:AnalyticOps Insights Twitter:Sameer Mhaisekar

    Thursday, October 4, 2018 4:09 PM
  • Hi Gourav, we ran the script and it triggered an error and same been attached below.

    Error: Msg 208, Level 16, State 1, Line 1

    Invalid object name 'OperationsManagerDW.Perf.vPerfdaily' .

    Can you please let us know what to be checked from DB end.

    Thanks

    Kesava

    Thursday, October 4, 2018 4:14 PM
  • we have checked the all objects and couldn't able to find ' OperationsManagerDW.Perf.vPerfdaily'.

    Below are the list of objects what we have. Please check and recommend which object to be selected?

    sysrscols
    sysrowsets
    sysallocunits
    sysfiles1
    syspriorities
    sysfgfrag
    sysphfg
    sysprufiles
    sysftinds
    sysowners
    sysprivs
    sysschobjs
    syscolpars
    sysnsobjs
    syscerts
    sysxprops
    sysscalartypes
    systypedsubobjs
    sysidxstats
    sysiscols
    sysbinobjs
    sysaudacts
    sysobjvalues
    sysclsobjs
    sysrowsetrefs
    sysremsvcbinds
    sysxmitqueue
    sysrts
    sysconvgroup
    sysdesend
    sysdercv
    syssingleobjrefs
    sysmultiobjrefs
    sysguidrefs
    syscompfragments
    sysftstops
    sysqnames
    sysxmlcomponent
    sysxmlfacet
    sysxmlplacement
    sysobjkeycrypts
    sysasymkeys
    syssqlguides
    sysbinsubobjs
    syssoftobjrefs
    OV_MS_Instruction
    DF__OPP_OV_Ag__HistC__00551192
    SERVICE_LOG
    PK_OV_MS_Instruction
    DF__OPP_OV_Ag__Histo__014935CB
    PK_SERVICE_LOG
    OV_MS_KeyRelationCache
    OPP_OV_AgentCommType
    OV_HierarchicalNodes
    PK_OV_MS_KeyRelationCache
    PK_OPP_OV_AgentCommType
    DF__OV_Hierar__Messa__03BB8E22
    OV_MS_Message
    SK_OPP_OV_AgentCommType
    GROUPS
    PK_OV_MS_Message
    DF__OPP_OV_Ag__HistC__0519C6AF
    SYSTEMS
    OV_MS_MessageCMAs
    DF__OPP_OV_Ag__Histo__060DEAE8
    V_ACTIVE_MESSAGES_BY_NODE
    PK_OV_MS_MessageCMAs
    OPP_OV_OsType
    V_ACTIVE_MESSAGES_BY_SERVICE
    OV_MS_ServerList
    PK_OPP_OV_OsType
    V_ACTIVE_MESSAGES_BY_SERVICE_75
    PK_OV_MS_ServerList
    SK_OPP_OV_OsType
    V_ACTIVE_MESSAGES_NODE_GROUP
    OV_MS_Stats
    DF__OPP_OV_Os__HistC__09DE7BCC
    V_ACTIVE_MESSAGE_AGE_NODE
    OV_RS_Role
    DF__OPP_OV_Os__Histo__0AD2A005
    V_ACTIVE_MESSAGE_AGE_NODE_GROUP
    PK_OV_RS_Role
    OPP_OV_OsVersion
    V_MESSAGE_GROUP_OPERATOR
    OV_RS_RoleAndCategory
    PK_OPP_OV_OsVersion
    V_MESSAGE_TREND_BY_NODEGROUP
    FK_OV_RS_RoleAndCategory_OV_RS_Role
    DF__OPP_OV_Os__HistC__0DAF0CB0
    V_MESSAGE_TREND_BY_SERVICE
    OV_RS_RoleAndMsgGroup
    DF__OPP_OV_Os__Histo__0EA330E9
    V_MESSAGE_TREND_BY_SERVICE_75
    FK_OV_RS_RoleAndMsgGroup_OV_RS_Role
    OPP_OV_SystemType
    V_NODES_BY_SEVERITY
    OV_RS_UserAndRole
    PK_OPP_OV_SystemType
    V_NODE_ASSIGNM_BY_NODEGROUP
    FK_OV_RS_UserAndRole_OV_RS_Role
    SK_OPP_OV_SystemType
    V_NODE_GROUP_OPERATOR
    OV_CSA_RequestNodeMap
    DF__OPP_OV_Sy__HistC__1273C1CD
    V_NODE_NAMES
    PK_OV_CSA_RequestNodeMap
    DF__OPP_OV_Sy__Histo__1367E606
    V_RESOLUTION_TIME_BY_NODE
    ov_ShowKillOpenviewProcesses
    OPP_OV_NodePlatform
    V_RESOLUTION_TIME_BY_NODE_GROUP
    OM_GetCSCollation
    PK_OPP_OV_NodePlatform
    V_RESOLUTION_TIME_TREND
    STO_VERSION
    DF__OPP_OV_No__AutoD__164452B1
    V_SERVICE_LOG
    DF__OPP_OV_Nod__Icon__173876EA
    V_SERVICE_LOG_75
    DF__OPP_OV_No__HistC__182C9B23
    V_SERVICE_SEVERITIES
    PK__STO_VERS__39AB491F17F790F9
    DF__OPP_OV_No__Histo__1920BF5C
    V_SERVICE_SEVERITIES_75
    STO_NAMESPACES
    FK_OPP_OV_NodePlatform_OPP_OV_SystemType
    V_UNASSIGNED_RESPONSIBILITIES
    FK_OPP_OV_NodePlatform_OPP_OV_OsType
    V_OV_MS_NODEOUTAGE_MESSAGES
    FK_OPP_OV_NodePlatform_OPP_OV_OsVersion
    V_OV_MS_NONOUTAGE_MESSAGES
    PK__STO_NAME__72E12F1A1BC821DD
    FK_OPP_OV_NodePlatform_OPP_OV_AgentCommType
    V_OV_MS_OUTAGE_MESSAGES
    STO_CLASSES
    FK_OPP_OV_NodePlatform_OPP_OV_AgentBinaryFormat
    V_OV_MS_SERVICEOUTAGE_MESSAGES
    OV_LIC_LicensedProduct
    V_MESSAGE_TREND_BY_NODEGROUP_MM
    PK_OV_LIC_LicensedProduct
    V_MESSAGE_TREND_BY_SERVICE_MM
    PK__STO_CLAS__F588E0641F98B2C1
    U_OV_LIC_LicensedProduct
    V_RESOLUTION_TIME_BY_NODE_MM
    sto_ov_tools
    OV_LIC_ProductOnNode
    V_SERVICE_LOG_MM
    PK_OV_LIC_ProductOnNode
    V_SERVICE_SEVERITIES_MM
    FK_OV_LIC_ProductOnNode_OV_LIC_LicensedProduct
    V_NODE_ASSIGNMENTS_BY_NODEGROUP_SQL2005
    PK__sto_ov_t__72E12F1A236943A5
    OV_LIC_NodeStats
    V_TOP_ACTIVE_MESSAGES_BY_NODEGROUP_SQL2005
    sto_ov_toolcomponent
    OV_PM_Package
    sto_ov_virtualhostedon
    PK_OV_PM_Package
    U_OV_PM_Package_Name
    PK__sto_ov_t__3664162E2739D489
    FK_OV_PM_Package_OV_PM_LicenseProduct
    PK__sto_ov_v__3664162E27F8EE98
    sto_ov_action
    OV_PM_PackageVersion
    sto_ov_servicetypedependency
    PK_OV_PM_PackageVersion
    U_OV_PM_PackageIdVersion
    PK__sto_ov_a__72E12F1A2B0A656D
    FK_OV_PM_PackageVersion_OV_PM_Package
    PK__sto_ov_s__A96F93C52BC97F7C
    sto_ov_toolactioncomponent
    OV_PM_PackageImplementation
    sto_ov_servicediscoveredby
    PK_OV_PM_PackageImplementation
    FK_OV_PM_PackageImplementation_OV_PM_PackageVersion
    PK__sto_ov_t__3664162E2EDAF651
    OV_PM_PackagePlatform
    PK__sto_ov_s__462C344C2F9A1060
    sto_ov_modelstate
    U_OV_PM_PackagePlatformId
    sto_ov_componentdiscoveredby
    FK_OV_PM_PackagePlatform_OV_PM_PackageImplementation
    FK_OV_PM_PackagePlatform_OPP_OV_OsType
    PK__sto_ov_m__209527E332AB8735
    FK_OV_PM_PackagePlatform_OPP_OV_OsVersion
    PK__sto_ov_c__92D56456336AA144
    sto_ov_externalnode
    FK_OV_PM_PackagePlatform_OPP_OV_AgentBinaryFormat
    sto_ov_autodeploypolicygroup
    FK_OV_PM_PackagePlatform_OPP_OV_AgentCommType
    OV_PM_PackageOnNode
    PK__sto_ov_e__72E12F1A367C1819
    PK_OV_PM_PackageOnNode
    PK__sto_ov_a__72E12F1A373B3228
    FK_OV_PM_PackageOnNode_OV_PM_PackageVersion
    sto_ov_nodegroupautodeploypolicygroupassoc
    OV_PM_Subpackage
    PK_OV_PM_Subpackage
    U_OV_PM_Subpackage_Name
    PK__sto_ov_n__87E9B6483B0BC30C
    sto_ov_nodegroup
    OV_PM_SubpackageVersion
    sto_ov_nodegroupactions
    PK_OV_PM_SubpackageVersion
    U_OV_PM_SubpackageIdVersion
    PK__sto_ov_n__72E12F1A3E1D39E1
    FK_OV_PM_SubpackageVersion_OV_PM_Subpackage
    PK__sto_ov_n__7663EDB83EDC53F0
    sto_ov_service
    OV_PM_PackageStructure
    sto_ov_servicetypeautodeploypolicygroupassoc
    PK_OV_PM_PackageStructure
    FK_OV_PM_PackageStructure_OV_PM_PackageImplementation
    PK__sto_ov_s__72E12F1A41EDCAC5
    FK_OV_PM_PackageStructure_OV_PM_SubbpackageVersion
    PK__sto_ov_s__87E9B64842ACE4D4
    sto_ov_servicetypedefinition
    OV_PM_SubpackageOnNode
    sto_ov_servicetypeactions
    PK_OV_PM_SubpackageOnNode
    FK_OV_PM_SubpackageOnNode_OV_PM_SubpackageVersion
    PK__sto_ov_s__497F6CB445BE5BA9
    OV_PM_PolicyType
    PK__sto_ov_s__7663EDB8467D75B8
    sto_ov_servicedependency
    PK_OV_PM_PolicyType
    sto_ov_unmanagedagent
    U_OV_PM_PolicyType_Name
    OV_PM_PolicyTypeVersion
    PK__sto_ov_s__A96F93C5498EEC8D
    PK_OV_PM_PolicyTypeVersion
    PK__sto_ov_u__928B079B4A4E069C
    sto_ov_startpoint
    U_OV_PM_PolicyTypeIdVersion
    sto_ov_messagefilter
    FK_OV_PM_PolicyTypeVersion_OV_PM_PolicyType
    OV_PM_PolicyTypePlatform
    PK__sto_ov_s__72E12F1A4D5F7D71
    U_OV_PM_PolicyTypePlatform
    PK__sto_ov_m__3213E83F4E1E9780
    sto_ov_calculationrule
    FK_OV_PM_PolicyTypePlatform_OV_PM_PolicyTypeVersion
    sto_ov_dependencydiscoveredby
    FK_OV_PM_PolicyTypePlatform_OPP_OV_OsType
    OV_PM_TypeNeedsPackage
    PK__sto_ov_c__097FE60451300E55
    PK_OV_PM_TypeNeedsPackage
    PK__sto_ov_d__0C50F18B51EF2864
    sto_ov_propagationrule
    FK_OV_PM_TypeNeedsPackage_OV_PM_PolicyTypeVersion
    sto_ov_dnsdomaindiscoveryconfig
    FK_OV_PM_TypeNeedsPackage_OPP_OV_AgentCommType
    FK_OV_PM_TypeNeedsPackage_OV_PM_Package
    PK__sto_ov_p__097FE60455009F39
    OV_PM_Policy
    PK__sto_ov_d__2A74A5EE55BFB948
    sto_ov_servicetypecomponent
    PK_OV_PM_Policy
    sto_ov_managednodeactions
    U_OV_PM_Policy_NameTypeId
    FK_OV_PM_Policy_OV_PM_PolicyType
    PK__sto_ov_s__3664162E58D1301D
    FK_OV_PM_PolicyVersion_OV_PM_LicenseProduct
    PK__sto_ov_m__7663EDB859904A2C
    sto_ov_servicecomposition
    OV_PM_PolicyVersion
    PK_OV_PM_PolicyVersion
    U_OV_PM_PolicyIdVersionConflict
    PK__sto_ov_s__3664162E5CA1C101
    FK_OV_PM_PolicyVersion_OV_PM_Policy
    sto_ov_nodegroupmember
    OV_PM_PolicyStream
    PK_OV_PM_PolicyStream
    DF_OV_PM_PolicyStream_Name
    PK__sto_ov_n__3664162E607251E5
    FK_OV_PM_PolicyStream_OV_PM_PolicyVersion
    sto_ov_confignamespace
    OV_PM_PolicyOnNode
    PK_OV_PM_PolicyOnNode
    DF_OV_PM_PolicyOnNode_ForServer
    PK__sto_ov_c__3213E83F6442E2C9
    FK_OV_PM_PolicyOnNode_OV_PM_PolicyVersion
    sto_ov_configitem
    OV_PM_PolicyGroup
    PK_OV_PM_PolicyGroup
    FK_OV_PM_PolicyGroup_OV_PM_PolicyGroup
    PK__sto_ov_c__3213E83F681373AD
    OV_PM_PolicyGroupAssignment
    sto_ov_managementmodule
    PK_OV_PM_PolicyGroupAssignment
    FK_OV_PM_PolicyGroupAssignment_OV_PM_PolicyGroup
    FK_OV_PM_PolicyGroupAssignment_OV_PM_Policy
    PK__sto_ov_m__72E12F1A6BE40491
    FK_OV_PM_PolicyGroupAssignment_OV_PM_PolicyVersion
    sto_ov_managementmodulepolicytemplateassoc
    OV_PM_Category
    PK_OV_PM_Category
    OV_PM_CategoryOfPolicy
    PK__sto_ov_m__893324606FB49575
    FK_OV_PM_CategoryOfPolicy_OV_PM_PolicyVersion
    sto_ov_policytemplate
    FK_OV_PM_CategoryOfPolicy_OV_PM_Category
    OV_PM_CategoryOnNode
    PK_OV_PM_CategoryOnNode
    PK__sto_ov_p__72E12F1A73852659
    FK_OV_PM_CategoryOnNode_OV_PM_Category
    sto_ov_platformmatch
    OV_PM_Job
    QueryNotificationErrorsQueue
    PK_OV_PM_Job
    queue_messages_1977058079
    DF_OV_PM_Job_VirtualNodeId
    EventNotificationErrorsQueue
    PK__sto_ov_p__FA09AD507755B73D
    OV_Version
    queue_messages_2009058193
    sto_ov_managednode
    OV_PM_InstrumentationMap
    ServiceBrokerQueue
    PK__sto_ov_managednode
    PK_OV_PM_InstrumentationMap
    queue_messages_2041058307
    FK_OV_PM_PackageOnNode_sto_ov_managednode
    OV_MS_Annotation
    filestream_tombstone_2073058421
    FK_OV_PM_SubpackageOnNode_sto_ov_managednode
    PK_OV_MS_Annotation
    syscommittab
    FK_OV_PM_PolicyOnNode_sto_ov_managednode
    OPP_OV_AgentBinaryFormat
    FK_OV_PM_CategoryOnNode_sto_ov_managednode
    PK_OPP_OV_AgentBinaryFormat
    FK_OV_PM_Job_sto_ov_managednode
    UQ__OV_MS_An__3214EC067E37BEF6
    SK_OPP_OV_AgentBinaryFormat
    FK_OV_LIC_ProductOnNode_sto_ov_managednode

    Thursday, October 4, 2018 4:25 PM
  • Like I said, these are 2 different products.

    You should open a thread in HP forums.


    Sam (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" wherever applicable. Thanks!) Blog:AnalyticOps Insights Twitter:Sameer Mhaisekar

    Thursday, October 4, 2018 4:30 PM
  • Hi gaurav,

    This query is very useful.thanks a lot for this.

    but one question suppose i only want data for one particular date for all servers in scom for all drives, so i made the below changes in the query, but i am not getting any data., how can i make modification on date if i want the data for all servers at one time but only for one particular date.

    and DateTime >= getdate()-2
    and DateTime <=''2019-10-03 05:22:41.000''
    --and FullName like ''''
    group by  Path, FullName, InstanceName, Cast(datetime as Date)
    --order by '% free space' ,Cast(datetime as Date).

    Also when i am running your query without making any changes, i am getting the data for all servers for all drives for 2 months, september and october. Please explain this


    AD


    • Edited by AD_SC Thursday, October 3, 2019 10:25 AM
    Thursday, October 3, 2019 10:25 AM
  • Hi Sir,

    Let me take this one by one to serve you better, here.

    1)- but one question suppose i only want data for one particular date for all servers in scom for all drives, so i made the below changes in the query, but i am not getting any data., how can i make modification on date if i want the data for all servers at one time but only for one particular date.

    <<<What are the counter you want to see for these servers>>>

    2)- Also when i am running your query without making any changes, i am getting the data for all servers for all drives for 2 months, september and october. Please explain this

    <<<Could you please share the query that you are running and also please check if your SCOM DW has data for the same, it might be case that data is not longer retained then 2 months by DW>>>


    Cheers, Gourav Please remember to mark the replies as answers if it helped.

    Thursday, October 3, 2019 10:55 AM
  • Hi Gaurav,

    Thanks for replying!

    1. Basically I want only allocated disk space, free space but the date I want is only current date or some specified duration .Since the 2 months data is coming all together for all servers, data seems very huge. so I am unable to copy paste the output in excel.

    2.it might be case that data is not longer retained then 2 months by DW>>> how to check this?

    3. I am running the below query

    select  Path, FullName, InstanceName, cast(datetime as date),

    Avg(case when countername = '% Free Space' then AverageValue end) As "% Free Space", 

    Avg(case when countername = 'Free Megabytes' then AverageValue end) As "Free Megabytes",





    --(((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' then AverageValue end))*100)/1024) as "Allocated GB"

    cast (((avg(case when countername = 'Free Megabytes' then AverageValue end)/Avg(case when countername = '% Free Space' and Averagevalue!=0 then AverageValue end))*100)/1024 as decimal(10,2)) as "Allocated GB"





    from OperationsManagerDW.Perf.vPerfdaily pvpr 

    inner join OperationsManagerDW.dbo.vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId 

    inner join OperationsManagerDW.dbo.vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId

    = vpri.PerformanceRuleInstanceRowId 

    inner join OperationsManagerDW.dbo.vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId 

    WHERE (CounterName Like '% Free Space' or 

                  CounterName Like 'Free Megabytes' and

                  InstanceName like 'C:' )

    and ObjectName like '%logicalDisk%' 

    --and DateTime >= getdate()-2

    --and DateTime <=''2014-06-17 05:22:41.000''

    --and FullName like '’

    group by  Path, FullName, InstanceName, Cast(datetime as Date)

    --order by '% free space' ,Cast(datetime as Date)


    AD

    Friday, October 4, 2019 2:56 AM
  • Hi There,

    You could use below query on DW, you have to only change the date in query

    select
    vManagedEntity.Path
    ,Perf.vPerfdaily.DateTime
    ,Perf.vPerfdaily.SampleCount
    ,vPerformanceRule.ObjectName
    ,vPerformanceRule.CounterName
    ,vManagedEntity.Name
    ,Perf.vPerfdaily.Averagevalue
    ,Perf.vPerfdaily.MinValue
    ,Perf.vPerfdaily.MaxValue
    ,vRule.RuleDefaultName

    from Perf.vPerfDaily
    join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid=Perf.vPerfDaily.PerformanceRuleInstanceRowid

    join vPerformanceRule on vPerformanceRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
    join vManagedEntity on vManagedEntity.ManagedEntityRowid=Perf.vPerfDaily.ManagedEntityRowId
    join vRule on vRule.RuleRowId=vPerformanceRuleInstance.RuleRowId
    where Perf.vPerfDaily.Datetime between '2012-10-01' and '2012-10-04'
    and vPerformanceRule.ObjectName='LogicalDisk'
    and vPerformanceRule.CounterName='% Free Space'
    and (vRule.RuleDefaultName='% Logical Disk Free space 2000'
    or vRule.RuleDefaultName='% Logical Disk Free Space 2003'
    or vRule.RuleDefaultName='% Logical Disk Free Space 2008'
    or vRule.RuleDefaultName='% Logical Disk Free Space 2012'
    or vRule.RuleDefaultName='% Logical Disk Free Space 2016')
    Order by Path,Name



     Hope this helps :)

    Cheers, Gourav Please remember to mark the replies as answers if it helped.

    Friday, October 4, 2019 6:52 AM