Jump to content

Advice: Updating the Last Logged On and Last Logged on User via Powershell

Recommended Posts


I was wondering if someone can have a look at the following script and let me know if I am overcomplicating things? I understand that using SQL is frowned upon in most cases... but since I dont have a full understanding of Integrations, and our Integrations Officer has left we are unable to proceed with using any proper methods... and I know Powershell to an extent which leads me to writing this.

I am trying to create a powershell script that runs during logon (it works!) but i am updating the Computer Assets table using SQL.

Can you advise me on what I should have done instead? Or is this actually ok?

Import-Module  "C:\users\samwoo\Documents\WindowsPowerShell\Modules\xmlmcmodule\xmlmcModule.psm1"
Import-Module  ActiveDirectory

# Retrieve the Laptop ID
$laptopID = $env:computername

# Retrieve the current logged on user
$currentUsername = $env:USERNAME

# Get the current date and time in the right format
$dateTimeNow = Get-Date -Format "yyyy-MM-dd hh:mm:ss"

# UNUSED - Retrieve the model number ofthe laptop
$laptopModel = ($model = Get-WmiObject -Class Win32_ComputerSystem -Property Manufacturer, Model).model

# Define instance connection details & API key for session authentication
$hbInstance = "(INSTANCE)"
$hbZone     = "eur"
$hbAPIKey   = "(API KEY)"
Set-Instance -Instance $hbInstance  -Key $hbAPIKey -Zone $hbZone

# Get the list of users in Hornbill
$xmlmcOutput_userList = Invoke-XMLMC "data" "getUserList"

# Narrow the list of users to those with the same username as the current logged in user
# and retrieve the name of the user set in Hornbill
$userDisplayName = ($xmlmcOutput_userList.Params.userInfo | Where-Object {$_.userid -eq $currentUsername}).name

# Checks to see whether the user exists in Hornbill
if ($userDisplayName -eq $null)
    Write-Verbose "User does not exist in Hornbill!"
elseif (($userDisplayName | Measure-Object).Count -gt 1)
    Write-Verbose "For some bizarre reason this user $currentUsername exists more than once in Hornbill!!"
    $username = $userDisplayName + ":" + $currentUsername

# Update the laptop asset in Hornbill to input the name of the current user and they date / time they last logged in to it
Add-Param "query" "UPDATE h_cmdb_assets_computer SET h_last_logged_on = '$dateTimeNow', h_last_logged_on_user = 'urn:sys:0:$username' where h_name like '%$laptopID' LIMIT 1"
$xmlmcOutput_updateAsset = Invoke-XMLMC "data" "sqlQuery" -ErrorAction SilentlyContinue

if($xmlmcOutput_updateAsset.status -ne "ok") 
    # API call status not OK - return status and error to console
    "API Call Status : " + $xmlmcOutput_updateAsset.status
    "Error Returned  : " + $xmlmcOutput_updateAsset.error

Remove-Module xmlmcModule

Many thanks,


Link to comment
Share on other sites

(I just noticed a mistake in the above script, I must have accidentally removed an "else" in the IF statement when I copied it to here... Can't edit my post now so :()

Link to comment
Share on other sites

On 06/12/2017 at 8:07 AM, samwoo said:

(I just noticed a mistake in the above script, I must have accidentally removed an "else" in the IF statement when I copied it to here... Can't edit my post now so :()

I fixed that for you

  • Thanks 1
Link to comment
Share on other sites

Thanks @Gerry!! I really appreciate that.

So if it turns out the script is ok to use, I wouldn't want users to find that it doesn't work if they attempt to use it for themselves.



Link to comment
Share on other sites

Hi @samwoo,

The approach that you've used isn't secure, as you're essentially sharing an API key that can run data::sqlQuery. Anyone who is capable could use that API key for malicious purposes. If you've already pushed the script out with that access-all-areas API key contained within it, then you need to delete the API key from the Hornbill user that it belongs to!

With regards to the script itself, I suggest you do not use data::sqlQuery at all, and use the following APIs in your code instead. Then create a custom role in Hornbill that only has permission to the relevant areas, assign this role to a user account for the integration, and generate an API key against this user instead. The user & role should be locked-down as much as possible, so that the API key cannot be used for anything else. 
So the first thing you need to do is identify the primary key of the asset you want to update. You can do this with data::entityBrowseRecords2 (https://api.hornbill.com/data/?op=entityBrowseRecords2 ), and the XMLMC API call would look something like:

<methodCall service="data" method="entityBrowseRecords2">

This will return the asset record, including its primary key.

Once you have the primary key for your asset, you can then use data::entityUpdateRecord (https://api.hornbill.com/data/?op=entityUpdateRecord ) to update the values you need. The API call for this would be structured as so:

<methodCall service="data" method="entityUpdateRecord">
                <h_pk_asset_id>The asset primary key (integer)</h_pk_asset_id>
                <h_last_logged_on>The Date/Time value</h_last_logged_on>
                <h_last_logged_on_user>The User URN</h_last_logged_on_user>

This is much safer, and more secure, than using data::sqlQuery.

I hope this helps,

Link to comment
Share on other sites

Thanks @Steve G! ++++1

Thankfully I haven't deployed it and it's currently in a testing phase with myself to discover what can or cant be done via Powershell (which is amazing in my view).

I will take all your points in and ensure that I get this to be as secure as possible.

I had a thought of the local machine sending a command to a remote "secure" machine which will run the Powershell Script on there - but I dont think this will handle lots of requests very well particularly if loads of users log in at the same time.

The other option is to populate a CSV file when someone logs in (there may already be a file for this) and have the Powershell script run every 30 minutes or an hour on a secure machine which reads this file and updates Hornbill accordingly.

The final solution would be to create this script which sits in a .exe file...

All in all the script will run hidden from the user's view.



  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...