/bundles/itninjaweb/img/Breadcrumb_cap_w.png

Blog Posts by dchristian

Ask a question

HOW TO: Smart label for Dell Machines older than X years

Hey Guys,

I'll keep this one short and sweet. Here's some SQL to create a label for all machines older than 2 years old.

 

SELECT *, 
       Unix_timestamp(NOW()) - Unix_timestamp(LAST_SYNC) AS LAST_SYNC_TIME, 
       Unix_timestamp(MACHINE.LAST_SYNC)                 AS LAST_SYNC_SECONDS 
FROM   ORG1.MACHINE 
       LEFT JOIN KBSYS.KUID_ORGANIZATION 
         ON ( KUID_ORGANIZATION.KUID = MACHINE.KUID ) 
       LEFT JOIN KBSYS.SMMP_CONNECTION 
         ON ( SMMP_CONNECTION.KUID = MACHINE.KUID 
              AND KUID_ORGANIZATION.ORGANIZATION_ID = 1 ) 
       JOIN KBSYS.DELL_ASSET DA 
         ON ( MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG ) 
WHERE  DA.SHIP_DATE < DATE_SUB(NOW(), INTERVAL 2 YEAR) 

 

 

Hope it helps.

View comments (2)

HOW TO: Create 2 Equal Size Partitions Regardless of Drive Size

Hey Guys,

 

Here's some poorly written VBS i put together to create 2 equal size partitions regardless of the hard drive size! I assign the partitions C&Z. I choose Z becuase i noticed D was used by the CD rom and it was causing trouble.

 

Here's the VBS to create the 2 paritions. Save it as "driver.vbs"

 

 

Option Explicit

Dim macer
Dim driveSize
Dim diskpartScript
Dim WshShell

macer = Replace(FindMac(),":","")
driveSize = FindDrive()
diskpartScript =(WriteFile(macer,driveSize))

Set WshShell = WScript.CreateObject("Wscript.Shell")

WshShell.Run "diskpart.exe /s " & diskpartScript,,True


'***********************************************************
'Find the size of the drive, convert to MB and divide by 2
'***********************************************************
Function FindDrive()
Dim strComputer
Dim objWMIService
Dim colItems
Dim objItem
Dim strSize

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_DiskDrive",,48)

For Each objItem in colItems
strSize = Int(((objItem.Size/1024)/ 1024) /2)
Next

FindDrive = strSize
End Function

'***********************************************************
'So hard to find the mac address!!!!!
'***********************************************************
Function FindMac()
Dim strComputer
Dim objWMIService
Dim colItems
Dim objItem
Dim strAdapterType

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colItems = objWMIService.ExecQuery("Select * from Win32_NetworkAdapter")

For Each objItem in colItems
Select Case objItem.AdapterTypeID
Case 0 strAdapterType = "Ethernet 802.3"
Case 1 strAdapterType = "Token Ring 802.5"
Case 2 strAdapterType = "Fiber Distributed Data Interface (FDDI)"
Case 3 strAdapterType = "Wide Area Network (WAN)"
Case 4 strAdapterType = "LocalTalk"
Case 5 strAdapterType = "Ethernet using DIX header format"
Case 6 strAdapterType = "ARCNET"
Case 7 strAdapterType = "ARCNET (878.2)"
Case 8 strAdapterType = "ATM"
Case 9 strAdapterType = "Wireless"
Case 10 strAdapterType = "Infrared Wireless"
Case 11 strAdapterType = "Bpc"
Case 12 strAdapterType = "CoWan"
Case 13 strAdapterType = "1394"
End Select
If (objItem.AdapterTypeID = 0 ) Then
FindMac = objItem.MACAddress
End If
Next
End Function

'***********************************************************
'This creates the text file. We'll use the mac address as the
'name. After we'll use diskpart to create the partitions
'***********************************************************
Function WriteFile (xMac,xSize)
Dim objFSO
Dim objTextFile
Dim objWMIService
Dim strComputer
Dim colServices
Dim path

Const FOR_READING = 1
Const FOR_WRITING = 2
Const FOR_APPENDING = 8
Const FOLDER_PATH = "T:\diskparts"

path = FOLDER_PATH & "\" & xMac & ".txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not((objFSO.FolderExists(FOLDER_PATH))) Then objFSO.CreateFolder(FOLDER_PATH)

Set objTextFile = objFSO.OpenTextFile _
(path, FOR_WRITING, True)

objTextFile.writeline "select disk 0"
objTextFile.writeline "clean"
objTextFile.writeline "create partition primary size=" & xSize
objTextFile.writeline "select partition 1"
objTextFile.writeline "active"
objTextFile.writeline "assign letter=c"
objTextFile.writeline "create partition primary"
objTextFile.writeline "select partition 2"
objTextFile.writeline "assign letter=z"
objTextFile.writeline "exit"

objTextFile.Close
WriteFile = path

End Function


 

I also create a bat Script to Format both the C & Z drive. 

 

 

format /q /y /fs:ntfs c:
format /q /y /fs:ntfs z:


 

 

Hope this helps.

 

Be the first to comment

HOW TO: Create a Pivot Table For HelpDesk Satisfaction Survey

Here's som old SQL that makes a nice little table for the sat survery in the K1.

My idea was that this could be pasted into excel really easy so you could create charts, etc...

 

SELECT Coalesce(FULL_NAME, 'UNASSIGNED')          AS NAME, 
       Max(IF(SATISFACTION_RATING = 1, COUNT, 0)) AS 'POOR', 
       Max(IF(SATISFACTION_RATING = 2, COUNT, 0)) AS 'Below Average', 
       Max(IF(SATISFACTION_RATING = 3, COUNT, 0)) AS 'Average', 
       Max(IF(SATISFACTION_RATING = 4, COUNT, 0)) AS 'Good', 
       Max(IF(SATISFACTION_RATING = 5, COUNT, 0)) AS 'Excellent', 
       Max(IF(SATISFACTION_RATING = 0, COUNT, 0)) AS 'Not Rated' 
FROM   (SELECT U.FULL_NAME, 
               T.SATISFACTION_RATING, 
               COUNT(*) AS COUNT 
        FROM   HD_TICKET T 
               LEFT JOIN USER U 
                 ON T.OWNER_ID = U.ID 
        GROUP  BY U.FULL_NAME, 
                  T.SATISFACTION_RATING) RAW 
GROUP  BY FULL_NAME 
ORDER  BY NAME 



I hope this helps.

View comments (2)

HOW TO: Find User log On and Log Off Time

Here's something i came up with to find when users are logging on and off of their machines. 

First we need to create a powershell script to find the actual info (i wish i wrote this, found it on the internet):

 

$UserProperty = @{n="User";e={(New-Object System.Security.Principal.SecurityIdentifier $_.ReplacementStrings[1]).Translate([System.Security.Principal.NTAccount])}}
$TypeProperty = @{n="Action";e={if($_.EventID -eq 7001) {"Logon"} else {"Logoff"}}}
$TimeProeprty = @{n="Time";e={$_.TimeGenerated}}
Get-EventLog System -Source Microsoft-Windows-Winlogon | select $UserProperty,$TypeProperty,$TimeProeprty

 

Save this as "get_logon_logoff.ps1"

Next create a new script in the K1000. 

Script type = Shell Script.

Upload the ps1 file as a dependency for the script.

Enter this for this script text.

 

IF NOT EXIST C:\windows\tvg (
mkdir c:\windows\tvg
)
powershell.exe -nologo -executionpolicy bypass -WindowStyle hidden -noprofile -file get_logon_logoff.ps1 > c:\windows\tvg\log.txt
 

Also make sure you change the script name from script.sh to script.bat.

 

 

 

Run this script on your test machine.

Next to actually see the information we'll create a custom inventory rule.

I called mine "Log on / Log off", but it doesn't really matter.

Whats important is the rule syntax:

ShellCommandTextReturn(cmd.exe /c type c:\windows\tvg\log.txt)


This is what it looks like when we're all done.


I hope this helps and you guys like it!


**post edit, in the script text there should only be one ">" instead of 2 (">>"). I corrected in the code but not the pic.


View comments (10)

HOW TO: Create a Machine Report by Label Group

I've been gettng asked for this a lot in my trainings. Here is a machine report for all labels belonging to the "Locations" label group.

Hopefully you can get some use out of it.

SELECT M.IP, 
       M. NAME, 
       M.USER_FULLNAME, 
       M.USER_LOGGED, 
       M.CS_MODEL, 
       M.CS_MANUFACTURER, 
       M.SYSTEM_DESCRIPTION, 
       M.OS_NAME, 
       M.SERVICE_PACK, 
       CHILD. NAME AS LABEL_NAME 
FROM   LABEL CHILD, 
       LABEL_LABEL_JT LL, 
       LABEL PARENT, 
       MACHINE M, 
       MACHINE_LABEL_JT ML 
WHERE  CHILD.ID = LL.CHILD_LABEL_ID 
   AND LL.LABEL_ID = PARENT.ID 
   AND M.ID = ML.MACHINE_ID 
   AND ML.LABEL_ID = CHILD.ID 
   AND PARENT. NAME = 'LOCATIONS' 
ORDER  BY CHILD. NAME, 
          M. NAME 

View comments (3)
Showing 1 - 5 of 9 results

Top Contributors

Talk About KACE Free Tools