Help to show pc audit database

Universal Dashboard
1

Hello everyone,

I just found an awesome tool to audit or inventory windows pc WinAudit (http://www.parmavex.co.uk/winaudit.html). It can export information of each audit time for each pc to various of file formats (txt, csv or html) and especially push all informations (some pc, each pc has information of one or two audit time) into a database (mysql, access, SQL server …) but unfortunately, it could not has central output for the whole items in database. Could UDDashboard do this?

This is database file (https://drive.google.com/drive/folders/1j5xSoarGDPT0GQn-KcQrwCdb4vgjhrdL?usp=sharing)

2

Hi @catinuk and a big warm welcome to the forums. So I am assuming you can query the database that the information is held in?
I have put one of my earlier dashboards on guthub which a few people have said helped them out which is a dashboard using an SQL database to present all the requested information to the end-user. This project is here https://github.com/psDevUK/psUniversalDashboard this should hopefully answer your question. Any further information required, just ask. Thanks

1 Like
3

Thank for your help, I’ll try and feedback soon

4

In fact I might write a blog for this tomorrow if you still not got this working… https://psdevuk.github.io/ud-flix/
Think this would be a good blog to do…
:smiley:

1 Like
5

Hey @catinuk just to show I am true to my word, I managed to smash out a blog tonight and just posted it here:- https://psdevuk.github.io/ud-flix/Serving-SQL-on-a-UniversalDashboard/
This should hopefully give you all the information you need to return data from SQL to a dashboard. Peace

3 Likes
6

Hey @psDevUK. Nice blog, thanks. One comment is that invoke-sqlcmd2 is a powershell gallery module with some nice updates worth looking at. Might cause some confusion that your custom function is named the same.

Oh and

I hope that more grids come to universaldashboard as I would like to see more capability added to the grid to get it to display more in a pivot table manner.

Let me refer you to this thread New-UDgrid filtering and grouping :smiley:

1 Like
7

Thanks @Alc I do these blogs in my own time, and sometimes like last night, I might have a kid or serveral kids to deal with put to bed, get some food for myself, and just try to get these done in an evening. So thanks for pointing out the function mistake I will get that corrected, as well as a few typos, I would like to chip in and see what I can do grid-wise but know this would be a massive challenge to take on. Thanks again for the feedback and will update blog on my lunch

8

Certainly not a mistake - if it works then it’s good right! Just the fact of producing a blog whilst running a family life is an achievement.

Here is how I do SQL with UD:

function Get-WeighticketAudit {
    Param ([int]$TransactionNo)
    
    #set SQL script
    $sql = "SELECT [audit_detail_id]
,[correlation_id]
,[fk_audit_type_code]
,[payload]
,[created_time]
,[fk_error_code]
FROM [MULEESB].[dbo].[AUDIT_DETAILS]
WHERE business_id = '$($TransactionNo)'
ORDER BY created_time desc"

    $SQLweighticketauditParams = @{
        query          = $sql
        Serverinstance = $cache:dataSource
        database       = $cache:database
        Credential     = $cache:cred
        As             = "PSObject"
    }

    $result = Invoke-Sqlcmd2 @SQLweighticketauditParams
    return $result
}

$username = "user"
$password = ConvertTo-SecureString "password"-AsPlainText -Force
$cache:dataSource = 'server'
$cache:database = 'database'
$cache:cred = New-Object System.Management.Automation.PSCredential ($username, $password)

New-UDGrid -Title "Showing audit details for 1234" -AutoRefresh -RefreshInterval 300 -PageSize 50  -Endpoint {
	Get-WeighticketAudit -TransactionNo 1234 | Out-UDGridData
	}

In this case I’ve set some of the parameters under $cache as I use them in other functions