Where do I find the table that holds the Asset Management data displayed in the History Tab

Alastair_Smart

I need to produce a report of when assets were move i or out of a particular asset status.

Justin_Workman

The table you're looking for I believe is ServiceManager.dbo.MT_Cireson$AssetManagement$HardwareAsset_Log but that's going to be a pretty hefty SQL statement!  I would yield to someone with more SQL knowledge here to produce a query that gets what you want.
  
The approach I would take here is using powershell. I would somehow make use of the get-scsmobjecthistory command.  I would probably format the results with HTML and send it off as an email.

Conner_Wood

Another potential option would be to use the DWDataMart Database to get the status history.  Checking the "Cireson Asset Management Data Warehouse", they defined the Outriggers for Cireson Hardware Asset Status but THERE IS NO FACT DEFINED FOR THE ENUMERATION/OUTRIGGER which would show up as a view with the name "StatusDurationFact" in it.  Mine is rather out of date but I don't think the newer ones have that definition, in which case, the Archive/Data Warehouse is lacking this data for you.

So unfortunately you'd need to look at an existing management pack to see how to make it happen.

Ex.  "dbo.IncidentStatusDurationFactvw" was made from DW management pack "Service Manager Incident Management Data Warehouse Library".  Here's the highlights in that MP:

    <Measures>
      <Measure ID="TotalTimeMeasure" Accessibility="Public" Aggregation="Additive">
        <DataType ID="TTR" Type="int" CaseSensitive="false" MaxLength="256" Nullable="true" />
      </Measure>
    </Measures>
      
    <Facts>
      <Fact ID="IncidentStatusDurationFact" Accessibility="Public" Template="DWBase!DW.Transform.Template.StateTransition" Domain="DWBase!Domain.IncidentManagement" TimeGrain="Daily">
        <WaterMarkDependency>CoreIncident!System.WorkItem.Incident</WaterMarkDependency>
        <Dimensions>
          <Dimension Nullable="false">IncidentDim</Dimension>
        </Dimensions>
        <Outriggers>
          <Outrigger>IncidentStatus</Outrigger>
        </Outriggers>
        <DegenerateDimensions>
          <DegenerateDimension ID="StartDateTime" Type="datetime" CaseSensitive="false" MaxLength="256" Nullable="false" />
          <DegenerateDimension ID="FinishDateTime" Type="datetime" CaseSensitive="false" MaxLength="256" Nullable="true" />
        </DegenerateDimensions>
        <Measures>
          <Measure>TotalTimeMeasure</Measure>
        </Measures>
      </Fact>
    </Facts><br>

But if you are fine with using the LIVE database History tables, then I'd carry on with that.

Geoff_Ross

Hi @Adam_Stoppa

Your PowerShell is not the most efficient method but will certainly work and get you the asset history so nice work.

The first History item will show you who created it. However, you must consider that SCSM doesn't keep all history for all time. The default is to keep 1 year of history and this is configurable in the data retention settings. Once history has been groomed, there is no way to tell who created the asset.

Geoff

Justin_Workman

The table you're looking for I believe is ServiceManager.dbo.MT_Cireson$AssetManagement$HardwareAsset_Log but that's going to be a pretty hefty SQL statement!  I would yield to someone with more SQL knowledge here to produce a query that gets what you want.
  
The approach I would take here is using powershell. I would somehow make use of the get-scsmobjecthistory command.  I would probably format the results with HTML and send it off as an email.

Alastair_Smart

thanks very much for this, I will have to go down the Poershell route

Conner_Wood

Another potential option would be to use the DWDataMart Database to get the status history.  Checking the "Cireson Asset Management Data Warehouse", they defined the Outriggers for Cireson Hardware Asset Status but THERE IS NO FACT DEFINED FOR THE ENUMERATION/OUTRIGGER which would show up as a view with the name "StatusDurationFact" in it.  Mine is rather out of date but I don't think the newer ones have that definition, in which case, the Archive/Data Warehouse is lacking this data for you.

So unfortunately you'd need to look at an existing management pack to see how to make it happen.

Ex.  "dbo.IncidentStatusDurationFactvw" was made from DW management pack "Service Manager Incident Management Data Warehouse Library".  Here's the highlights in that MP:

    <Measures>
      <Measure ID="TotalTimeMeasure" Accessibility="Public" Aggregation="Additive">
        <DataType ID="TTR" Type="int" CaseSensitive="false" MaxLength="256" Nullable="true" />
      </Measure>
    </Measures>
      
    <Facts>
      <Fact ID="IncidentStatusDurationFact" Accessibility="Public" Template="DWBase!DW.Transform.Template.StateTransition" Domain="DWBase!Domain.IncidentManagement" TimeGrain="Daily">
        <WaterMarkDependency>CoreIncident!System.WorkItem.Incident</WaterMarkDependency>
        <Dimensions>
          <Dimension Nullable="false">IncidentDim</Dimension>
        </Dimensions>
        <Outriggers>
          <Outrigger>IncidentStatus</Outrigger>
        </Outriggers>
        <DegenerateDimensions>
          <DegenerateDimension ID="StartDateTime" Type="datetime" CaseSensitive="false" MaxLength="256" Nullable="false" />
          <DegenerateDimension ID="FinishDateTime" Type="datetime" CaseSensitive="false" MaxLength="256" Nullable="true" />
        </DegenerateDimensions>
        <Measures>
          <Measure>TotalTimeMeasure</Measure>
        </Measures>
      </Fact>
    </Facts><br>

But if you are fine with using the LIVE database History tables, then I'd carry on with that.

Alastair_Smart

Thank you for this information. We have in fact got our report using PowerShell.  Regards

Adam_Stoppa

Hi, i have a similary problem does someone has an idea ?

i need the history from a hardware asset, special i am looking for who has added a certain asset, is there a way to find out ?

so far i have tried the following in powershell:

$HWClass = Get-SCSMClass -name Cireson.AssetManagement.HardwareAsset

$HWList=Get-SCSMObject -computername $scsmserver -Class $HWClass

foreach($hw in $hwlist){

  if($hw.HardwareAssetID -eq "2020080093")

 {

    Write-Output $hw.HardwareAssetID

    Write-Output $result = Get-SCSMObjectHistory

}

}

But Get-SCSMObjectHistory does not show who has added the asset.

Geoff_Ross

Hi @Adam_Stoppa

Your PowerShell is not the most efficient method but will certainly work and get you the asset history so nice work.

The first History item will show you who created it. However, you must consider that SCSM doesn't keep all history for all time. The default is to keep 1 year of history and this is configurable in the data retention settings. Once history has been groomed, there is no way to tell who created the asset.

Geoff

Adam_Stoppa

Thank you for this information.