Jump to content
adrianc2014

Event Log report for last ShadowProtect SPX backup time

Recommended Posts

I've got a report that trawls through the eventlogs table looks for the last successful SPX backup job and then displays this, however when I have multiple servers in a report, it shows the same value for each server even though they are different in the table.

The datasource is based on eventlogs and this Where query:

source='shadowprotectspx' AND eventid='3'

Fields pulled through are ComputerID, EventID, Source, TimeGen.

There's a relationship between this data source and computers based on ComputerID.

The field on the report is based on a calculated field using max([TimeGen]) but that value appears as the same for all devices, i.e. it appears to be one value for the whole customer when actually it should be one per server.

I'm sure I'm very close to getting this working but just haven't got the right syntax!

I wouldn't have to do this is the SPX plugin worked with later versions of the software but that's another issue!

Would be grateful for any suggestions.  Thanks

Adrian

Share this post


Link to post
Share on other sites

Sorted.

It ended up being the field picked from the Field Chooser needing to be the subtable of Computers so that it could tally up per computer ID.

Here's the SQL query to create the view for the results if anybody finds it useful elsewhere.  (It's 'between 2 and 4' because displaying '=3' showed incomplete results despite being only value 3 that you can visually see in the table data! Odd but true and still no reasonable explanation as to why.

SELECT
  `eventlogs`.`ComputerID` AS `ComputerID`,
  MAX(`eventlogs`.`TimeGen`) AS `MAX(TimeGen)`
FROM `eventlogs`
WHERE ((`eventlogs`.`Source` = 'shadowprotectspx')
       AND (`eventlogs`.`EventID` BETWEEN '2' AND '4'))
GROUP BY `eventlogs`.`ComputerID`
ORDER BY `eventlogs`.`TimeGen` DESC)$$

Share this post


Link to post
Share on other sites

Hi @adrianc2014 would you be able to provide some more details on how you have this report set up? I am having the same issue you first mentioned even using between 2 and 4. I am pretty new to building reports so I am probably missing something. I made a data source but if I do custom query I haven't been able to do all the code that you provided on the one line. If I do the data source the other way then I chose eventlogs as the source and then next to where sql I put "Source='shadowprotectspx' AND EventID BETWEEN '2' AND '4'". Included columns I selected ComputerID and TimeGen. It shows the same info across all clients. 

Share this post


Link to post
Share on other sites

I got around this. Didn't realize I needed to create a view with the query. Got that done thru sqlyog. Thanks for sharing the query. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...