Format() Function

format() — Converts values into other formats.

Syntax

The format() function returns a string and takes 2 parameters:

string format(string input, string format)

Or:

string format(integer input, string format)

Or:

tree format(tree input, string format)

Return value

The format() function returns an output that is generally the same type as the input.

Description

The format() function returns a number that is formatted to the specified number of decimal places, such as 233.05 MB instead of 244367360 Bytes. The format() function must contain 2 parameters, one of which is 'human_bytes'.

Parameters

The format function supports the following named parameter:

• human_bytes — Converts an integer or a string to a string, such as 244367360 Bytes to 233.05 MB. The 'human_bytes' parameter must be surrounded by single quotes, as in:
  format(freespace, 'human_bytes')

Examples

You can try the following examples against your own data in the GoToAssist search field, or against fictitious data in the Query Sandbox:

• Example 1: Free space below 1 GB
• Example 2: Windows operating systems
• Example 3: Local drives
• Example 4: Free space below 1 GB

Example 1: Free space below 1 GB

Query: What devices have free disk space that is less than 1 GB?

SELECT systemname, name, description, 
  format(freespace, 'human_bytes') 
FROM %free disk space% 
WHERE freespace <1000000000 
AND description = 'Local Fixed Disk'

This query finds all machines with free disk space under 1 GB, and formats the freespace results in megabytes instead of bytes. For example, the format() function converts the free space for the Luxemburg machine from 244367360 Bytes to 233.05 MB.

Results: Depending on the database, this query returns results similar to this:

row
   systemname LUXEMBURG
   name C:
   description Local Fixed Disk
   format(freespace, human_bytes)
     freespace 233.05 MB
row
   systemname ROME
   name D:
   descriptionLocal Fixed Disk
   format(freespace, human_bytes)
     freespace 176.97 MB
row
   systemname PARIS-VM-XP
   name C:
   description Local Fixed Disk
   format(freespace, human_bytes)
     freespace 318.95 MB  
   . . .

Example 2: Windows operating systems

Query: Which machines are running on Windows OS?

SELECT csname as "System Name", caption as "Installed OS", 
  windowsdirectory as "OS Installation Directory",
  serialnumber as "Windows Product Key", 
  installdate as "Date Installed", 
  lastbootuptime as "Boot Time",
  currenttimezone / 60 as "Time Zone", 
  ../win32_computersystem/daylightineffect 
    as "Daylight Savings", 
  format(totalvirtualmemorysize * 1000, 'human_bytes') 
    as "Virtual Memory Size",
  format(freephysicalmemory * 1000, 'human_bytes') 
    as "Free Physical Memory", 
  format(sizestoredinpagingfiles * 1000, 'human_bytes') 
    as "Used Paging File Memory",
  encryptionlevel as "Encryption Strength", 
  numberoflicensedusers as "User Licenses",
  registereduser as "Registered User", 
  organization as "Registered Organization", 
  csdversion as "Installed Service Pack", 
  largesystemcache as "Optimization",
  producttype as "Domain Role", 
  numberofprocesses as "Running Processes"
FROM /network/device/wmi/win32_operatingsystem order by 1

This query provides information about Windows operating systems, including license, installation date and path, and installed Service Pack. As in Example 1, the format() function displays virtual memory, physical memory, and file memory in gigabytes instead of bytes.

Results: Depending on the database, this query returns results similar to this:

row
   System Name AS01-THMS232-ASB
   Installed OS Microsoft Windows XP Professional
   OS Installation Directory C:\WINDOWS
   Windows Product Key 76487-OEM-0011903-00102
   Date Installed 20061222224926.000000-480
   Boot Time 20080529083536.500000-420
   Time Zone -7
   Daylight Savings true
   Virtual Memory Size 1.95 GB
   Free Physical Memory 317.23 MB
   Used Paging File Memory 2.91 GB
   Encryption Strength 168 
   User Licenses
   Registered User Altoworks
   Registered Organization Altoworks
   Installed Service Pack Service Pack 2
   Optimization 0 Optimize for Applications
   Domain Role 1 Work Station
   Running Processes 59
row
   System Name AS02-THSM232-ASB
   Installed OS Microsoft Windows XP Professional
   OS Installation Directory C:\WINDOWS
   Windows Product Key 76487-OEM-0011903-00102
   Date Installed 20061222224926.000000-480
   Boot Time 20080529083536.500000-420
   Time Zone -7
   Daylight Savings true
   Virtual Memory Size 1.95 GB
   Free Physical Memory 317.23 MB
   Used Paging File Memory 2.91 GB
   Encryption Strength 168 
   User Licenses
   Registered User Altoworks
   Registered Organization Altoworks
   Installed Service Pack Service Pack 2
   Optimization 0 Optimize for Applications
   Domain Role 1 Work Station
   Running Processes 59
   . . .

Example 3: Local drives

Query: List my drives and pertinent information about them.

SELECT systemname as "Machine Name",
  name as "Drive",
  description as "Label",
  drivetype as "Type",
  filesystem as "File System",
  volumeserialnumber as "Serial Number",
  format(size, 'human_bytes') as "Total Space",
  format(freespace, 'human_bytes') as "Free Space",
  format(size - freespace, 'human_bytes') as "Used Space",
  format((100 * freespace) / size, 'human_bytes')||'%' as 
  "% Free"
FROM /network/device/wmi/win32_logicaldisk 
WHERE description != 'Network Connection' order by 1,2

This query displays a list of disk and CD Rom drives obtained via the Windows Management Instrumentation (WMI) tool. As in Example 1, the format() function displays total space, free space, and used space in gigabytes instead of bytes.

Results: Depending on the database, this query returns results similar to this:

row
   Machine Name AS01-THMS232-ASB
   Drive C:
   Label Local Fixed Disk
   Type 3 Local Disk
   File System NTFS
   Serial Number E433125D
  Total Space
     size 37.18 GB
  Free Space
     freespace 8.88 GB
   Used Space 28.30 GB
   % Free 23%
row
   Machine Name AS01-THMS232-ASB
   Drive D:
   Label CD-ROM Disc
   Type 5 Compact Disc
   File System
   Serial Number
  Total Space
     size
  Free Space
     freespace
   Used Space null
   % Free null   
   . . .

Example 4: Free space below 1 GB

Query: What devices have free disk space that is less than 1 GB?

SELECT systemname, name, description, 
  format(freespace, 'human_bytes') 
FROM %free disk space% 
WHERE freespace <1000000000 
AND description = 'Local Fixed Disk'

This query finds all machines with free disk space under 1 GB, and formats the freespace results in megabytes instead of bytes. For example, the format() function converts the free space for the Luxemburg machine from 244367360 Bytes to 233.05 MB.

Results: Depending on the database, this query returns results similar to this:

row
   systemname LUXEMBURG
   name C:
   description Local Fixed Disk
   format(freespace, human_bytes)
     freespace 233.05 MB
row
   systemname ROME
   name D:
   descriptionLocal Fixed Disk
   format(freespace, human_bytes)
     freespace 176.97 MB
row
   systemname PARIS-VM-XP
   name C:
   description Local Fixed Disk
   format(freespace, human_bytes)
     freespace 318.95 MB  
   . . .

To see other PQL functions, see Functions.

Related topics

• Functions
• Ontology
• Operators
• Expressions
• Read about PQL

| Views: 1258 | Last Updated: Thu, May 09 2013 2:28 PM

Is this article helpful?