Deployment log report?

[sullivane]

I posted in the report section, but I think that's for posting working reports, my bad.

Can you make a report that returns info from the deployment log?

[sullivane]

Works like a charm! Thanks!

[Daniel_B]

You can filter on today's date by adding the following under the Where clause:

and pl.Added >= Cast(DateDiff(d,0,GetDate()) as DateTime)

It looks a bit complicated, but in this way it is compatible to SQL Compact.

[sullivane]

I was able to add criteria to narrow this down some (one deployment and only successful), but I can't figure out how to add criteria to only today's date. Not even any good examples on the great and mighty Google.

Thanks for all your help on this!!

[sullivane]

Perfect thanks! I want to play with it to narrow it down for some thing, but I want to try first. I have been trying to learn this some, but I am not a DB guy.

Thanks!!

[Daniel_B · Accepted solution]

You might need to put single quotes around the server name, like the following:

Select Top 1000000 pl.Added As [Log Date],
  a.AssetName,
  a.AssetID,
  p.PackageName As Package,
  p.PackageID,
  pl.Executor As Executor,
  pl.Errorcode As [Return],
  pl.LastStepID As [Last Step],
  Case When pl.RunMode = 1 Then 'System Account'
    When pl.RunMode = 2 Then 'Scanning Credentials'
    When pl.RunMode = 3 Then 'Currently Logged On' End As [Run Mode],
  Case When pl.Success = 1 Then 'black' Else 'red' End As foregroundcolor,
  Case When pl.Success = 1 Then 'tick.png' Else 'minus.png' End As icon,
  pl.Errormessage As Message,
  pl.Version As [Deployer Version]
From tsysPackageLogs pl
  Left Outer Join tblAssets a On pl.AssetID = a.AssetID
  Join tsysPackages p On pl.PackageID = p.PackageID
Where pl.ServerName = 'asd-util-02'
Order By [Log Date] Desc

[sullivane]

Thanks! my server name is asd-util-02 but it changes it to asd - util - 02 and I get "Invalid column name 'asd'. Invalid column name 'util'."

[esr]

Following will mimic the same data as shown in the Deployment>Logs page. Be sure to define the server name, or remove/replace the Where criteria as needed for your goals.

The tables are tsysPackageLogs and tsysPackages

Select Top 1000000 pl.Added As [Log Date],
  a.AssetName,
  a.AssetID,
  p.PackageName As Package,
  p.PackageID,
  pl.Executor As Executor,
  pl.Errorcode As [Return],
  pl.LastStepID As [Last Step],
  Case When pl.RunMode = 1 Then 'System Account'
    When pl.RunMode = 2 Then 'Scanning Credentials'
    When pl.RunMode = 3 Then 'Currently Logged On' End As [Run Mode],
  Case When pl.Success = 1 Then 'black' Else 'red' End As foregroundcolor,
  Case When pl.Success = 1 Then 'tick.png' Else 'minus.png' End As icon,
  pl.Errormessage As Message,
  pl.Version As [Deployer Version]
From tsysPackageLogs pl
  Left Outer Join tblAssets a On pl.AssetID = a.AssetID
  Join tsysPackages p On pl.PackageID = p.PackageID
Where pl.ServerName = @Servername
Order By [Log Date] Desc