Splunk - SQL Server Index Usage Statistics

index="myindex" sourcetype="Foo:Bar:Baz:IndexUsageStats"
| rename
    SQLServerStartTimeUTC          as sSQLServerStartTimeUTC, ```rename to more easily differentiate between fields (s = string)```
| eval ```this could be eliminated if we were to load this value directly as a unix timestamp```
    uSQLServerStartTimeUTC         = strptime(sSQLServerStartTimeUTC, "%Y-%m-%dT%H:%M:%S.%3N") ```Parse sSQLServerStartTimeUTC to unix timestamp (u = unix)```
| streamstats current=f ```Grab the next event stats```
    last("User*Count")             as "NextUser*Count"
    last(EstimatedIndexSizeKB)     as NextEstimatedIndexSizeKB
    last(IndexRowCount)            as NextIndexRowCount
    last(_time)                    as NextEventTime
    last(uSQLServerStartTimeUTC)   as uNextSQLServerStartTimeUTC
  by host DatabaseName ObjectSchemaName ObjectName IndexName
| eval ```determine whether SQL Server was restarted since the next event```
    WasSQLServerRestarted          = if(uSQLServerStartTimeUTC < uNextSQLServerStartTimeUTC, 1, 0),
    ```calculate the stats deltas```
    DiffUserSeeksCount             = NextUserSeeksCount       - if(WasSQLServerRestarted = 1, 0, UserSeeksCount),
    DiffUserScansCount             = NextUserScansCount       - if(WasSQLServerRestarted = 1, 0, UserScansCount),
    DiffUserLookupsCount           = NextUserLookupsCount     - if(WasSQLServerRestarted = 1, 0, UserLookupsCount),
    DiffUserUpdatesCount           = NextUserUpdatesCount     - if(WasSQLServerRestarted = 1, 0, UserUpdatesCount),
    DiffEventTimeSeconds           = NextEventTime            - if(WasSQLServerRestarted = 1, uNextSQLServerStartTimeUTC, _time),
    DiffEstimatedIndexSizeKB       = NextEstimatedIndexSizeKB - EstimatedIndexSizeKB,
    DiffIndexRowCount              = NextIndexRowCount        - IndexRowCount
| stats
    count                          as TotalEvents
    latest(_time)                  as LatestEventTime
    ```metadata```
    latest(IsUnique)               as IsUnique
    latest(IsUniqueConstraint)     as IsUniqueConstraint
    latest(IsPrimaryKey)           as IsPrimaryKey
    latest(IsDisabled)             as IsDisabled
    latest(HasFilter)              as HasFilter
    latest(IndexType)              as IndexType
    latest(ObjectType)             as ObjectType

    latest(ObjectCreateDate)       as sObjectCreateDate
    latest(EstimatedIndexSizeKB)   as EstimatedIndexSizeKB
    latest(IndexRowCount)          as IndexRowCount
    ```sum up each event delta```
    sum("DiffUser*Count")          as "TotalUser*"
    sum(DiffEstimatedIndexSizeKB)  as TotalEstimatedIndexSizeKBChange
    sum(DiffIndexRowCount)         as TotalIndexRowCountChange
    sum(DiffEventTimeSeconds)      as TotalEventTimeSeconds
  by host DatabaseName ObjectSchemaName ObjectName IndexName
| search
    TotalEvents                    > 1
| eval
    TotalReads                     = TotalUserSeeks + TotalUserScans + TotalUserLookups,
    TotalWrites                    = TotalUserUpdates,
    TotalStatsDays                 = TotalEventTimeSeconds / 86400,
    IsArchived                     = if(LatestEventTime < relative_time(now(), "-7d"), 1, 0),
    uObjectCreateDate              = strptime(sObjectCreateDate, "%Y-%m-%dT%H:%M:%S.%3N")
| fields
    host DatabaseName ObjectSchemaName ObjectName uObjectCreateDate sObjectCreateDate ObjectType IndexName IsArchived LatestEventTime
    IndexType IsUnique IsUniqueConstraint IsPrimaryKey IsDisabled HasFilter EstimatedIndexSizeKB IndexRowCount
    TotalUserSeeks TotalUserScans TotalUserLookups
    TotalReads TotalWrites TotalEstimatedIndexSizeKBChange TotalIndexRowCountChange TotalEvents TotalStatsDays