logo Windmill Hub
Login
All items
0

Aggregate Records

by
Published 4 days ago

Count records on a table (Aggregate API), optionally filtered by an encoded query and grouped by a field.

Script servicenow Verified
Use in Windmill

The script

Submitted by hugo989 Typescript (fetch-only)
Verified 5 days ago
All edits
Permalink
1 
//native
2
3 
export type DynSelect_table = string
4 
export type DynSelect_group_by = string
5
6 
function authHeader(auth: RT.Servicenow) {
7 
  return auth.token
8 
    ? `Bearer ${auth.token}`
9 
    : `Basic ${btoa(`${auth.username}:${auth.password}`)}`
10 
}
11
12 
// Dropdown of the instance's tables (sys_db_object).
13 
export async function table(auth: RT.Servicenow) {
14 
  const response = await fetch(
15 
    `${auth.instance_url}/api/now/table/sys_db_object?sysparm_fields=name,label&sysparm_limit=10000`,
16 
    {
17 
      headers: {
18 
        Authorization: authHeader(auth),
19 
        Accept: "application/json",
20 
      },
21 
    }
22 
  )
23 
  if (!response.ok) {
24 
    throw new Error(`${response.status} ${await response.text()}`)
25 
  }
26 
  const { result } = (await response.json()) as {
27 
    result: { name: string; label: string }[]
28 
  }
29 
  return result
30 
    .filter((t) => t.name)
31 
    .map((t) => ({ value: t.name, label: `${t.label} (${t.name})` }))
32 
    .sort((a, b) => a.label.localeCompare(b.label))
33 
}
34
35 
// Walk the table's inheritance chain (sys_db_object.super_class) so the group-by
36 
// options include fields inherited from parent tables, not just fields defined
37 
// on the chosen table (sys_dictionary stores each field under its defining
38 
// table — e.g. incident extends task, so priority/state live on task).
39 
// Best-effort: any failure falls back to just the table itself.
40 
async function tableChain(auth: RT.Servicenow, table: string) {
41 
  const chain: string[] = []
42 
  let current: string | undefined = table
43 
  while (current && !chain.includes(current)) {
44 
    chain.push(current)
45 
    const response = await fetch(
46 
      `${auth.instance_url}/api/now/table/sys_db_object?sysparm_query=name=${current}&sysparm_fields=super_class.name&sysparm_limit=1`,
47 
      {
48 
        headers: {
49 
          Authorization: authHeader(auth),
50 
          Accept: "application/json",
51 
        },
52 
      }
53 
    )
54 
    if (!response.ok) break
55 
    const { result } = (await response.json()) as {
56 
      result: { "super_class.name"?: string }[]
57 
    }
58 
    current = result[0]?.["super_class.name"] || undefined
59 
  }
60 
  return chain
61 
}
62
63 
// Dependent select: the chosen table's columns (sys_dictionary) to group by,
64 
// including columns inherited from parent tables.
65 
export async function group_by(auth: RT.Servicenow, table: DynSelect_table) {
66 
  if (!table) return []
67 
  const chain = await tableChain(auth, table)
68 
  const url = new URL(`${auth.instance_url}/api/now/table/sys_dictionary`)
69 
  url.searchParams.append(
70 
    "sysparm_query",
71 
    `nameIN${chain.join(",")}^elementISNOTEMPTY`
72 
  )
73 
  url.searchParams.append("sysparm_fields", "element,column_label")
74 
  url.searchParams.append("sysparm_limit", "10000")
75
76 
  const response = await fetch(url, {
77 
    headers: {
78 
      Authorization: authHeader(auth),
79 
      Accept: "application/json",
80 
    },
81 
  })
82 
  if (!response.ok) {
83 
    throw new Error(`${response.status} ${await response.text()}`)
84 
  }
85 
  const { result } = (await response.json()) as {
86 
    result: { element: string; column_label: string }[]
87 
  }
88 
  return result
89 
    .filter((f) => f.element)
90 
    .map((f) => ({
91 
      value: f.element,
92 
      label: `${f.column_label} (${f.element})`,
93 
    }))
94 
    .sort((a, b) => a.label.localeCompare(b.label))
95 
}
96
97 
/**
98 
 * Aggregate Records
99 
 * Count records on a table (Aggregate API), optionally filtered by an encoded query and grouped by a field. Returns ServiceNow's stats envelope with the count per group.
100 
 */
101 
export async function main(
102 
  auth: RT.Servicenow,
103 
  table: DynSelect_table,
104 
  query: string | undefined,
105 
  group_by: DynSelect_group_by | undefined
106 
) {
107 
  const url = new URL(`${auth.instance_url}/api/now/stats/${table}`)
108 
  url.searchParams.append("sysparm_count", "true")
109 
  if (query !== undefined && query !== "") {
110 
    url.searchParams.append("sysparm_query", query)
111 
  }
112 
  if (group_by !== undefined && group_by !== "") {
113 
    url.searchParams.append("sysparm_group_by", group_by)
114 
  }
115
116 
  const response = await fetch(url, {
117 
    method: "GET",
118 
    headers: {
119 
      Authorization: authHeader(auth),
120 
      Accept: "application/json",
121 
    },
122 
  })
123
124 
  if (!response.ok) {
125 
    throw new Error(`${response.status} ${await response.text()}`)
126 
  }
127
128 
  return await response.json()
129 
}
130